View Full Version : (basic?) Query problem

01-27-2007, 03:02 PM
Trying to use Scuttle, but have a query that returns the correct results, but ignores the ORDER BY request:

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

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.

01-27-2007, 03:29 PM
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:

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.

01-27-2007, 03:34 PM

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.

01-27-2007, 03:47 PM
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.

01-27-2007, 03:52 PM
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.