CODEWITHSUNDEEP
phpmysqlsorting
CBuzatu
CBuzatu

Reputation: 785

Sorting MySQL with PHP

I have some relative simple questions about how it works MySQL with PHP. For beginning I have created "products" table. The table is this:

-----------------------------------------------------------------------
| name                          | brand   | type      | color | price |
|-------------------------------+---------+-----------+----------------
| Samsung S5230 (white          | samsung | s5230     | white |    80 |
| Samsung S5230 (black)         | samsung | s5230     | black |    95 |
| Samsung Wave 723 (black)      | samsung | wave723   | black |   200 |
| Apple iPhone 4G 16GB (white)  | apple   | iphone4g  | white |   500 |
| Apple iPhone 4G 32GB (white)  | apple   | iphone4g  | white |   600 |
| Apple iPhone 4G 16GB (black)  | apple   | iphone4g  | black |   450 |
| Apple iPhone 4G 32GB (black)  | apple   | iphone4g  | black |   550 |
| Apple iPhone 3GS 16GB (white) | apple   | iphone3gs | white |   300 |
| Apple iPhone 3GS 8GB (black)  | apple   | iphone3gs | black |   200 |
-----------------------------------------------------------------------

I want to create a PHP script who sorts my products. I want to sort combinations in the picture below: enter image description here

The first image is the default state.
The second image is when the Samsung checkbox is checked.
The third image is when the Apple checkbox is checked.
Sort by price menu has 3 options: Random, Ascending, Descending.
PRODUCT LIST HERE is the location where i want to make the MySQL selection.
Thank you for your time, regards.

Upvotes: 2

Views: 347

Answers (2)

Itai Sagi
Itai Sagi

Reputation: 5615

Well, considering that you want to so many options to sort, I would advise against writing a billion SQL Queries to get the correct order, as it is in no-awy scalable or efficent, not to talk about - a design flaw.

I would instead get the entire result set, and sort it using javascript, there is a great jQuery plugin for that, called tablesorter

Upvotes: 1

Madara's Ghost
Madara's Ghost

Reputation: 175098

Well, to solve this kind of problems (Where not all products have the same filtering (which is by the way, different than sorting) fields), I use a many to many relationship table, for instance:

FieldsToBrands
-------------------
brand_id | field_id
-------------------
1          1
1          2
2          2

Brands
---------------------
brand_id | brand_name
---------------------
1          Apple
2          Samsung

Fields
---------------------
field_id | field_name
---------------------
1          Color
2          Space

Which then means that Apple has both Color and Space, and Samsung only has Space. This is called a many-to-many relationship between tables.

Upvotes: 0

Related Questions