Page 1 of 1

mysql random update.. how to do it?

Posted: Sun Mar 29, 2009 4:59 am
by ViserExcizer
a mysql statement

"UPDATE usertable SET user_status='premium', ref_date='$date', referrer='$refname' WHERE user_status!='premium' AND referrer='' LIMIT 1"

always picks the entry that satisfies the condition from the earliest record to later ones. How to randomize this?

Like is a user with the credentials

id user_status referrer

75 standard

(which is the first record to match the conditions)
doesnt get picked but another random one down the line like with an ID 87 gets picked?

Re: mysql random update.. how to do it?

Posted: Sun Mar 29, 2009 1:02 pm
by tech603
Sounds like you would want to create a random number to use in your queries. Here is php's link for that function.

http://php.net/rand

Hope that helps point you in the right direction.

Re: mysql random update.. how to do it?

Posted: Sun Mar 29, 2009 2:10 pm
by s.dot
I would use a SELECT statement to pull someone with that criteria using ORDER BY RAND() LIMIT 1 in your SELECT query. Then once you've retrieved that username/id from that row, use an UPDATE query to update that username/id.

Re: mysql random update.. how to do it?

Posted: Tue Mar 31, 2009 9:13 am
by ViserExcizer
thanks i know the php random function can be used but sometimes it might choose a number that doesnt exist, or the user with that ID has been deleted, so it might return no value. Scottayy yours is ingenious.