Re: php, mysql How to do a random selection and limit response?

From: "Leonard B Burton" <lbburt0@uky.edu> > I would like to do a random selection in mysql > [select * from members order by rand() limit $limit] > and then I'd like to insert the items selected into a > table [named already_selected] with a datetime > stamp to make sure one item is only sleceted once > over a defined period of time. > > Would it be best to do the select and then an > insert? Would it be best to do a left join in The select statement? > > Can anyone suggest a more efficient way? I'd avoid duplicating the data in another table. Any way you do this, you're going to have to lock the table between the SELECT and the UPDATE to prevent problems / duplicates. Lock the tables, select your random X rows, update a "already_selected" and/or datetime column in those X rows (using a primary key), then unlock the tables. You'll also have to check if there are X "unselected" rows in the table before you begin, too. You'll also have to periodically "clear" the selected rows so you can begin over, too. ---John Holmes...