Page 1 of 1

Order By

Posted: Wed Sep 24, 2003 12:34 pm
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 ?

Posted: Wed Sep 24, 2003 12:41 pm
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).

Posted: Wed Sep 24, 2003 1:50 pm
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

Posted: Wed Sep 24, 2003 2:15 pm
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.

Posted: Wed Sep 24, 2003 4:46 pm
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

Posted: Wed Sep 24, 2003 5:09 pm
by Leviathan
*Applauds* Well, I learned something then. Good for you, and thanks!

Posted: Wed Sep 24, 2003 6:17 pm
by Unipus
Sure. It's not everyday that I get to totally show off. 8)

Posted: Wed Sep 24, 2003 6:24 pm
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.

Posted: Wed Sep 24, 2003 7:11 pm
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.

Posted: Wed Sep 24, 2003 8:13 pm
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.