How To Use Rand() With Databases

[Roxana]

Whenever I randomize the values of an array, I create a second array that acts as a flag, so no value gets repeated. Whenever a value is chosen from the main array, the second array gets a one (for instance) in the corresponding place.

When working with tables what should the solution be? Should I save the table entries in an array and do as I said above or should I create a temporary column that does the same thing?This randomization would restart every time somebody acceses the page, so in the end the column would be deleted.

For instance a piece of advice offered by the computer each time someone presses a button.

[HartleySan]

I would avoid randomizing things on the DB side. I think that will put an unnecessary load on the DB.

And yeah, I think you hit the nail on the head in that doing it in PHP would also create an unnecessary load as well as cause possible repeats.

As such, I can think of two practical solutions:

 

1) Use the $_COOKIE or $_SESSION superglobal to persistency store a randomized array across individual calls to the PHP script.

2) Use Javascript, which is a client-side language.

 

If you'd like more details about the actual code needed to implement this, just ask.

Thanks.

[Roxana]

Yeah, you are right about cookies. I couldn't even use the new column correctly if many visitors were there at the same time. Thanks. I've worked a little with cookies before, I will keep on reading and if I need help or I want to show you my code I will be back.

[Roxana]

I'm back.

 

This is how I learned to access a table a few years ago:

 

$result = mysql_query("SELECT * FROM users") or die(mysql_error());

while($row = mysql_fetch_array( $result )) {

$myvalue = $row['email']

....

}

where of course $myvalue could also be an array.and all its values would still exist out of the loop.

 

I'm reading chapter 6, and in chaper 5 the book says:

 

SELECT email FROM users ORDER BY RAND() LIMIT 1

 

So then:

 

$i=0;

$result = mysql_query("SELECT email FROM users ORDER BY RAND()") or die(mysql_error());

 

while($row = mysql_fetch_array( $result )) {

$myvalue[$i] = $row['email'];

$i++;

}

Would this automatically randomize my data? All at once? And save it in an array? No more need for flag arrays to keep track of the selected entries?

Would I still need cookies?

My coding will be so much better by the time I finish this book!

Up till now I would generate a random number with php and pick the primary key that had that value in the table.

[Larry]

Keep in mind that query only selects 1 row, so you don't need a loop and it doesn't create an array.

 

I think using ORDER BY RAND() is perfectly reasonable for most people and situations. When you have a high demand, you'd want to do something different. If you only want one random record, you could, for example, generate a random number and then select the first record whose primary key is less than that (you wouldn't want to do an equals here, because there could be gaps in the sequence or the generated number could be too high).

 

If you need multiple random records on a high demand site, you can create another table that acts as a randomizer and use it to randomly select records from the main table.

[Antonio Conte]

I would get all rows. (assuming you don't have a huge amount to select for

) I would then store all rows in a session array, and randomly select from that. Only my two cents.

[HartleySan]

Antonio, the one possible problem I see with that is potentially repeating various quotes, since Roxana would be forced to regenerate the random order of the array every time.

Instead, Roxana might want to randomize the array once at the beginning, store that random array in a session/cookie, and then each time she uses a quote, shift it off the beginning of the random array.

Of course, the array in the session/cookie doesn't have to be random initially to avoid repeats, but I think it makes the process of removing the correct value from the array a lot easier.

In addition, if/when the random quotes array becomes empty, she could then regenerate a new random array.

 

Anyway, there are a number of ways of doing this, and I'm kinda guessing at Roxana's desired behavior, so I suppose we should wait for her input before I step on your toes anymore, as your method is perfectly fine and efficient under certain circumstances.