Counting the number of times that a record is viewed - nested SQL commands??

[MortimerJazz]

Ok, I think I've sort of got the hang of this. I've inserted a "count" field into my table which I'm planning on incrementing by 1 every time the data is displayed.

My question is, how do I set up two SQL queries to (a)Extract the necessary information to display on the screen and (b) Update the "count" filed. Presumably the UPDATE command has to be inserted into the mysql_fetch_array so that each record gets updated should there be more than one

At the moment I've got this:

[code]
$recent_sql = mysql_query("SELECT *
  FROM toys
WHERE id='$id'
ORDER BY price DESC");

while ($recent = mysql_fetch_array($recent_sql))
    {
$username=$recent['username'];
$colour=$recent['colour'];
$count=$recent['count'];
$count++;

$update = mysql_query("UPDATE toys SET count=$count WHERE id=$id")
or die(mysql_error()); 

}
[/code]

Will that actually work?

Thanks for your help,

[printf]

Don't do the update in the result loop, wait until you exit the loop then run a single query to update all the rows with (1) query, using the auto increment primary key!


[code]$update = array ();

$recent_sql = mysql_query ( "SELECT * FROM toys WHERE id = '" . $id . "' ORDER BY price DESC" );

while ( $recent = mysql_fetch_array ( $recent_sql ) )
{
$username = $recent['username'];
$colour = $recent['colour'];
$count = $recent['count'];

// toys should ha a primary key (auto_increment)

$update[] = $recent['toy_id'];
}

mysql_query ( "UPDATE toys SET count = ( count + 1 ) WHERE toy_id IN(" . implode ( ', ', $update ) . ")" ) or die ( mysql_error () );[/code]