Retrieving values from MySQL

Question

I have a very simple table that contains a list of 'victims' and the corresponding number of that type destroyed. I'm trying to make an output page of this information, using this code:

foreach( $victims as $vic )
{
   $hits = mysql_query("SELECT amount
                          FROM victims
                         WHERE victim = ".$vic );

   echo $hits;

   print "$vic: $hits <br /><hr>";
}

However, hits comes out empty. What's wrong with my SQL query?

Answer 1

foreach($victims as $vic)
{
   $hits = mysql_query('SELECT amount
                        FROM victims
                        WHERE victim = "' . mysql_real_escape_string($vic) . '"');

   if($hits && mysql_num_rows($hits)>0) {
        while($row = mysql_fetch_array($hits)) {
               echo '<p>' . $row['amount'] . ' hits</p>';
        }
   } else {
        echo '<p>' . mysql_error() . '</p>';
   }
}

Answer 2

mysql_query() doesn't return the actual result of your query, but rather a resource with which you can then access the results.

This is a typical pattern:

$result = mysql_query(...);
$row = mysql_fetch_assoc($result);
print($row['amount']);

Each call to mysql_fetch_assoc returns the next row of the result set. If you were expecting multiple rows to be returned, you can call this in a while loop:

$result = mysql_query(...);
while ($row = mysql_fetch_assoc($result)) {
    print($row['amount']);
}

Answer 3

Since there's no sane error checking in any of the answers, I'll put the whole thing in here:

foreach( $victims as $vic )
{
   $sql = "SELECT amount
               FROM victims
               WHERE victim = '".mysql_real_escape_string($vic)."'";
   $result = mysql_query($sql);
   $result or die('Query Error: '.mysql_error() . ' - ' . $sql);

   $hitsarray = mysql_fetch_assoc($result);
   if ($hitsarray) {
       $hits = $hitsarray['amount'];
   } else {
       // No row was found
       $hits = 0;
   }

   echo $hits;

   print "$vic: $hits <br /><hr>";
}

Oh, and this fixes the query error that caused the issue in the first place. Note the quotes wrapping the $vic variable in the string, as well as the proper escaping of the string...