How To Use Rand() With Databases

[Roxana]

I'm back and here is what I've done:

 

I have a site that teaches people English (I'm Romanian).

Once I decide on the best code for generating random words, I will make a page that tests one's vocabulary - by generating one word at a time for the user to translate.

 

I created a dummy database containing english words and their translations.

 

I do not know if I should use sessions - I read that sessions are used mostly to save data when changing pages, I will not have multiple pages. I might use cookies when the page is done to ask returning visitors the words they did not know last time.

 

But apart from that, could someone explain why I would need them? Data security doesn't matter, I would think.

 

Since there are quite a few ways of doing this and I don't know which is best, I developed them all.

 

I came up a few years ago with a code that generates random numbers, but I never was completely pleased with it, a while in a for loop seemed too much.

 

if (isset($_POST['submit'])) {

$nr=$_POST['nr'];

for ($i=0;$i<$nr;$i++){ //initialize the position array, that will hold the positions of chosen numbers

 

$ord0[$i]=0;

}

for ($j=0;$j<$nr;$j++){ //the loop that finds the numbers

$param=0; //flag

while ($param==0){

$i=rand(1,199); //generate one random number

if ($ord0[$i]!=-1){ //if generated number is new

$ord1[$j]=$i; //save it in the array of randomized numbers

echo $j.'-'.$array1[$i].'<br>';

$ord0[$i]=-1; //update the position array

$param=1; //stop while (break)

}

}

}

}

http://grafitgroup.r...dictionary0.php

 

Then, yesterday I found the Fisher-Yates algorithm, which has just one loop, I found it here for Javascript

 

http://sedition.com/...ascript-fy.html

 

(I had to change the --i to i-- in php, though it says the --i is the right way for their javascript implementation.)

 

 

if (isset($_POST['submit'])) {

$nr=$_POST['nr'];

while ($nr--) {

$j = rand(0,$nr);

$tempi = $array1[$nr];

$tempj = $array1[$j];

$array1[$nr] = $tempj;

$array1[$j] = $tempi;

echo $nr .'-'.$array1[$nr].'<br>';

}

}

I think this is a fantastic method because it moves the numbers found to the back of the list and shortens the randomization interval each time so that the numbers found cannot be found again. It is obviously better than my code. It would need to run all the way to 200 though and either be stopped somewhere along the way or pick the first $nr of numbers after.

 

http://grafitgroup.r...dictionary3.php

 

Then there is the method that Larry's book inspired me to try, using rand()

 

$i=0;

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

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

$array1[$i]=$row['eng']; //save the randomized numbers/words in an array

$i++;

}

 

if (isset($_POST['submit'])) {

$nr=$_POST['nr'];

for ($i=0; $i<$nr; $i++){

echo $i .'-'.$array1[$i].'<br>'; //print array

}

}

http://grafitgroup.r...dictionary4.php

 

And last but not least, there is Larry's method, which is very good if I change the words on a daily basis, for instance if I have 20 Words of the Day which the user must know. I created a new table with random numbers which I joined with the words table. The random number table can be smaller than 200 rows.

 

 

$result = mysql_query("SELECT example.eng, aleator.id FROM example,aleator WHERE example.example_id = aleator.numar") or die(mysql_error());

 

if (isset($_POST['submit'])) {

$nr=$_POST['nr'];

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

echo $i .'-'.$row['eng'].'<br>';

$i++;

if ($i==$nr){

break;

}

}

}

 

http://grafitgroup.r...dictionary5.php

 

How do I compare these methods in terms of speed in the case of one or many simultaneous visitors?

[HartleySan]

I'm not sure if you're asking more about the best way to randomize the vocab or the best way to handle the site, but I'll try to address both.

 

For randomizing an array, I'd first get all the terms from the DB and place them in a PHP array or JS array. If you're not sure how to do this, please ask (although it looks like you're using Larry's method for the PHP version, which is fine).

