943,107 Members | Top Members by Rank

Ad:

PHP Discussion Thread
Marked Solved
Views: 687

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Mar 20th, 2010

0

Is there something wrong with this query?

Expand Post »

php Syntax (Toggle Plain Text)
$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 1");
$row_table = mysql_fetch_assoc($results);
$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 1");
$row_table = mysql_fetch_assoc($results);

For some odd reason this does not return data, however if I change it to:

php Syntax (Toggle Plain Text)
$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 2");
$row_table = mysql_fetch_assoc($results);
$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 2");
$row_table = mysql_fetch_assoc($results);

I get data. Any ideas?

Last edited by Tekkno; Mar 20th, 2010 at 6:57 pm.

Similar Threads

HQL query in JAVA in Java
query problem case syntax error can't figure it out in MS SQL
Syntax Errors, Variables & Query, Oh my! in PHP
is my query wrong in MySQL
sql query in PHP

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Mar 20th, 2010

0

Re: Is there something wrong with this query?

Maybe the following.

PHP Syntax (Toggle Plain Text)
$results = mysql_query("SELECT column1, column2, date, time FROM table LIMIT RAND(),1");
$row_table = mysql_fetch_assoc($results);
$results = mysql_query("SELECT column1, column2, date, time FROM table LIMIT RAND(),1");
$row_table = mysql_fetch_assoc($results);

If that fails to return a row then rand() is returning a number higher than the number of rows present.

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,003 posts
since Sep 2007

Mar 21st, 2010

0

Re: Is there something wrong with this query?

That just gives me "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource..." on the line with the while loop. Which just means the query failed right? I'm not sure what you mean by:

Click to Expand / Collapse Quote originally posted by cwarn23 ...

If that fails to return a row then rand() is returning a number higher than the number of rows present.

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Mar 21st, 2010

0

Re: Is there something wrong with this query?

To make things easier the following.

PHP Syntax (Toggle Plain Text)
$results = mysql_query("SELECT column1, column2, date, time FROM table LIMIT ROUND(RAND(COUNT(*))),1");
$row_table = mysql_fetch_assoc($results);
$results = mysql_query("SELECT column1, column2, date, time FROM table LIMIT ROUND(RAND(COUNT(*))),1");
$row_table = mysql_fetch_assoc($results);

Last edited by cwarn23; Mar 21st, 2010 at 1:37 am. Reason: bug

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,003 posts
since Sep 2007

Mar 21st, 2010

0

Re: Is there something wrong with this query?

Thanks for your help cwarn,

I get the same error with that query. I also tried:

php Syntax (Toggle Plain Text)
$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 1");
$row_table = mysql_fetch_row($results);
$results = mysql_query("SELECT column1, column2, date, time FROM table ORDER BY RAND() LIMIT 1");
$row_table = mysql_fetch_row($results);

I do not receive an error with this, but no results. I have another php file that does a similar query to the same database and it works fine. Why would it work with ORDER BY RAND() LIMIT 2 but not ORDER BY RAND() LIMIT 1? Also, I am not sure if I mentioned this, but when I use a LIMIT of 2, it only returns one result. I just don't get it.

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Mar 21st, 2010

0

Re: Is there something wrong with this query?

Maybe we should start by asking whatt you are trying to accomplish with this query.
"Order BY" refers to how mysql returns the sort order. How does a random floating point between 0 and one do anything useful in this context?
I wonder how how you got any results at all.

JRM

Reputation Points: 130

Solved Threads: 75

Practically a Master Poster

Offline

618 posts
since Oct 2006

Mar 22nd, 2010

0

Re: Is there something wrong with this query?

Click to Expand / Collapse Quote originally posted by JRM ...

Maybe we should start by asking whatt you are trying to accomplish with this query.
"Order BY" refers to how mysql returns the sort order. How does a random floating point between 0 and one do anything useful in this context?
I wonder how how you got any results at all.

JRM,
The query is for recommendations, it is meant to randomize the results to get something different each time. Thus, the order by rand. By default how is a query ordered, by id I would assume? Doesn't do me much good if it displays the same values every time does it? If you have a better way I'm all ears.

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Mar 22nd, 2010

0

Re: Is there something wrong with this query?

Try each of the following

PHP Syntax (Toggle Plain Text)
$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 0,1");
$row_table = mysql_fetch_row($results);
$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 0,1");
$row_table = mysql_fetch_row($results);

PHP Syntax (Toggle Plain Text)
$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 2");
$row_table = mysql_fetch_row($results);
$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 2");
$row_table = mysql_fetch_row($results);

PHP Syntax (Toggle Plain Text)
$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 1,1");
$row_table = mysql_fetch_row($results);
$results = mysql_query("SELECT * FROM table ORDER BY RAND() LIMIT 1,1");
$row_table = mysql_fetch_row($results);

My theory is that the limit 1 statment wasn't designed to be ordered by random. But maybe we can make a work around this bug.

Last edited by cwarn23; Mar 22nd, 2010 at 3:12 am. Reason: code tags

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,003 posts
since Sep 2007

Mar 22nd, 2010

0

Re: Is there something wrong with this query?

Thanks cwarn, I will give that a try. I just don't understand why the same query in a different script and same database, just different data, works fine. I will let you know the results.

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Mar 22nd, 2010

0

Re: Is there something wrong with this query?

Tried back-ticking your fields? I don't know if it will make any difference. I know that DATE and TIME have a particular meaning in MySQL.

Sponsor

Featured Poster

Reputation Points: 1041

Solved Threads: 935

Sarcastic Poster

Offline

6,620 posts
since Oct 2006

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: I am new Plz Help

Next Thread in PHP Forum Timeline: Styles in a php include

About Us | Contact Us | Advertise | Acceptable Use Policy

Forum Index | Build Custom RSS Feed

© 2011 DaniWeb® LLC