Random sql row where hitsleft>0

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Trenchant
Forum Contributor
Posts: 291
Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS

Random sql row where hitsleft>0

Post 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?
shailendra
Forum Newbie
Posts: 13
Joined: Mon Jun 20, 2005 12:20 am
Location: INDIA
Contact:

Post by shailendra »

Hi,

Try this query.

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


This helps you

Thanks

Shailendra
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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
User avatar
Trenchant
Forum Contributor
Posts: 291
Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS

Post 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.
User avatar
Trenchant
Forum Contributor
Posts: 291
Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS

Post 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());
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT *
FROM bar
WHERE foo IS NOT NULL
User avatar
Trenchant
Forum Contributor
Posts: 291
Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS

Post 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());
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Post Reply