Jump to content


Photo

MySQL Ordering Troubles


  • Please log in to reply
1 reply to this topic

#1 dunksy

dunksy
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 24 May 2006 - 09:20 AM

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


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 May 2006 - 03:33 PM

SELECT c.* 
FROM lindoProducts c
LEFT JOIN lindoInstruments p ON ( c.instID = p.ID )
LEFT JOIN lindoCategories q ON ( p.categoryID = q.ID ) 
WHERE c.active='1' AND c.description LIKE 'ultra' 
ORDER BY q.ordering DESC
Well, no, this query won't do any grouping -- if any of these are one-to-many, you'll get them all. If you want to group by instrument type, you have to say so.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users