SELECTing and UPDATEing into a random record

[matthewdingley]

I am having some problems trying to get an ad rotator program to work.
Basically, I want to access a database full of information for images, URLs etc and select a random entry. From that entry I want to SELECT everything and I also want to increment the impressions field by one using an UPDATE command.
I am completely stuck on how to do this in one query (so that I am using the same random field).
The MySQL code I have so far is:
SELECT * FROM $tablename ORDER BY RAND() LIMIT 0,1
but I want to put:
UPDATE $tablename SET impressions=impressions+1
into it somehow.

Does anyone have any suggestions?

[hermand]

You need to give everythign a Unique ID (Autoinremement field called aid or somethign)

//Maxrows being the number of adverts in the table

$rand = rand(0,$maxRows);

Then you can do
$query = "SELECT * FROM $tablename WHERE aid = '$rand'";

//get it

$query = "UPDATE $tablename set whatever=whatever WHERE aid='$rand'";

Probably a better way, but that'll work.

[rulinus]

this is just an idea.... do the update in the same db connection, just after the select:
mysql_connect(X,X,X) or die ("Problem connecting to DataBase");

$query = "SELECT * FROM $tablename ORDER BY RAND() LIMIT 0,1";
$result = mysql_db_query("X", $query);
while ($r = mysql_fetch_array($result)) {
$id = $r["your index column...some id of the row"];
$impressions = $r["impressions"];
}
++$impressions
$query2 = "UPDATE $tablename SET impressions='$impressions' WHERE ID='$id'";
$result2 = mysql_db_query("X", $query2);

i believe that shoud do the trick