selecting random data from db - other methods

[receiver]

Hello,

Need to get random data from db but order by rand() is too slow. i have tried other methods but i can’t make them work properly

i found simple solution :

SELECT * FROM table WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM table ) ORDER BY id LIMIT 1;

or this :

$offset_result = mysql_query( ” SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM table “);
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( ” SELECT * FROM table LIMIT $offset, 1 ” );

i can’t make it work with this script:

//$_SESSION['sess_data']['usrid']=3;

//$_SESSION['useridtest']=3;

$criteria="(select category_id from user_category where user_id=" .$_SESSION['sess_data']['usrid'].") ";

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and  s.state='Enabled'";
$query.=" and s.id=sc.siteid and sc.category in $criteria ";
if ($_SESSION['sess_data']['negact'] == 0) {$query = $query . " and  credits>=1";}
$query = $query . " and  ((cth < cph) or (cph=0))";
$query = $query . " order by rand() limit 8";
//echo $query;

//overided query
//$query="select id, url from site";
$res = mysql_query($query);


echo("");
//echo $query."<br>";
//echo "number of rows= ".mysql_num_rows($res1);
//echo "number of rows1= ".mysql_numrows($res1);
echo("
<br><table width="700" border=0 align=center cellpadding=5 cellspacing=15 bordercolor="">");
$col=2;




if(mysql_numrows($res)==1){$rows=1;}
//if(mysql_numrows($res)==3){$rows=2;}
//if(mysql_numrows($res)==5){$rows=3;}

continue...........

i tried this –

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and  s.state='Enabled' and WHERE s.id >= (SELECT FLOOR(MAX(id) * RAND()) ";

this doesn’t work like this, right?

Thank you very much

[Zyppora]

Your last query will fail because you’re using the ‘WHERE’ clause twice If it reflects what you want, then your random number is simply a value in your query, right? Can’t you just run the random query first (or even just run a query and let PHP’s rand() do the job) and then insert that value into the next query?

[receiver]

oh that “where” was just my forum quick example mistake, i have tried like this and more:

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and  s.state='Enabled' and s.id >= (SELECT FLOOR(MAX(id) * RAND())";

and this:

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and   s.state='Enabled'  and s.id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `site` ) ORDER BY id LIMIT 8";

and this;

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `site` ) ORDER BY s.id LIMIT 8 and s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and   s.state='Enabled'";

and this:

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and  s.state='Enabled'";
$query.=" and s.id=sc.siteid and sc.category in $criteria ";
if ($_SESSION['sess_data']['negact'] == 0) {$query = $query . " and  credits>=1";}
$query = $query . " and  ((cth < cph) or (cph=0))";
$query = $query . " and s.id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `site` ) ORDER BY id LIMIT 8";
//echo $query;

can you please write an example, how you think it should be. I really need good working example

Thank you

Found Solution that works: (added this)

$res = mysql_query($query);
//select random
$res = mysql_query("SELECT *
FROM site
WHERE id >= FLOOR( RAND( ) * (
SELECT MAX( id )
FROM site ) )
ORDER BY id ASC
LIMIT 8");

But this doesn’t get right results at all

[Zyppora]

What you’re doing in all those queries is not extracting random rows from your database. You’re extracting all rows with an ID that’s above a certain random value. In your first query of your first post however, you use ‘LIMIT 1’. What exactly are you expecting back? One row, or a random number of rows? And if a number of rows, then should they be randomly picked, or somehow bound to a match (as used in your WHERE clause)?

[receiver]

yep I know that did not work.

thank you

I got the right code somehow to make faster because order rand() is slow.

faster way, this is what I needed(solution):

$query = "select distinctrow s.id,s.name,s.`desc`,s.url from site s,site_category sc where s.usrid!=" .$_SESSION['sess_data']['usrid'] . " and s.state='Enabled'"; $query.=" and s.id=sc.siteid and sc.category in $criteria "; if ($_SESSION['sess_data']['negact'] == 0) {$query = $query . " and credits>=0";} $query = $query . " and ((cth < cph) or (cph=0))"; $query = $query . " and s.id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `site`)-(SELECT MIN(id) FROM `site`)) + (SELECT MIN(id) FROM `site`))) ORDER BY id LIMIT 8";

thank you again