mysql random update.. how to do it?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ViserExcizer
Forum Newbie
Posts: 24
Joined: Tue Nov 25, 2008 1:17 pm

mysql random update.. how to do it?

Post 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?
tech603
Forum Commoner
Posts: 84
Joined: Thu Mar 19, 2009 12:27 am

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

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

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

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
ViserExcizer
Forum Newbie
Posts: 24
Joined: Tue Nov 25, 2008 1:17 pm

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

Post 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.
Post Reply