Order by

Ordering mysql

[ikke]

Hello,

Does somebody know how you can do ORDER BY not alfabetic or something just custom the way I want.

LIST:
test1
test2
test3
test4

Now I want to sort that list on this way:

ORDER BY list:
test3
test1
test4
test2

Has somebody has an idea?
my code so far(Does not work):

$result = mysql_query("SELECT productcategory FROM `products` WHERE `Shopc` LIKE 'Winkel' GROUP BY productcategory ORDER BY productcategory='test3','test1', 'test4', 'test2'");

Thanks

[arran]

There is no such ORDER BY - you can't assume there will be a set number of rows returned (in this case 4). It looks like your data is static so you could introduce another table as a (nasty) workaround:

ProductCategory(productCatID, productCategory)

-- Use the productCatID to assign numerical values based on the ordering you want.

-- Your SQL would then be something like:

SELECT productcategory FROM products, productCategory WHERE `Shopc` LIKE 'Winkel' products.productcategory = productCategory.productCategory and GROUP BY productcategory ORDER BY productCategory.productCatID

[ikke]

That means that I need to give al the products a new number that gives to much work with it. But thanks for your suggestion.

I put the items that I get out of the database into an array and from there I say that for each category in the array build a product list maybe I can do something with the array. Is there something in php that can order a array in my own style?

Thank again for your help

[olwen]

That means that I need to give al the products a new number that gives to much work with it.

I don't see that. You need to build a table with the productcategory as an id. Then put an order field in that table.

[ikke]

O so, I did not catch you, But the problem is solved I did the following

$result = mysql_query("SELECT productcategory FROM `products` WHERE `Shopc` LIKE 'Winkel' GROUP BY productcategory ORDER BY productcategory=''"); 

So I left it empty and it is a good order. But when the client is not statisfied with it I use your idea. Thanks a lot.

Bye,

[arran]

That's some crazy SQL you got there :)