rand() selecting database entries

Worker Joined: Feb 18, 2004 Posts: 244

Hi,

I'm trying to randomly select one record from a database. At the start of the function I don't know how many records there are in the database. The id fields are not guaranteed to be sequential either.

Could anyone offer some suggestions on the best way to do this?

Code:

// Seed the generator





mt_srand((double)microtime() * 1000000);





// Find out how many rows are in the database





$total = $db->sql_numrows($db->sql_query("SELECT * FROM ".$prefix."_rtc"));





// Is there a better way to do this, because I'd hate to have to select the whole thing when I ultimately only want 1 entry.





// Okay, now I want a random number between 1 and total records in the database


$num = rand(1,$total);





// Okay now to go out and fetch the actual record...  What is the where clause???  I definitely don't get that.





$card=$db->sql_query("SELECT * FROM ".$prefix."_rtc" Where ___??___)


list($id, $name, $img_url, $description) = $db->sql_fetchrow($card);

I know this code dosent work, but any suggestions on improving it and a bit of theory on why would really help me a lot.

Thanks!

Darrell

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Assuming I haven't made any typo's (I didn't test this) you could strip it down to

Code:

// Seed the generator





mt_srand((double)microtime() * 1000000);





// Find out how many rows are in the database





list($total) = $db->sql_fetchrow($db->sql_query("SELECT count(*) FROM ".$prefix."_rtc"));





// Is there a better way to do this, because I'd hate to have to select the whole thing when I ultimately only want 1 entry.





// Okay, now I want a random number between 1 and total records in the database


$num = rand(1,$total);





// Okay now to go out and fetch the actual record...  What is the where clause???  I definitely don't get that.





list($id, $name, $img_url, $description) = $db->sql_fetchrow($db->sql_query(SELECT id, name, img_url, description FROM ".$prefix."_rtc LIMIT $num,1"));

Posted: Thu Jun 02, 2005 6:44 am

The first part is doing as I expected:

Code:

list($total) = $db->sql_fetchrow($db->sql_query("SELECT count(*) FROM ".$prefix."_rtc")); 


$content .="<tr><td align=\"center\">".$total."</td></tr>";





$num = rand(1,$total); 


$content .="<tr><td align=\"center\">".$num."</td></tr>";

The first content statement always prints total records in the database. I've never seen SELECT count(*) and I'm having trouble finding it on the net, but I'll keep looking. Anyway, that's working.

The second content always prints out a value from 1 to total records, including the 1 and the total records number. So that's working perfectly!

This one:

Code:

list($id, $name, $img_url, $description) = $db->sql_fetchrow($db->sql_query(SELECT "id, name, img_url, description FROM ".$prefix."_rtc LIMIT $num,'1'"));

Generates a error:

Quote:

Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in /i/removed/path/block-Random_Card.php on line 21

I can probably figure out and fix the error. I'm definitely going to try.

Thanks for getting me started!

Darrell

Posted: Thu Jun 02, 2005 7:32 am

It should be

Code:

list($id, $name, $img_url, $description) = $db->sql_fetchrow($db->sql_query("SELECT id, name, img_url, description FROM ".$prefix."_rtc LIMIT $num,1"));

Posted: Thu Jun 02, 2005 2:59 pm

Thanks Raven,

I'd of cought that quotation being off eventually. Smile

Does anyone know where a good place is to read what the various PHP errors mean online?

I have a PHP Bible, and have been to several places online, but the things I need arent easy to find.

I also need a better understanding of the LIMIT x,x clause because it isent working quite like I thought it would.

Right now it pulls cards one thru (total records -1) correctly, but when the randomizer calls for the last card in the database it dosent work, and it dosent print an error either.

It does pull the first card in the database correctly.

Links to good reference material is fine with me!!

thanks,
Darrell

Posted: Thu Jun 02, 2005 3:11 pm

Why are you doing (total records - 1)? Try (total records). Limit x,y simply says move the data pointer to table record/row x and retrieve y number of rows.

Posted: Thu Jun 02, 2005 3:44 pm

I'm not doing total records minus 1.

I'm saying that the clause (limit x,y) is working for all random numbers up to one less than total records in the database. It is also working for record number 1... Which only reinforces that I should not be doing total records -1... Very Happy

Just to be perfectly clear I'll repost it for you here:

Code:

$content = "<table width=\"100%\" border=\"0\">";





// Seed the generator 


mt_srand((double)microtime() * 1000000); 





// Find out how many rows are in the database 


list($total) = $db->sql_fetchrow($db->sql_query("SELECT count(*) FROM ".$prefix."_rtc")); 


$content .="<tr><td align=\"center\">".$total."</td></tr>";  // Temporary output used for testing





$num = rand(1,$total); 


$content .="<tr><td align=\"center\">".$num."</td></tr>";  // Temporary output used for testing





list($id, $name, $img_url, $description) = $db->sql_fetchrow($db->sql_query("SELECT id, name, img_url, description FROM ".$prefix."_rtc LIMIT $num,1"));





$content .= "<tr><td align=\"center\"><img src=\"".$img_url."\"></td></tr>";


$content .= "<tr><td align=\"center\">".$description."</td></tr>";





$content .= "</table>";

Sorry to have mislead you.

If the ramdomizer calls by chance for the last record in the database it dosent work. This image is not displayed, and the description is not displayed. No errors.

Any ideas on places I can read up on this stuff please?

Thanks,
Darrell

Posted: Thu Jun 02, 2005 3:52 pm

php.net

Posted: Thu Jun 02, 2005 4:01 pm

thanks

Regular Joined: Mar 14, 2005 Posts: 91

another way to do it would be

Code:




"SELECT field, field from table ORDER BY rand() ASC LIMIT 1"

Posted: Fri Jun 03, 2005 7:04 am

Wow,

That's a neat idea gotcha! I did not realize you could embed another function call inside the select statement!

I finally found some information about the LIMIT clause on the net somewhere late last night.

It turns out I was wrong.

This will not return a random number including lowest thru highest record.

Code:

$num = rand(1,$total);

It turns out that the first selectable record is a 0 with the LIMIT clause so this was what I needed:

Code:

$num = rand(0,($total-1));

Now it's working perfectly!

It's amazing what a little documentation will do for ya!

Thanks,
Darrell