Selecting multiple MySQL records and passing them to variables

I’m writing a small script using PHP and MySQL. I have the historical temperatures of cities in the database. Here are two ways I’ve tried to select the temperature of New York and Tokyo.

The first

$row = mysql_query("SELECT city_temperature FROM temperature_table WHERE temperature_date = ‘2009-11-10 00:00:00’
AND city_id = ‘1’ ") or die(mysql_error());

$ny_temp = mysql_fetch_array($row);

I could have similar code for some 7 other cities on the same page. That would mean running 7 such queries on the same page. There are 8 records in the database for each date, one for each city. In terms of memory usage, is there some better way to select the records.

The second

I tried selecting multiple records using this code below. It works well to display the records in a table but I can’t figure out how to pass the values to variables on the page. Could I use an array for this?

$result = mysql_query("SELECT * FROM temperature_table WHERE temperature_date = ‘2009-11-10 00:00:00’ GROUP BY city_id ")
or die(mysql_error());

echo “<table border=‘1’>”;
echo “<tr> <th>City ID</th> <th>City Temperature</th> </tr>”;

while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo “<tr><td>”;
echo $row[‘city_id’];
echo “</td><td>”;
echo $row[‘city_temperature’];
echo “</td></tr>”;
}

echo “</table>”;

Appreciate some guidance. Thank you.

Selecting multiple MySQL records and passing them to variables

not sure what you mean.

each element in each row of $row can be passed to a variable if you like.

If I have understood correctly, you only have one temp per day per city, in which case this should work.


SELECT city_id, city_temperature
FROM temperature_table 
WHERE temperature_date = '2009-11-10 00:00:00' 
AND city_id IN (1,2,45,89);

That should work with your loop and table, though I have not tested it.

cups++

:award:

Thanks guys, that helped!!