Once you have the values in an array, you can easily randomize them in PHP as follows:

 

shuffle($arr);

 

That's it! The following is a link to the reference page about the shuffle function:

http://php.net/manua...ion.shuffle.php

 

For JS, all you have to do is call the sort method, which is supported by all array objects in JS. For example:

 

arr.sort();

 

There are some caveats with the JS sort method though. If you're dealing with all numbers or non-ASCII values, you'll have to do something like the follows:

 

arr.sort(function () {

 return 0.5 - Math.random();

});

 

The syntax here is a bit tricky, so if you're not sure what's going on, please ask.

You can get more details about this method from the following page:

http://www.javascrip...arraysort.shtml

 

I think that's all you need to know about randomizing an array.

As for sessions, cookies, etc., it all depends on what experience you want to create for your user vs. how much work you want to do.

 

The simplest way to do this is have a PHP page where every time you load it, you grab a random vocab from the DB and display it. The problems with this method are two-fold:

1) Every time you load the page, you'll have to requery the DB, which is time consuming.

2) You can't keep track of the vocab that have already been used unless you do something like send a list of the used vocab in a URL parameter to the PHP script, which is cumbersome and not efficient.

 

A slightly more advanced (but I think actually easier) solution is to uses sessions or cookies. Both are very easy to use in PHP (please see Larry's book or any Internet resource on the topic). By using a session or cookies, you can very easily keep track of the vocab that's already been covered. Better yet, you could do something like query the DB the very first time someone goes to your site, and instead of grabbing only one vocab from the DB, grab all the vocab from the DB, randomize it, and then store that randomized array in a session or cookie. After that, even though you'd have to reload the PHP script for each vocab, you wouldn't have to requery the DB every time. Naturally, you'd also have to include some sort of counter variable in the session or a cookie in order to keep track of which vocab have already been covered.

 

With all that said, I think the best solution for this is to use JS. By using JS, the using will never have to reload the page. The user loads the page once, the entire randomized array is placed in a JS variable, and after that, JS takes over, meaning that you don't have to reload the page between vocab. If you're not familiar with JS, this is a more difficult solution, but for something like this, I honestly think it's the only reasonable solution.

If you decide you want to go this route and need some help, please ask.

 

Thanks, and hope that answers your questions.

[Roxana]

Hi,

Thank you for your advice to work in JS (which I can manage) but because I want to perfect my PHP (that is why I am reading this book) I will continue with php, if possible.

Now, I havr the following 2 problems:

1. So far my php code has an if else conditional as explained in the book:

 

if (isset($_POST['submit'])) {

$crtnr++;

echo $_SESSION['array'].;

echo 'hi';

}else{

... main code ... download table...shuffle array (must happen just once)

 

$_SESSION['array']=$array1[1];// This is probably why you said I need to save the data, so it will exist when the if condition validates.

}

However, when the if validates this value is not displayed.

 

Second Problem:

 

2. If I reload the page the $_POST['submit'] probably is not erased and I cannot restart the vocab. I have to open a new window.

I use this:

 

 

header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past

 

but it does not help.

 

http://grafitgroup.r...dictionary0.php

[HartleySan]

Honestly, I would probably take a completely different approach to this problem.

 

The first time someone starts up the vocab game (which you can easily determine by checking for the existence of the appropriate session/cookie value(s)), I'd query the DB, get back all the vocab, randomize them, and then store the entire array with a counter variable into the session/cookie. Also, I would display the first vocab in the randomized array to start the game.

After that, if the sesion/cookie exists (i.e., they've been to your site before, I'd skip over the querying of the DB and get the next value in the randomized array in the session/cookie, which would be based off of the counter variable you store with the randomized array in the session/cookie. Naturally, you'd want to increment the counter variable in the session/cookie by one each time to keep track of things.

In the event that the user gets through the entire array, you could then re-query the DB and re-randomize the array and store it in the session/cookie as well as restart the counter. In other words:

 

if (session/cookie does not exist || counter value exceeds max array value) {

// Query the DB and randomize the array. Also, reset the counter variable.

}

// Regardless of whether the if statement above is performed or not, display the next vocab in the array.

// When the user clicks next, increment the counter by one and store the new value in the session/cookie.

 

Anyway, that's what I'd do.

Thoughts?

[Roxana]

Ok - I see the differences.

But my SESSION problem remains:

 

Here is the code I wrote.

 

<?php

header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past

if (isset($_POST['submit'])) {

$crtnr++;// increment the current number (counter)

echo $_SESSION['array'].'<br>';//for now just show this saved variable exists.

echo 'hi';//if the above echo didn't work, at least print 'hi' to show the conditional was validated.

}else{

session_start();

mysql_connect("mysqldb.com", "username", "password") or die(mysql_error());

mysql_select_db("mydb") or die(mysql_error());

$i=0;

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

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

$array1[$i]=$row['eng'];

$i++;

$_SESSION['array']=$array1[1]; //For the moment I am only storing the first entry, that will change when it works.

}

$page_title="Initial Method";

$php_action="dictionary0.php";

$nr=20;//number of words that will be displayed

$crtnr=0; // initialize counter

//RANDOMIZE:

....

}

include ('common1.html');

?>

[HartleySan]

Correct me if I'm wrong, but don't you always have to execute session_start(), regardless of whether you are starting a new session or continuing an existing one?

[Roxana]

Oh, ok thank you.

Now it works.

[HartleySan]

Excellent! Great to hear!

And oh my! My 1000th post! Yikes!

[Roxana]

This is the code.

Since I am still using the dummy table, anybody can check out the page.

 

For eng143 write rom143 (for instance) to get a correct answer.

 

I checked it and double checked it, but it you find any mistakes or have any comments please let me know.

I will trim spaces and decapitalize the answers soon.

Also, in the end the page will have a menu so going to another page will not be a problem.

http://grafitgroup.ro/dictionary11.php

Thanks everyone.

(I did not include the small html codes)

 

<?php

header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past

if (isset($_POST['submit'])) {

session_start();

function rezultat($rez){

echo $_SESSION['id'].".".$_SESSION['eng'][$_SESSION['crtnr']]." - ".$_SESSION['rom'][$_SESSION['crtnr']]."<br> Raspunsul tau: <i>".$_POST['rom']."</i> este ".$rez.$_SESSION['corecte'];"<br><br>";

}

$_SESSION['id']=$_SESSION['id']+1;

if ($_POST['rom']==$_SESSION['rom'][$_SESSION['crtnr']]){

$_SESSION['corecte']=$_SESSION['corecte']+1;

rezultat('corect!');

}else{

rezultat('incorect');

}

$_SESSION['crtnr']=$_SESSION['crtnr']+1;

}else{

session_start();

mysql_connect("mysqldb.com", "username", "password") or die(mysql_error());

mysql_select_db("test") or die(mysql_error());

$i=0;

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

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

$eng[$i]=$row['eng'];

$rom[$i]=$row['rom'];

$i++;

}

for($i=0;$i<200;$i++){

$ord1[$i]=$i;

}

shuffle($ord1);

for($i=0;$i<200;$i++){

$eng1[$i]=$eng[$ord1[$i]];

$rom1[$i]=$rom[$ord1[$i]];

}

$_SESSION['eng']=$eng1;

$_SESSION['rom']=$rom1;

$_SESSION['crtnr']='0';

$_SESSION['corecte']='0';

$_SESSION['id']='0';

 

}

if ($_SESSION['id']=='20'){

unset($_POST['submit']);

include ('common2.html');

$_SESSION['corecte']='0';

$_SESSION['id']='0';

}else{

include ('common1.html');

}

?>

[HartleySan]

Seems to be working fine.

Best of luck with expanding that code and getting what you want.