ORDER BY Confusion

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
easinewe
Forum Newbie
Posts: 23
Joined: Mon Jan 11, 2010 3:22 pm

ORDER BY Confusion

Post 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?
User avatar
JNettles
Forum Contributor
Posts: 228
Joined: Mon Oct 05, 2009 4:09 pm

Re: ORDER BY Confusion

Post 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.
easinewe
Forum Newbie
Posts: 23
Joined: Mon Jan 11, 2010 3:22 pm

Re: ORDER BY Confusion

Post 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?
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: ORDER BY Confusion

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: ORDER BY Confusion

Post 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]
Post Reply