two MySQL queries in 1 while()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

two MySQL queries in 1 while()

Post 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
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post 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.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

you two are a big help, thanks. 8)
Post Reply