CODEWITHSUNDEEP
phpmysqlgrouping
Ovidiu
Ovidiu

Reputation: 161

Order MySQL result by column value

I'm sorry if this is bad being my 2nd post today, but I need a little help one more time. I have table in mysql DB which holds some products( id, name, description, price, url, CATEGORY ). Now, when I display the products, I need to first display all products having category='X' ( regardless of the actual order of the products in the table ), than the products with category="Y" and finally the products with category="Z". Having only 3 categories, I just added the column to the products table, without creating a separate 'category' table (I know it's bad). This is the simple code to pull out all the products(I can't use PDO due to the fact the website is old and done only with old/regular mysql)

while($prod = mysql_fetch_assoc($sql)) {
    $id = $prod['id'];
    $titolo = $prod['titolo'];
    $descr = $prod['descrizione'];
    $prezzo = $prod['prezzo'];
    $foto_piccola = $prod['url_foto_piccola'];
    $foto_grande = $prod['url_foto_grande'];
    $cat = $prod['categoria'];
            // display all products
}

So any ideas how can I do this easy/fast ? My first guess was to make a switch('category') inside the while() loop, but I end up with a lot of duplicate code.

Any help and suggestions are very appreciated, Thank you

This is my $sql $sql = mysql_query(" SELECT * FROM prodotti Order BY categoria='marmellata' DESC, categoria='altro' DESC, categoria='oli' DESC")

Bit this way doesn't work. I want to pull the name of the category and below it display the products of that category

Upvotes: 0

Views: 200

Answers (2)

Deepak Rai
Deepak Rai

Reputation: 52

You can even use Field() function of SQL 

order by Field(category, X, Y, Z)

Upvotes: 1

Jonathan Pellerin
Jonathan Pellerin

Reputation: 432

You can do Order By category if your category field is an INT and your categories are entered in the order you want.

-OR-

You can do Order BY category='X' DESC, category='Y' DESC, category='Z' DESC if your categories are entered by name (although I would suggest entering them by ID)

Upvotes: 1

Related Questions