Order By

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leebo
Forum Commoner
Posts: 44
Joined: Sun Oct 20, 2002 9:49 am

Order By

Post by leebo »

Hi

If I had a database with the following fields in:

Brand
Description
Size
Price

And one record out of 20 with brand 'Adidas' would be:

Brand: Adidas
Description: Samba
Size: 7-10
Price: £40

And one record out of 20 with brand 'Puma' would be:

Brand: Puma
Description: Sprint
Size: 8-11
Price: £60

How would I display all records order by the Brand field ?


I.E. I want to display all records with adidas first - Or display all records with Puma first etc...

can this be done ?
User avatar
Leviathan
Forum Commoner
Posts: 36
Joined: Tue Sep 23, 2003 7:00 pm
Location: Waterloo, ON (Currently in Vancouver, BC)

Post by Leviathan »

No, you can't display all the records for an arbitrary brand first. You can order by the brand, either ascending or descending, but assuming that there's a middle brand (say Nike; it's alphabetically after Adidas and before Puma), you can't have all the records grouped by brand with Nike coming first. If you really wanted Nike first, you'd have to do 2 queries: one to return Nike, and one to return all the rest. (Of course, to force a specific ordering of ALL your brands, you'd have to do one query per brand).
nirma78
Forum Commoner
Posts: 42
Joined: Wed Sep 17, 2003 2:02 pm

Post by nirma78 »

Yes I agree

If you want an list by alphabetical order ie. the ones with 'a' first and then 'b' and 'c' and so on.. you can use order by brand_name
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

In similar situations, I added a "sequence" field to my table so that I could specify (with integers) what order I'd like my rows to appear. Something like this might help you.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Ha! Naysayers. It can be done all within the query.

SELECT *, FIELD(brand,'Puma','Adidas','Nike') AS this_order FROM table ORDER BY this_order
User avatar
Leviathan
Forum Commoner
Posts: 36
Joined: Tue Sep 23, 2003 7:00 pm
Location: Waterloo, ON (Currently in Vancouver, BC)

Post by Leviathan »

*Applauds* Well, I learned something then. Good for you, and thanks!
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Sure. It's not everyday that I get to totally show off. 8)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Brands ought to be stored in a lookup (one to many) table - although it is acceptable to de-normalise a relational database in certain circumstances.

You're also missing a unique, integer primary key for catalogue items.

http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf

Various db articles - see concepts of database design & management:
http://phpcomplete.com/tutorials.php?sec=Databases

Don't mean to be nit-picking: a good db design is the foundation of a php program.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Well you would want to store the Brand as a column in the item table anyway, because without that you've got no way to reference your handy brand table... unless I've missed your point.

Agree that it should have an auto_increment key.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Unipus wrote:Well you would want to store the Brand as a column in the item table anyway...
Yes but as a foreign key and not a brand value - check out the links above.
Post Reply