Targeted key sorting in multi-dimensional array without disturbing order of other keys

I was wondering if it was possible to sort one particular key in a mutli-dimensional array without disrupting the order of the other keys? I'm going to to be doing a complex "ORDER BY" when selecting my table with the mysql query, however there's a limitation on how much I can do, and want to try to do the rest with the resulting array in PHP without destroying the sort already present. To elaborate, I'm unable to implement a "natural sort" on varchar fields with an "ORDER BY" -  I've scoured the net and tried multiple ideas, but nothing I've tried to apply a natural-sort-equivalent has worked, so I thought that could be the job of PHP.  A snippet of my resulting array from my table looks like this (the real one is quite a bit larger with a lot more keys):

    [1422] => Array
        (
            [pagename] => item000617
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => Warner Music Taiwan - January 1992 
            [country_full] => Taiwan
            [newyear] => 1992
            [format] => CD
        )

    [1423] => Array
        (
            [pagename] => item000898
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => Warner Music Taiwan - September 1999
            [country_full] => Taiwan
            [newyear] => 1999
            [format] => CD
        )

    [1424] => Array
        (
            [pagename] => item000782
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases"  17  
            [country_full] => United Kingdom
            [newyear] => 1992
            [format] => CD
        )

    [1425] => Array
        (
            [pagename] => item002553
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases" 103
            [country_full] => United Kingdom
            [newyear] => 1996
            [format] => CD
        )

    [1426] => Array
        (
            [pagename] => item000877
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases" 15  
            [country_full] => United Kingdom
            [newyear] => 1992
            [format] => CD
        )

Open in new window


Desired result:

    [1422] => Array
        (
            [pagename] => item000617
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => Warner Music Taiwan - January 1992 
            [country_full] => Taiwan
            [newyear] => 1992
            [format] => CD
        )

    [1423] => Array
        (
            [pagename] => item000898
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => Warner Music Taiwan - September 1999
            [country_full] => Taiwan
            [newyear] => 1999
            [format] => CD
        )


    [1426] => Array
        (
            [pagename] => item000877
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases" 15  
            [country_full] => United Kingdom
            [newyear] => 1992
            [format] => CD
        )

    [1424] => Array
        (
            [pagename] => item000782
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases"  17  
            [country_full] => United Kingdom
            [newyear] => 1992
            [format] => CD
        )

    [1425] => Array
        (
            [pagename] => item002553
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases" 103
            [country_full] => United Kingdom
            [newyear] => 1996
            [format] => CD
        )

Open in new window


I'm a bit green with coding so feel free to let me know if this is just impossible. Thanks in advance.
Coral SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gr8gonzoConsultantCommented:
Use usort.

usort($yourarrayofdata, "my_custom_sort");

function my_custom_sort($a,$b)
{
  if($a["pagename"] == "item000877")
  {
    if($b["pagename"] == "item000898") { return 1; }
    if($b["pagename"] == "item000782") { return -1; }
  }
  return 0;
}
0
Coral SAuthor Commented:
Thank you! I failed to include the important detail that I'm trying to sort this by the "title" key without disturbing the rest of the current sort -- hopefully my desired output code block makes a bit more sense. Given that in mind, can you elaborate on your solution gr8gonzo?
0
gr8gonzoConsultantCommented:
You'll need to define the criteria that separates "the rest" from the specific items you're trying to sort...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Coral SAuthor Commented:
I sorted first by category, then subcategory, then subsubcat, then title.  These were already sorted in my MYSQL select statement, and I don't want to have to re-order them (there are also many other keys to this table). The desired output moves the titles to their proper order but it doesn't change the category/subcategory/subsubcat sort, as seen below. Notice in particular the titles "Warner Music UK New Releases" 15, "Warner Music UK New Releases" 17, "Warner Music UK New Releases" 103 -- these are currently out of order in my original array, and below is the desired result.

[1422] => Array
        (
            [pagename] => item000617
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => Warner Music Taiwan - January 1992 
            [country_full] => Taiwan
            [newyear] => 1992
            [format] => CD
        )

    [1423] => Array
        (
            [pagename] => item000898
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => Warner Music Taiwan - September 1999
            [country_full] => Taiwan
            [newyear] => 1999
            [format] => CD
        )


    [1426] => Array
        (
            [pagename] => item000877
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases" 15  
            [country_full] => United Kingdom
            [newyear] => 1992
            [format] => CD
        )

    [1424] => Array
        (
            [pagename] => item000782
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases"  17  
            [country_full] => United Kingdom
            [newyear] => 1992
            [format] => CD
        )

    [1425] => Array
        (
            [pagename] => item002553
            [category] => Promotional Music
            [subcategory] => Compilations
            [subsubcat] => 
            [title] => "Warner Music UK New Releases" 103
            [country_full] => United Kingdom
            [newyear] => 1996
            [format] => CD
        )

Open in new window

0
Coral SAuthor Commented:
Sidenote: is there a way to edit my original question? I can't seem to find the edit button for the original q, only the comments after.
0
Julian HansenCommented:
@Coral, if I understand your question you are not getting the results you want from a MySQL query in terms of sorting. Can you give an example of your data, your query, the results you get and the result you want to get. When it comes to sorting you should be doing this in MySQL - PHP should be an absolutely last resort and used for those tasks a DB is not good at - which excludes sorting.
2
Coral SAuthor Commented:
Hi Julian,

I realized after posting that I worded my question quite poorly, hence the request for deletion. But perhaps I can salvage it by sharing the information I should have shared from the outset. I agree that sorting should be done by MYSQL, but I've been hitting a brick wall with one particular part of the sort, so I wondered if PHP was the only answer. Anyway, I will show you an example of my table array and the sort I require. Please note that I'm attempting to sort the following by table by "title" and failing miserably. There is an unpredictable mix of letters and numbers in each title, and the ones with numbers are especially not sorting properly. I'm working toward a "natural-sort" equivalent. You can see my current "ORDER BY" is not working:

