Sorting MySQL with PHP

Question

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.

I want to create a PHP script who sorts my products. This is what I want. What you see in the image is a html printscreen with no functions that doesn't works. I want to create a functional PHP script. — CBuzatu
– CBuzatu, Commented Mar 25, 2012 at 16:03
What should happen if both the Apple and the Samsung checkboxes are checked? — Arjan
– Arjan, Commented Mar 25, 2012 at 16:25
@Sara It works for me, but try here instead i.sstatic.net/GZpSF.png — Mattias
– Mattias, Commented Mar 25, 2012 at 17:59

Itai Sagi · Accepted Answer · 2012-03-25 16:18:33Z

1

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

answered Mar 25, 2012 at 16:18

Itai Sagi

5,60513 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1191247 Over a year ago

You think sorting the entire result set in js is more efficient and more scalable than writing a query that returns the desired records? It may well be more efficient if the dataset is small but it is not more scalable.

Itai Sagi Over a year ago

@nnichols I was speaking in context, ofcourse for a large company with a query which pulls a billion records per day I won't advise to do that. put for a dataset of 50-100 records, tablesorter handles that beautifully.

CBuzatu Over a year ago

Thank you very much for advice, but I don't want to create a table. I want to create a PHP script who sort my product with links: for example if i want to display from MySQL table all Apple (brand) iPhone 4G (type) with this link: products.php?brand=apple&type=iphone4g

CBuzatu Over a year ago

I want to create sorting combinations between brand, type, name, and price.

user1191247 Over a year ago

Filtering, not sorting. So what have you tried? There are many articles out there on getting started with PHP and MySQL. This is a Q&A site, not a we'll write it for you for free site. How many products will be in your table?

Madara's Ghost · Accepted Answer · 2012-03-25 16:03:02Z

0

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.

answered Mar 25, 2012 at 16:03

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

3 Comments

CBuzatu Over a year ago

And how cand i do this with PHP? What Space means?

Madara's Ghost Over a year ago

Space is an example field (4GB, 8GB, 16GB, etc)

CBuzatu Over a year ago

Thank you very much. I had a confusion between sorting and filtering. What I looked for was filtering. This code helped me so much to do what I wanted to do: [enter link description here][1] [1]: codingforums.com/showthread.php?t=216525

Collectives™ on Stack Overflow

Sorting MySQL with PHP

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related