MySQL ordering of results - problem

[duncang]

Right, appealing to all you MySQL lovers:

I have three tables:

• lindoProducts, which stores the ID of the type of instrument it is in(instID)
• lindoInstruments, which in turn stores the ID of the type of category it is in (categoryID)
• lindoCategories

lindoInstruments and lindoCategories also have a field called ordering, so that I can order the categories on the page, and within these order the instruments, and then list products by name.

I am trying to do exactly this, but also allow for other selction criteria, for example selecting all products whose description contains the word "ultra".
I have this:

SELECT c.* FROM lindoProducts c, lindoInstruments p, lindoCategories q WHERE p.categoryID=q.ID AND c.instID=p.ID AND c.active='1' AND c.description LIKE 'ultra' ORDER BY q.ordering DESC

This seems to be working fine, but I am getting a very odd occurance.
For some reason, although it is ordering the categories fine, within these, it is not grouping the products by instrument type. So, I am getting:

Category: Guitars
Instrument Type: Electric
then Instrument Type: Bass
then Instrument Type: Electric again

So it seems like I am missing some sub ordering or grouping.

Any ideas?

Duncan

[napper]

Try

ORDER BY q.ordering DESC, p.CategoryID ASC