Page 1 of 1
sorting a multidimension array by f1 then f2
Posted: Thu Aug 24, 2006 1:40 am
by mameha
In my mySQL database I have a 3 level list of products.
e.g.
> Fruits (catNo = 010000)(nSort = 5)
>> Sweet (011000)
>>> Apple (011010)
>>> Pear (011020)
>> sour (012000)
>>> lemon (012010)
>>> lime
> Vegetables(020000)(nSort = 1)
>> Green
>>> Cucumber
>>> Lettuce
>> Red
>>> Paprika
>>> Radish
The list can be sorted by catNo or nSort.
Problem: I want to reorder this list so that Vegetables appears above fruits.
So, I want to sort it by nSort, but also maintain the 3level structure. Currently if I sort it only by nSort the list structure goes crazy. So I need to sort it by nSort and also by catNo somehow.
You can see the relationship of catNo to the structure (XXxxxx = 1st level or not, xxXXxx = 2nd level or not etc).
I think the answer is to do with using:
Code: Select all
SELECT * FROM table WHERE catNo IN(select * from table order by catNo) ORDER BY nSort
...but I cant get it to work, I get '#1241 - Operand should contain 1 column(s) ' error.
Or, I can just get the list and order by catNo, then sort it using PHP with
- but I have no idea how. Any ideas?
Posted: Thu Aug 24, 2006 3:08 am
by jamiel
The way you have presented your table structure to us is very ambigious. However I believe what you are looking for is the
GROUP BY function. Read that Document, and give it another shot.
Posted: Thu Aug 24, 2006 3:52 am
by mameha
I think GROUP BY will help, but I'm still not sure how to put the query together.
At first, I think I need to get the top level categories (ones that end have 4 zeros at the end of catNo - e.g. 010000). The following query does this perfectly:
Code: Select all
SELECT catno, nSort FROM product_category WHERE RIGHT(catNo,4) = '0000' ORDER BY nSort
Next, I need to sort these top level categories by the nSort. Then, I need to get all the sub categories and display them.
I wonder if its possible to even do this in mySQL in a single query...?
Posted: Thu Aug 24, 2006 5:14 am
by jamiel
I don't understand what your final output should be. Is it all in one display, all the top level categories orders by catNo and then by nSort, followed by all the subcategories below?
Posted: Thu Aug 24, 2006 9:57 am
by feyd
If this is to be done in php,
array_multisort() may be of interest.
Posted: Thu Aug 24, 2006 7:24 pm
by mameha
I don't understand what your final output should be. Is it all in one display, all the top level categories orders by catNo and then by nSort, followed by all the subcategories below?
Yes - exactly.
The problem is, the sub categories have nSort numbers too (I dont know why) so when I just do a simple ORDER BY catNO, nSort it comes out all wrong (the subcategories get put under the wrong main category).
Posted: Thu Aug 24, 2006 7:39 pm
by mameha
I have reformatted this data to make the problem easier to understand:
DATA STRUCTURE:
catNo = int(6) unsigned zerofill
name = varchar(64)
nSort = int(4)
DATA
(I have added > marks to make the menu structure clearer)
name / catNo / nSort
-------------------------
> Fruits / 010000 / 5
>> Sweet / 011000 / 1
>>> Apple / 011010 / 1
>>> Pear / 011020 / 2
>> sour / 012000 / 2
>>> lemon / 012010 / 1
>>> lime / 012011
> Vegetables / 020000 / 1
>> Green / 021000 / 1
>>> Cucumber / 021010 / 1
>>> Lettuce / 021011 / 2
>> Red / 022000 / 1
>>> Paprika / 022010 / 1
>>> Radish / 022011 / 2
Menu level understood by catNo:
top level = last 4 digits are 0000 (e.g. fruits, vegetables)
2nd level = last 2 are 00 (e.g. sweet, sour)
3rd level = last 2 are not 00 (e.g. apple, pear)
I think I need to do something like this:
* get top level items only, order by nSort
* now get subcategories and put under them under the correct top level category.
Posted: Fri Aug 25, 2006 3:43 am
by jamiel
This is possible in one query yes, but what I suggest is you ditch that table completely and normalize it. With limited knowledge on what you are trying to do, I suggest a seperate table which contains each item with a catNo and a category id that links to a category.
You have made things unneccessarily complex for yourself and you will run into more problems later.
Posted: Fri Aug 25, 2006 3:58 am
by mameha
I agree.
I ended up solving this problem by adding an extra column called nSortTopLvl. all vegetables become 1, all fruits 2, etc. Then I just select all and order by nSortTopLvl, catNo.
This data was inherited but unfortunately I am not allowed to remove the catNo fields so I had to do this lame workaround.