Page 1 of 1

two MySQL queries in 1 while()

Posted: Sun Nov 03, 2002 4:37 pm
by m3mn0n
Mission Impossible. :cry:

I've never attempted this before so bare with me...

Code: Select all

<?php

$Link = mysql_connect ($Host, $User, $Password);

$Query = "SELECT * from $TableName order by rank LIMIT 3";
$Result = mysql_db_query ($DBName, $Query, $Link);

$Query2 = "SELECT * from $TableName2 order by rank LIMIT 3";
$Result2 = mysql_db_query ($DBName, $Query2, $Link);

//creating a table...

print ("<table border=0 width="100%" align=left>\n");
//fetch the results from the databas
while ($Row = mysql_fetch_array ($Result) && $Row2 = mysql_fetch_array ($Result2)) {
	print ("<tr align=left valign=top>\n");
	print ("<td align=left valign=top><font size=1 face=verdana>#$Rowїrank].</td>\n");
	print ("<td align=left valign=top><b><font size=1 face=verdana>$Rowїempire](#$Rowїnum])</b></td>\n");
	print ("<td align=left valign=top><font size=1 face=verdana>$Row2їtag]</td>\n");
	print ("</tr>\n");
}

mysql_close ($Link);

print ("</table>\n");

?>

What am i doing wrong? I just figured this is how to do it, but i am getting this...
Warning: Supplied argument is not a valid MySQL result resource in C:\apache\htdocs\_smx.ca\0\gamezone\home.links.php on line 88
Line 88 is the while() loop. :x

Posted: Sun Nov 03, 2002 6:19 pm
by mydimension
why not join the sql statements? here's how:
SQL query:

Code: Select all

SELECT t1.*, t2.* FROM $TableName AS t1, $TableName2 AS t2 ORDER BY t1.rank LIMIT 3
what you PHP code would look like:

Code: Select all

<?php

$Link = mysql_connect ($Host, $User, $Password);

$Query = "SELECT t1.*, t2.* FROM $TableName AS t1, $TableName2 AS t2 ORDER BY t1.rank LIMIT 3";
$Result = mysql_db_query ($DBName, $Query, $Link);

//creating a table...

print ("<table border=0 width="100%" align=left>\n");
//fetch the results from the databas
while ($Row = mysql_fetch_array($Result)) {
   print ("<tr align=left valign=top>\n");
   print ("<td align=left valign=top><font size=1 face=verdana>#$Rowїt1.rank].</td>\n");
   print ("<td align=left valign=top><b><font size=1 face=verdana>$Rowїt1.empire](#$Rowїt1.num])</b></td>\n");
   print ("<td align=left valign=top><font size=1 face=verdana>$Rowїt2.tag]</td>\n");
   print ("</tr>\n");
}

mysql_close ($Link);

print ("</table>\n");

?>
all i did was alias the two tables together into one query and therefore you only have one result to deal with. hope this is easy to follow. if not feel free to contact me for more explanation.

Posted: Mon Nov 04, 2002 2:18 am
by twigletmac
First of all, don't use mysql_db_query():
php manual wrote:Note: This function has been deprecated since PHP 4.0.6. Do not use this function. Use mysql_select_db() and mysql_query() instead.
Secondly, if you're getting a "Supplied argument is not a valid MySQL result resource" error then the problem is likely to lie with your MySQL connection, database selection or query so use mysql_error() to debug.
So try changing your code to:

Code: Select all

@$Link = mysql_connect ($Host, $User, $Password) or die(mysql_error()); 
@mysql_select_db($DBName) or die(mysql_error());

$Query = "SELECT * from $TableName order by rank LIMIT 3"; 
$Result = mysql_query ($Query) or die(mysql_error().'<p>'.$Query.'</p>'); 

$Query2 = "SELECT * from $TableName2 order by rank LIMIT 3"; 
$Result2 = mysql_query ($Query2) or die(mysql_error().'<p>'.$Query.'</p>');
Finally, you really do not need parenthesis around your print() statements, so things like this

Code: Select all

print ("<table border=0 width="100%" align=left>\n");
can be

Code: Select all

print "<table border=0 width="100%" align=left>\n";
Mac

Posted: Wed Nov 06, 2002 5:03 pm
by m3mn0n
you two are a big help, thanks. 8)