Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9

Thread: SQL error

  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts

    SQL error

    using this query...
    PHP Code:
    "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....
    PHP Code:
    "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)

  • #2
    Regular Coder
    Join Date
    Apr 2004
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Firstly try using < maybe not sure on what order you want? if ASC/DESC didnt help.

    PHP Code:
    "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.

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


  • #3
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).

  • #4
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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?

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hm. If mysql_error() isn't retuning a syntax issue, than it must be something else. What version of MySQL are you using?

  • #6
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    it is 4.1, with php 5 on iis 5

  • #7
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

    Code:
    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.
    Last edited by Kid Charming; 06-12-2005 at 04:46 AM.

  • #8
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Give this one a shot:

    Code:
    (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

  • #9
    Regular Coder
    Join Date
    May 2005
    Posts
    563
    Thanks
    0
    Thanked 3 Times in 3 Posts
    worked!

    Thanks.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •