sorting a multidimension array by f1 then f2

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mameha
Forum Newbie
Posts: 13
Joined: Thu Aug 24, 2006 1:36 am

sorting a multidimension array by f1 then f2

Post 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

Code: Select all

asort
- but I have no idea how. Any ideas?
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
mameha
Forum Newbie
Posts: 13
Joined: Thu Aug 24, 2006 1:36 am

Post 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...?
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If this is to be done in php, array_multisort() may be of interest.
mameha
Forum Newbie
Posts: 13
Joined: Thu Aug 24, 2006 1:36 am

Post 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).
mameha
Forum Newbie
Posts: 13
Joined: Thu Aug 24, 2006 1:36 am

Post 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.
jamiel
Forum Contributor
Posts: 276
Joined: Wed Feb 22, 2006 5:17 am
Location: London, United Kingdom

Post 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.
mameha
Forum Newbie
Posts: 13
Joined: Thu Aug 24, 2006 1:36 am

Post 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.
Post Reply