Page 1 of 1

Random sql row where hitsleft>0

Posted: Mon Jun 27, 2005 1:37 am
by Trenchant
For a sponsor system I'm designing I need to select a random row from a database. The only thing is that the row has to have a higher value than 0 for a column hitsleft.

Currently I have this:

Code: Select all

$sql = mysql_query("SELECT * FROM qc_sponsors WHERE hitsleft > '0' LIMIT 1") or DIE("The system needs more sponsors to be operational. (in_sql-01)");
The only thing is it selects the first row it finds. I need it to select a random one.

Any ideas?

Posted: Mon Jun 27, 2005 2:10 am
by shailendra
Hi,

Try this query.

SELECT * FROM qc_sponsors WHERE hitsleft > '0' order by rand() LIMIT 1


This helps you

Thanks

Shailendra

Posted: Mon Jun 27, 2005 2:20 am
by Chris Corbyn
The function RAND() can be used in a nice way here ;)

Code: Select all

SELECT * FROM qc_sponsors WHERE hitsleft > '0' ORDER BY RAND() LIMIT 1
EDIT | DOH! I was typing this post for 10 mins between jobs at work LOL :P

Posted: Mon Jun 27, 2005 10:24 am
by Trenchant
Thank-you very much. That worked amazingly.

I never thought of using rand in the actual query. I've never done that before.

Thanks again for the help.

Posted: Thu Jun 30, 2005 8:24 pm
by Trenchant
New problem on this topic. I now need to select a row where one of the database columns are NOT null.

This is what I have. It doesn't display an error message but it doesn't work either.

Code: Select all

$sql = mysql_query("SELECT * FROM qc_sponsors WHERE hitsleft > '0' AND answer1!='null' ORDER BY rand() LIMIT 1") or DIE(mysql_error());

Posted: Thu Jun 30, 2005 8:38 pm
by timvw

Code: Select all

SELECT *
FROM bar
WHERE foo IS NOT NULL

Posted: Fri Jul 01, 2005 7:59 pm
by Trenchant
This still won't work.

Heres the code:

Code: Select all

$sql = mysql_query("SELECT * FROM `qc_sponsors` WHERE `hitsleft` > '0' AND `answer1` IS NOT NULL ORDER BY rand() LIMIT 1") or DIE(mysql_error());

Posted: Sat Jul 02, 2005 3:53 am
by timvw
That should be:

Code: Select all

hitsleft > 0
Or if you insist on escaping the column name:

Code: Select all

`hitsleft` > 0
'0' is not the same as 0