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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (basic?) Query problem

    Trying to use Scuttle, but have a query that returns the correct results, but ignores the ORDER BY request:
    Code:
    select distinct sql_calc_found_rows b.*, u.username from sc_users as u, sc_bookmarks as b 
    where b.uid = u.uid and ((b.bstatus = 0) or (b.uid = 1)) and b.uid = 1 ORDER BY b.bDatetime desc
    I received this suggestion but it is giving me a SQL error

    Code:
    SELECT DISTINCT sql_calc_found_rows b*, u.username
    FROM sc_bookmarks b
    LEFT JOIN sc_users u ON (u.uid = b.uid)
    WHERE ((b.bstatus = 0) OR (b.uid = 1)) AND b.uid = 1
    ORDER BY b.bDatetime DESC
    I am running MySQL 4.1.21 and PHP 4.4.4 as CGI.

    Any help appreciated.
    Last edited by dkiesow; 01-27-2007 at 03:08 PM. Reason: Add PHP info

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    remove the DISTINCT, since you have primary keys in your table then every row of your table is unique and thus your query will return every row anyway.

    secondly, don't use SELECT *, actually name the columns you need from both tables.

    here is a minor problem:
    Code:
    WHERE ((b.bstatus = 0) OR (b.uid = 1)) AND b.uid = 1
    you are imposing a requirement in the AND clause that already exists in your OR clause, thus any rows returned must have a b.uid of 1. if there are rows you want to capture with b.status = 0 but a uid of a different value, those will not be returned.


    next is your datetime column a varchar/char type or are you actually using a type of datetime. by the way you should rename the column. while datetime is not on the reserved words list, it is the type of a column and thus could be easily mixed up.

    after that if you show the relevant rows in your table (with only columns necessary for clarity), to show how the order by clause is ignored that might help me or someone else assist you. right now I can only guess at the problem as above.
    Last edited by guelphdad; 01-27-2007 at 03:32 PM.

  • #3
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    Thanks.

    My changes to the query are somewhat constrained by the fact that this is someone else's code. I did discover the problem with the second query was a missing "." - so I am going to give that a shot.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    note that the only difference is that you are using an INNER JOIN in the first query and an OUTER in the second. it will return rows from the first table without matches in the second table, but it won't solve your order by problem, nor the other issues I point out.

  • #5
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got ya.

    OK - well I am out of my depth then. I have pointed out the issue to the original progammer - hoping for a solution there.

    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
    •