[SOLVED] Trying to get a random row from db, mysql error:

[aebstract]

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 'Resource id #3, 1' at line 1

 

Here is my code atm:

 

<?php
mysql_connect("****","****","****") or die(mysql_error());
mysql_select_db("****");

$generated_number = mysql_query("SELECT COUNT(*) AS cnt FROM mailer") or DIE(mysql_error());

$myquery = mysql_query("SELECT * FROM mailer LIMIT $generated_number, 1") or DIE(mysql_error());

while($r=mysql_fetch_array($myquery))
{

$id=$r["id"];
$flyer=$r["flyer"];
$date=$r["date"];

}


echo "$flyer";
?>

[revraz]

Why are you setting the first LIMIT parameter to the number of rows of the first query?

 

Looks like you are trying to start at the end of the table.

[aebstract]

http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

 

Which in that article, this is all that is relevant:

 

You can use something like:

SELECT COUNT(*) AS cnt FROM quotes

generate random number between 0 and cnt-1 in your programming language and run the query:

SELECT quote FROM quotes LIMIT $generated_number, 1

Yes. This are two queries, but they are MUCH faster than the first one. This option is good if you need just one random row.

 

Which is what I got going on but I have that error.. Which I just realized I wasn't getting the random number, so here is my updated:

 

$generated_number = mysql_query("SELECT COUNT(*) AS cnt FROM mailer") or DIE(mysql_error());

$random_number = rand(1, $generated_number);

$myquery = mysql_query("SELECT * FROM mailer LIMIT $random_number, 1") or DIE(mysql_error());

while($r=mysql_fetch_array($myquery))
{

$id=$r["id"];
$flyer=$r["flyer"];
$date=$r["date"];

}

and here is my updated error:

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 ' 1' at line 1

[revraz]

You should put your queries in a variable so you can echo them to find the problem.

 

$sql = ("SELECT * FROM mailer LIMIT $random_number, 1") or DIE(mysql_error());

$myquery = mysql_querry($sql) or DIE ("Error in $sql" .mysql_error())

 

 

Also, echo $generated_number and $random_number to see what they contain.  If $random_number is empty, then your query will have a blank value followed by your comma then 1.