Ovidiu Ovidiu - 2 years ago 130
MySQL Question

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

Answer Source

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


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)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download