...

View Full Version : SQL error



SeeIT Solutions
06-11-2005, 02:38 PM
using this query...

"SELECT * FROM `news` WHERE `time` > '$time' ORDER BY `time` ASC LIMIT 2;"
I get no errors but I get the 2 news items in reverse order that I want them in.

Using this query....

"SELECT * FROM (SELECT `id` FROM `news` WHERE `time` > '$time' ORDER BY `time` ASC LIMIT 2);"
I get an error.


mySQL query error:
0 :
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\sketchbattle\new\index.php on line 188

Anyone know what the problem is or how to reverse the order of the results without using an array. (Changing ASC to DESC doesn't work as I want the 2 dates closest to the timestamp passed)

gsoft
06-11-2005, 04:10 PM
Firstly try using < maybe not sure on what order you want? if ASC/DESC didnt help.



"SELECT * FROM (SELECT `id` FROM `news` WHERE `time` > '$time' ORDER BY `time` ASC LIMIT 2);"

Echo the Query out and run it in PHPMyAdmin see what comes up, from what I can tell if your using MySQL you cant do that however my SQL Skills arent that great so I could be wrong.



Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\sketchbattle\new\index.php on line 188

If your getting to the mysql_fetch your not doing any error checking.



if (($result = mysql_query($query))
{
// do stuff
}
else
{
//error Query Failed
}

Kid Charming
06-11-2005, 06:09 PM
The 'supplied argument...' error comes from a failed query causing mysql_query() to return FALSE -- since functions like mysql_fetch_array() take a resource as an argument, a boolean FALSE raises an error. Most of the time, this is due to a syntax error; you can get MySQL's error message by using mysql_error() -- it'll give you more specific information.

In this case, I'm assuming your version of MySQL is lower than 4.1, so you can't use subqueries.

Without subqueries, though, I don't know of a way to get what you're wanting in a query alone, aside from using a temporary table, which seems like more trouble than just rearranging your results in PHP (which I would do using mysql_data_seek() to manipulate the pointer in the original result set instead of a separate array).

SeeIT Solutions
06-12-2005, 02:09 AM
mySQL query error:
0 :
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\sketchbattle\new\index.php on line 188

The mysql error is returned at the top and under that there is the error in php because of it failing. sql didnt return an error, just a blank one.

Also, I've tried every possibility of < > ASC & DESC. None get what I want.

How do you use that mysql_data_seek u speak of?

Kid Charming
06-12-2005, 02:18 AM
Hm. If mysql_error() isn't retuning a syntax issue, than it must be something else. What version of MySQL are you using?

SeeIT Solutions
06-12-2005, 02:44 AM
it is 4.1, with php 5 on iis 5

Kid Charming
06-12-2005, 03:39 AM
Then your problem was probably that you didn't set an alias on your subqueried table -- when a subquery results in another table that your main query is pulling from, that subquery must have an alias:



SELECT
foo
FROM
(SELECT
bar
FROM
t2) AS st


[Edit]
Your original subquery still won't work, though -- you can't have LIMIT in a subquery -- let me see what I can come up with and get back to you.

Kid Charming
06-12-2005, 04:00 AM
Give this one a shot:



(SELECT * FROM news WHERE `time` > '$time' ORDER BY `time` ASC LIMIT 1)
UNION ALL
(SELECT * FROM news WHERE `time` > '$time' ORDER BY `time` ASC LIMIT 1,1)
ORDER BY
`time`
DESC

SeeIT Solutions
06-12-2005, 04:55 AM
worked!

Thanks.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum