Page 1 of 1

ORDER BY Confusion

Posted: Tue Feb 09, 2010 9:59 am
by easinewe
Hi,

I have written the following query:

$sql = "SELECT * FROM `Catalog` WHERE title = '{$article[alsolike]}' OR title = '{$article[alsolike2]}' ORDER BY 1"

I get the resulting titles that I want but would like them to be listed in such a way that result of $article[alsolike] is listed first and $article[alsolike] is listed second. I suspect that I must modify my ORDER BY clause in some way but I'm not sure how.

What would be the correct way to write this query?

Re: ORDER BY Confusion

Posted: Tue Feb 09, 2010 10:46 am
by JNettles
Is this a SQL question or a PHP one? If its PHP then just arrange your echo $results['field'] in the way that you want them.

Re: ORDER BY Confusion

Posted: Tue Feb 09, 2010 1:31 pm
by easinewe
So there is no way to just have them echo in the order they appear in the database with a ORDER BY function?

Re: ORDER BY Confusion

Posted: Tue Feb 09, 2010 1:40 pm
by AbraCadaver
easinewe wrote:So there is no way to just have them echo in the order they appear in the database with a ORDER BY function?
If you omit the ORDER BY, then they should appear in the order they are in the database. If you want to order by something then it needs to be a column in the database, like: ORDER BY title.

Re: ORDER BY Confusion

Posted: Tue Feb 09, 2010 6:45 pm
by Weirdan
easinewe wrote: $sql = "SELECT * FROM `Catalog` WHERE title = '{$article[alsolike]}' OR title = '{$article[alsolike2]}' ORDER BY 1"

I get the resulting titles that I want but would like them to be listed in such a way that result of $article[alsolike] is listed first and $article[alsolike] is listed second. I suspect that I must modify my ORDER BY clause in some way but I'm not sure how.
If I understand you correctly, you want to display rows matching the " title = '{$article[alsolike]}' " condition first, followed by records matching the " title = '{$article[alsolike2]}' " condition. If that's how you want it then it's easy to do using ORDER BY on an expression:
[sql] SELECT * FROM `Catalog` WHERE   title = '{$article[alsolike]}'   OR title = '{$article[alsolike2]}' ORDER BY title = '{$article[alsolike]}'  [/sql]
or more generic (with support for more than 2 expressions OR'ed together):
[sql] SELECT * FROM `Catalog` WHERE     title = '{$article[alsolike]}'     OR title = '{$article[alsolike2]}'--  ...ORDER BY FIELD(1, title = '{$article[alsolike]}'                , title = '{$article[alsolike2]}'--              , ...) [/sql]