DB Fiddle

Scroll toward the bottom of the Results and see how "Warner Music UK New Releases 103" comes before "Warner Music UK New Releases 15". I've tried about 20 different sorting techniques, many of which are illustrated on this page:

https://stackoverflow.com/questions/153633/natural-sort-in-mysql

I haven't tried the rather large function with 40+ upvotes which most people said worked best. The reason I haven't used it is due to the fact that I don't know how to implement MYSQL functions before making a SELECT/ORDER BY query. I don't know where to put the function,  or how to connect it up to the SELECT query, like at all. Plus my version of MYSQL may not be the same as the poster's. My MYSQL database version is 5.1.68, using PHP version 5.2.17.
0
Coral SAuthor Commented:
I forgot to mention that in this example, I used "col_name" to label my title column. And the results I wish to get are natural sort, alphabetical with letters and sorting numbers in ascending order.
0
Julian HansenCommented:
Ok I see your problem you are wanting it to sort lexicographically  on the alpha but when there is a number in the name to sort numerically.

I also see what you are trying to do in terms of "tricking" the numerical sort

However, that trick works when you have strings that are predominantly numbers such as 11.1 11.3 etc.

When you have a string with a random length number in the middle at random intervals you are not going to solve this one so easily. You need to remove the number from the string and append it to the value (as a number) to get that to work OR pad the numbers with the right number of zeros to make them the same length  - if the number was in a predictable position this would be possible but we are dealing with random names here - the number could be anywhere and any length.

Numbers in a string will sort naturally providing they are all the same length i.e XXX 103 and XXX 015 will sort as required - it is the missing leading 0 on the numbers that breaks the sort. If we knew the longest number's length we may be able to build a sort string with 0 padded numbers..

BUT, What happens if you have more than 1 number in a string

2 Flowers and 20 Trees
29 Flowers and 3 Trees
027 Flowers and 2 Trees

Or three sets of numbers in a string - the problem becomes quite complex.

The question is - at what point does the complexity of the solution outweigh the effort to solve it - in otherwords - would it not be a simpler solution to provide an ordering column that has a modified name or a sequential value that allows for ordering.

For instance - make an order column,
Copy the contents of the title into that column
Update the numbers in the strings to have sufficient leading 0's or alter name in some way to achieve required sort.

This may not be feasible if the dataset is large and or fluid.
1
Coral SAuthor Commented:
Julian,

Thank you for outlining my problem so eloquently. I'm relieved to hear that there's not some magic sort I was missing to put in SQL statement. I'm more than eager to make a separate column for sorting. I have complete control over what is saved into the database at any time, so I don't mind writing extra code to get something so necessary on the site to work.

You said:
"For instance - make an order column,
Copy the contents of the title into that column
Update the numbers in the strings to have sufficient leading 0's or alter name in some way to achieve required sort."

I'm quite excited to try this. I don't suppose you could take a few examples from the list on the DB Fiddle and elaborate on what you mean by "update the numbers in the strings to have sufficient leading 0's or alter name in some way to achieve required sort"? I understood "numbers in a string will sort naturally providing they are all the same length" -- so, is that what I have to do? Scan my entire database to find the longest number, and pad the zeros to make them all the same length? I can certainly program that in, but let me know if I have that correctly. Thank you SO MUCH for this. I had a feeling a separate ordering column would solve everything, since I've done it before, but I wasn't sure how to approach it, so thank you again.
0
Coral SAuthor Commented:
BTW I no longer believe deleting this question is necessary, since I was able to finally explain myself in a way that's not confusing. :)
0
Julian HansenCommented:
There are a number of ways of creating a sorting column. The simplest is a numeric field you put a number in. This is probably not going to work in your instance as you will have to keep renumbering the sequence when you insert records into the middle of the sorted record set so you would need something else.

With regards to your recordset I have taken two examples

Warner Music UK New Releases 103
Warner Music UK New Releases 15 

Open in new window

Options for the sort column - add a leading Zero
Warner Music UK New Releases 103
Warner Music UK New Releases 015 

Open in new window


I took these two examples from your resultset as well - here is where even a complex code solution will fall down - are you wanting to sort on volumn first or year first - in this case it happens to be sequential (and probably would always be for this type of data - volume / year) but it demonstrates another case where your string might have two different number sets that imply a different ordering depending on which number you want to have preference.

In this case the padding would work as well
Warner Music CD Sampler Vol. 17 - 1994
Warner Music CD Sampler Vol. 02 - 1992

Open in new window


These are just examples - an in depth analysis of the data might yield more efficient or appropriate keys for instance

Warner 001
Warner 002
Warner 003

Open in new window

Might work instead of the full name. It is really up to you and how your data works.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coral SAuthor Commented:
Julian,

Thank you so much! You've given me fantastic food for thought. Thankfully I have separate columns for the year and volume that will be sorted outside of the title (it just so happens that information is in the physical title on the item itself, hence why it was included) but I can easily search and filter those out for the title sorting column, since I always know that information will be in those separate columns. This finally feels within reach! Should I accept the answer now and post another question if I run into any issues, or try it out first before accepting your answer as the solution? Thank you again!
0
Julian HansenCommented:
It is up to you - if you feel the answer is enough to move forward you can accept - and then post back with another question if necessary.
0
Coral SAuthor Commented:
I think I have a pretty solid idea about where to go from here thanks to your insight, Julian. Many thanks and appreciation once again.
0
Julian HansenCommented:
You are welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
arrays

From novice to tech pro — start learning today.