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
Trenchant
Forum Contributor
Posts: 291 Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS
Post
by Trenchant » Mon Jun 27, 2005 1:37 am
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 » Mon Jun 27, 2005 2:10 am
Hi,
Try this query.
SELECT * FROM qc_sponsors WHERE hitsleft > '0' order by rand() LIMIT 1
This helps you
Thanks
Shailendra
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Mon Jun 27, 2005 2:20 am
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
Trenchant
Forum Contributor
Posts: 291 Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS
Post
by Trenchant » Mon Jun 27, 2005 10:24 am
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.
Trenchant
Forum Contributor
Posts: 291 Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS
Post
by Trenchant » Thu Jun 30, 2005 8:24 pm
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 » Thu Jun 30, 2005 8:38 pm
Code: Select all
SELECT *
FROM bar
WHERE foo IS NOT NULL
Trenchant
Forum Contributor
Posts: 291 Joined: Mon Nov 29, 2004 6:04 pm
Location: Web Dummy IS
Post
by Trenchant » Fri Jul 01, 2005 7:59 pm
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 » Sat Jul 02, 2005 3:53 am
That should be:
Or if you insist on escaping the column name:
'0' is not the same as 0