Thursday, January 3, 2008

Multiple MySQL Queries in PhP

The problem with using multiple queries from multiple tables is that the MYSQL_ASSOC or MYSQL_NUM both only seem to work on the latest query even if the queries and results are stored under different variables. Here is the code:
echo '
';
require ('dbconnect.inc');
$query_state = "select state from States;";
$result_state = mysql_query($query_state);
$query_city = "select city from Cities;";
$result_city = mysql_query($query_city);
$query_venue = "select venue from Venues;";
$result_venue = mysql_query($query_venue);

if (mysql_num_rows($result_state) == 0 | mysql_num_rows($result_city) == 0 | mysql_num_rows($result_venue) == 0){
echo 'nothing here...';
}
else{
while ($row1 = mysql_fetch_array($result_state, MYSQL_ASSOC) | $row2 = mysql_fetch_array($result_city, MYSQL_ASSOC) | $row3 = mysql_fetch_array($result_venue, MYSQL_ASSOC)){
echo "";
$out = "";
echo $out;
echo $row1['state'];
echo "
";

$out = "";
echo $out;
echo $row2['city'];
echo "
";

$out = "";
echo $out;
echo $row3['venue'];
echo "

";
}
}
echo '
';
?>
And all that appears is the last column. The tags are being read correctly because the result remains in the correct column it just doesn't seem to read the first two queries.
My solution for now is just to move the tables into one table which isn't most efficient for the database end but I think that can be sacrificed for now for PhP efficiency.

1 comment:

Efeion said...

You should be able to store results of multiple queries with out any problem. Looking at your code it should work, except for one thing. Why are you using the bitwise or ( | ) instead of the logical or ( || ) ? I wonder if this could be a part of your problem.