How to get a random row from a table

Posted: Sat Apr 07, 2007 11:16 pm

I thought this block might work, but it's way too simple, so naturally it's not working. Wink

All I'm getting is nothing but blank after the last working block. I checked my error logs and no help there. I'm guessing it's a mysql issue, but I could be wrong.

What I'm trying to do should be pretty obvious.
table nuke_mymod with columns called name and picpath.
ie: name picpath
CodyG 123.jpg

The block should display a random row with name followed by 123.jpg. Simple right? There are less than 200 rows in the table, in case you were thinking I should use something other than RAND().

Any ideas as to why this isn't working would be appreciated. A fix will be rewarded with lunch if you are ever in my neighbourhood.

I've tried various permutations of quoting and non-quoting, this is the latest...

Code:

<?php





if ( !defined('BLOCK_FILE') ) {


    Header('Location: ../index.php');


    die();


}








 $imagepath = 'modules/mymod/images/thumbs/';


 $result = $db->sql_query("SELECT RAND( ) , `name` , `picpath` FROM nuke_mymod WHERE picpath != '' LIMIT 1");


 $row = $db->sql_fetchrow($result);


 $content= "$row['name'] <br /> $imagepath.$row['picpath']";


?>

Posted: Sun Apr 08, 2007 10:09 pm

Let's just grab the count and seed the random number in PHP

Code:




$imagepath = 'modules/mymod/images/thumbs/';


$numrows = $db->sql_numrows($db->sql_query("SELECT * FROM nuke_mymod WHERE picpath != '' LIMIT 1"));


if ($numrows > 1) {


   $numrows = $numrows-1;


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


   $num = mt_rand(0, $numrows);


} else {


   $num = 0;


}


$result = $db->sql_query("SELECT `name` , `picpath` FROM nuke_mymod WHERE picpath != '' LIMIT " . $num . ", 1");


$row = $db->sql_fetchrow($result);


$content= "$row['name'] <br /> $imagepath.$row['picpath']";

Posted: Tue Apr 10, 2007 6:34 am

CodyG, I hope you do not mind my changing your thread title. If you disagree with it, please do not hesitate to "adjust". Wink

Posted: Tue Apr 10, 2007 7:38 am

I don't understand why you have a LIMIT 1 on the first SQL query evaders? Won't that only return 1 row?

I might suggest changing the query to be "SELECT COUNT(*) FROM ..." as it might be less resource intensive. That will just return a count of the number of rows that matched the query. Maybe try this (untested)

Code:




$sql = 'SELECT COUNT(*) FROM nuke_mymod WHERE picpath != \'\'';


$numrows = 0;


if ($result = $db->sql_query($sql))


{


   $row = $db->sql_fetchrow($result);


   $numrows = $row[0];


}





// no need to call mt_srand() in PHP 4.2.0 or greater


$num = mt_rand(0, $numrows);





// and the rest as per evaders:


$result = $db->sql_query("SELECT `name` , `picpath` FROM nuke_mymod WHERE picpath != '' LIMIT " . $num . ", 1"); 


$row = $db->sql_fetchrow($result); 


$content= "$row['name'] <br /> $imagepath.$row['picpath']";

Posted: Tue Apr 10, 2007 10:31 am

Thank you my friends. I should remember to write the title after I write the post. Smile

I checked my syntax three times and get the same results with all versions of the code, any further table loading on the page is stopped.
I tried punching up the php error reporting. nada. Maybe a mysql die() function would help.

this is what it looks like so far...

Code:

<?php


error_reporting(E_ALL^E_NOTICE);








if ( !defined('BLOCK_FILE') ) {


    Header('Location: ../index.php');


    die();


}








$imagepath = '/modules/mymod/images/thumbs/';


$numrows = $db->sql_numrows($db->sql_query("SELECT COUNT(*) nuke_mymod WHERE picpath != \'\";


$numrows = 0;





// g





if ($result = $db->sql_query($sql))


{


   $row = $db->sql_fetchrow($result);


   $numrows = $row[0];


}











/* e


if ($numrows > 1) {


   $numrows = $numrows-1;


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


   $num = mt_rand(0, $numrows);


} else {


   $num = 0;


}


*/











$result = $db->sql_query("SELECT `name` , `picpath` FROM nuke_mymod WHERE picpath != '' LIMIT " . $num . ", 1");


$row = $db->sql_fetchrow($result);


$content= "$row['name'] <br /> $imagepath.$row['picpath']";











?>

Posted: Tue Apr 10, 2007 10:51 am

Run the SQL queries in phpMyAdmin just to see if they are working the way you (and we) expect.

Posted: Tue Apr 10, 2007 10:58 am

The first query produced 76.
The second produced this error.

Quote:

SELECT `name` , `picpath`
FROM nuke_mymod
WHERE picpath != ''
LIMIT " . $num . ", 1
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" . $num . " , 1
LIMIT 0, 30' at line 1

Posted: Tue Apr 10, 2007 12:49 pm

When you run that 2nd one in phpMyAdmin you sort of have to act like PHP and fill in the values first. So what does this return?

SELECT name, picpath FROM nuke_mymod WHERE picpath != '' LIMIT 42, 1

Here, I just picked 42 as the random number.

I also noticed you have the code that computes $num commented out. Put that back in before running the second query (in the actual PHP code, not in phpMyAdmin).