Page 1 of 1

echo $ from two queries ??

Posted: Sun Dec 26, 2004 7:09 am
by pookie62
Hi all,
I'm using two Queries and want to output $ form both.
I'm using a while statement for the second query, how can I define a variable in this While loop which uses a $ form the first query where `sc` and `naam`(query 2) must match.
Tried this, but doesn't work.
First query:

Code: Select all

<?php
$deelname = "SELECT DISTINCT COUNT(`inschrijving`.`WedstrijdId`) AS `visited`, `deelnemer`.`Naam` AS `sc` FROM `deelnemer` INNER JOIN `inschrijving` ON (`deelnemer`.`Id` = `inschrijving`.`DeelnemerId`) INNER JOIN `wedstrijd` ON (`inschrijving`.`WedstrijdId` = `wedstrijd`.`Id`)GROUP BY `deelnemer`.`Naam` ";
	$aantal = mysql_query($deelname) or die("Error: " . mysql_error());	
	while ($line2 = mysql_fetch_array($aantal, MYSQL_ASSOC)){
	$Visit = $line2["visited"];
	$sc = $line2["sc"];
    }
?>
Second query

Code: Select all

<?php
$query  = "SELECT `klasse`.`Klasse` AS `Klasse`,`klassement`.`LevelId` AS `Level`,`deelnemer`.`Naam` AS `naam`,`deelnemer`.`Vereniging` AS `Vereniging`, `deelnemer`.`URL` AS `URL`, TRUNCATE (`klassement`.`Totaal percentage` * 100, 2) AS `Totaal%`, TRUNCATE (`klassement`.`Gemiddelde percentage` * 100,2) AS `Gem%` FROM `klassement` INNER JOIN `deelnemer` ON (`klassement`.`DeelnemerId` = `deelnemer`.`Id`) INNER JOIN `deelnemerklasselevel` ON (`deelnemer`.`Id` = `deelnemerklasselevel`.`DeelnemerId`) INNER JOIN `klasse` ON (`klassement`.`KlassId` = `klasse`.`Id`) GROUP BY `Klasse`,`Naam`,`Level` ORDER BY `Klasse`,`Level`,`Totaal Percentage` DESC LIMIT $limitvalue, $limit";        
    $result = mysql_query($query) or die("Error: " . mysql_error());
    $numrows = mysql_num_rows($result);
    if($numrows == 0){
        echo("Nothing to Display!");
    }
?>


While statement

Code: Select all

<?php
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
	$row_count = $row_count;
	$Klasse = $line["Klasse"];
    $Level = $line["Level"];
    $Naam = $line["naam"];
    $Vereniging = $line["Vereniging"]; 
    $Totaal = $line["Totaal%"];
    $Gem = $line["Gem%"];
	$URL = $line["URL"];
	 if ($sc == $Naam) { 
       $Visit = $line2["visited"];    
	   }
?>

Posted: Sun Dec 26, 2004 7:43 am
by feyd
typically, we use a :arrow:[mysql_man]join[/mysql_man] to attach 1 query to another related/dependant query.

Posted: Sun Dec 26, 2004 8:11 am
by pookie62
Hi Feyd,
How can I do that ? These queries have allready several joins and I don't see a way to combine them.
Thought that some PHP code could do this easily for me..

Posted: Sun Dec 26, 2004 8:30 am
by feyd
php can relatively easily do it, I prefer using sql typically. With php, you'll need to ask the inner query to toss it's results after each iteration or you can run the risk of running out of server memory.

Posted: Sun Dec 26, 2004 1:27 pm
by pookie62
Can you give me example of code??

Posted: Sun Dec 26, 2004 5:31 pm
by timvw
an example of join with more than 2 tables can found at
http://norlonto.net/gyrus/dev/3_table_outer_joins.cfm (it's somewhere on that page)

Posted: Mon Dec 27, 2004 3:00 am
by pookie62
Interesting article, but I really don't see how I can join the two query's, especially while the first one has a SELECT DISTINCT and the second query starts with a normal SELECT. I'm too much newbie for this.. :oops:
Are you able to combine them ?? (Or the proper PHP code is fine too.)
Would be very hellpfull !!

Posted: Tue Dec 28, 2004 6:19 am
by pookie62
Does anybody knows how to do this ???
The both queries give the results I want seperatly, but I can't combine them, or find the proper code to do it..