Getting Random data from db

[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 not so simple:

 

$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;}

continues...........

 

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

[receiver]

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;

 

I really need a good working example. what am I doing wrong

 

Thank you

 

[fenway]

I assume you got those from the sticky -- what currently doesn't "work" about it?