Sort multidimensional array by one or two columns

Is there a command/function to do this?

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 32815 Views - Last Post: 23 September 2008 - 12:26 PM Rate Topic: -----

#1 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Sort multidimensional array by one or two columns

Posted 22 September 2008 - 12:44 PM

I've tried array_multisort, but that just sorts the columns you tell it to, and independently of one another. Hardly useful. :angry:
Is This A Good Question/Topic? 0
  • +

Replies To: Sort multidimensional array by one or two columns

#2 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 01:39 PM

And you're looking for...what, precisely? Sorting a multi-dimensional array "by one or two columns" could mean any number of things. Perhaps you could give an example of the behaviour you're looking for.
Was This Post Helpful? 0
  • +
  • -

#3 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 02:13 PM

The most common need, of course! Sort a column and the others follow. No point in sorting by last name if the first names won't budge.
Was This Post Helpful? 0
  • +
  • -

#4 grimpirate  Icon User is offline

  • Pirate King
  • member icon

Reputation: 149
  • View blog
  • Posts: 714
  • Joined: 03-August 06

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 02:52 PM

array_multisort in fact provides the solution you're asking for. If it's sorting some other way, then you're likely using the function incorrectly.
Was This Post Helpful? 0
  • +
  • -

#5 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 03:41 PM

Ok, it seems to be doing that if I only use the array name, but how do I choose which column(s) to use? I have an array with N rows and 4 columns, and I'd like to be able to sort by the 4th column, and then a sub sort by the third column where two items have identical values in the 4th column.
Was This Post Helpful? 0
  • +
  • -

#11 Ridikule  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 68
  • Joined: 29-September 05

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 04:50 PM

This would be easier if you would provide the data arrays (in print_r format).

So, just guessing, I'm going to assume you have the most common problem. You have a two dimensional array representing row and column. Your data may looks something like this:
$arr[0] // first row of data (array)
$arr[0][0] // first column of first row


The problem here is that array_multisort is going to sort the values of an array. You have nothing to pass it in this format, if you pass it a sub array, you will be passing it a row value and it will basically be sorting them horizontally and not vertically (what a mess).

What you have to do is break out each column of data into it's own array.
foreach( $arr as $key => $row )
{
      $column1[$key] = $row[0];
      $column2[$key] = $row[1];
      $column3[$key] = $row[2];
      // etc
}



Then, in order to build an array that has the result's you'd typically expect (in the same format as the original array), you would do a multisort like this:
array_multisort($column3, SORT_ASC, $column1, SORT_ASC, $column2, SORT_ASC, $arr);


This is the equivalent of, say, and order by column3, column1, column2, in sql. Notice you pass the original array as the last parameter, so that it will sort by the common key of the original array (put it back in context).
Was This Post Helpful? 1

#12 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 04:59 PM

I see. So then PHP doesn't really have arrays with true rows and columns. They're more like lists. Applescript does this too, but at least they call them lists. :blink:

Thanks for the help.
Was This Post Helpful? 0
  • +
  • -

#13 grimpirate  Icon User is offline

  • Pirate King
  • member icon

Reputation: 149
  • View blog
  • Posts: 714
  • Joined: 03-August 06

Re: Sort multidimensional array by one or two columns

Posted 22 September 2008 - 08:49 PM

I don't know of any programming language that has an array with rows and columns since that is explicitly for a two-dimensional case. Whereas an array as a programming construct can be n-dimensional. If you need a good example just look at the 'volume' and 'edition' example listed in the php documentation itself. That example demonstrates how to sort the results of a database query.
Was This Post Helpful? 0
  • +
  • -

#14 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 05:26 AM

View Postgrimpirate, on 22 Sep, 2008 - 08:49 PM, said:

I don't know of any programming language that has an array with rows and columns since that is explicitly for a two-dimensional case.

Maybe not, but having looked a little more, it seems that there is no shortage of routines to accomplish the task. This fact alone should demonstrate to anyone developing a language that there is a strong need for it.

It's disappointing that it is easier and faster to let mysql do the sorting, since loading the data into RAM and then sorting should be faster.

Let this be a notice to others: If you need to sort data from a mysql server, do it in the query, ie:

$query = "SELECT * FROM sometable where somefield = $somevalue order by a_column DESC, another_column";
Was This Post Helpful? 0
  • +
  • -

#15 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 06:43 AM

View PostCTphpnwb, on 23 Sep, 2008 - 08:26 AM, said:

This fact alone should demonstrate to anyone developing a language that there is a strong need for it.


Not necessarily, it means many people don't understand their needs.

View PostCTphpnwb, on 23 Sep, 2008 - 08:26 AM, said:

It's disappointing that it is easier and faster to let mysql do the sorting, since loading the data into RAM and then sorting should be faster.


Wrong. A database should always be faster at sorting. If it's not, you're not asking the right question.

View PostCTphpnwb, on 23 Sep, 2008 - 08:26 AM, said:

Let this be a notice to others: If you need to sort data from a mysql server, do it in the query, ie:

$query = "SELECT * FROM sometable where somefield = $somevalue order by a_column DESC, another_column";


Quite so.

Also note that a database table can be seen to a OOP language as an array of objects. In this case, the object is sometable_row. Most languages have sorting options for arrays that will pass two array items to a comparison mechanism. This is how sorting an array of records is done. If the item is another array it's seen as "multidimentional". In general, a "multidimentional array" is a hack.
Was This Post Helpful? 0
  • +
  • -

#16 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 07:05 AM

View Postbaavgai, on 23 Sep, 2008 - 06:43 AM, said:

Not necessarily, it means many people don't understand their needs.

Or it means that language developers don't understand that the most common expectation is that if you can sort rows, you should be able to sort columns too. Honestly, you can call these multi dimensional arrays, but most people are going to want to treat them as two dimensional matrices at least some of the time.

View Postbaavgai, on 23 Sep, 2008 - 06:43 AM, said:

Wrong. A database should always be faster at sorting. If it's not, you're not asking the right question.

How do you come to that conclusion? The database is on a disc of some kind, which is hundreds, if not thousands of times slower than RAM. A database sort might have been faster in the days when 512 KB was a lot of memory, forcing frequent disk swaps, but not today.

Having run into this problem, I'm sure that the way sorting is implemented in php, doing this sort would be slower than through mysql, but I see that as a flaw in php, not an advantage of mysql.
Was This Post Helpful? 0
  • +
  • -

#17 Ridikule  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 68
  • Joined: 29-September 05

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 07:46 AM

Databases have been optimizing their sorting algorithms in their respective DB engines for years. It's common to let the database handle a data sort, and yes, it's much faster.

If you don't believe this, Make a script that prints time at the beginning of the script and at the end. Try pulling back 100,000 records and sorting them yourself. Then try doing it with the sql order by. The results will speak for themselves.
Was This Post Helpful? 0
  • +
  • -

#18 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 07:58 AM

View PostRidikule, on 23 Sep, 2008 - 07:46 AM, said:

Databases have been optimizing their sorting algorithms in their respective DB engines for years. It's common to let the database handle a data sort, and yes, it's much faster.

Yes, they have been optimized, while RAM based methods have been ignored. It makes sense then that the database is faster. That doesn't mean that it should be faster. It shouldn't, because if all things (software) were equal, RAM would have a huge advantage.
Was This Post Helpful? 0
  • +
  • -

#19 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,805
  • Joined: 16-October 07

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 08:00 AM

View PostCTphpnwb, on 23 Sep, 2008 - 10:05 AM, said:

View Postbaavgai, on 23 Sep, 2008 - 06:43 AM, said:

Wrong. A database should always be faster at sorting. If it's not, you're not asking the right question.

How do you come to that conclusion? The database is on a disc of some kind, which is hundreds, if not thousands of times slower than RAM. A database sort might have been faster in the days when 512 KB was a lot of memory, forcing frequent disk swaps, but not today.


You are retrieving that data from a database in the first place, right? Which means all the overhead you're describing is unavoidable regardless of where the sorting happens. When asked to to sort, the database simply passed back the data in a given order, rather the natural order. If you have indexes, the overhead of this is functionally negligible. A database will do all of the expensive stuff in RAM.

In the scenario of sorting data programmatically you've introduced a number of issues. If you just read from the database and pass the data out, storage in your program is item sized. If, on the other hand, you pull all that data into a structure for sorting, you've now created considerable storage overhead before you've even done anything.

There is simply no instance I can imagine where sending back an ordered result set will not be significantly faster than retrieving an entire unordered result set, allocating memory for it, and then applying a sort to that data. A database does one thing, stores and retrieves data. It is optimized for that. If a general purpose middle tier scripting language could beat it at that job, then something is simply broken.


View PostCTphpnwb, on 23 Sep, 2008 - 10:05 AM, said:

Or it means that language developers don't understand that the most common expectation is that if you can sort rows, you should be able to sort columns too. Honestly, you can call these multi dimensional arrays, but most people are going to want to treat them as two dimensional matrices at least some of the time.


You're not sorting "rows", you're sorting element in an array. Array sorting is common. Sizes are expected to be reasonable. Result sets from databases are cannot be expected to be reasonable. I can write a PHP program to pass ten million data points to the user upon request; I may not be able to store that amount in RAM.

The most common expectation of language developers is that they are writing for other developers who understand the issues involved. Writing a sort is trivial, any programmer should be able to write one for custom logic. The canned sorts exist more for expediency than thoroughness.
Was This Post Helpful? 0
  • +
  • -

#20 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3075
  • Posts: 10,783
  • Joined: 08-August 08

Re: Sort multidimensional array by one or two columns

Posted 23 September 2008 - 08:32 AM

View Postbaavgai, on 23 Sep, 2008 - 08:00 AM, said:

You are retrieving that data from a database in the first place, right? Which means all the overhead you're describing is unavoidable regardless of where the sorting happens. When asked to to sort, the database simply passed back the data in a given order, rather the natural order. If you have indexes, the overhead of this is functionally negligible. A database will do all of the expensive stuff in RAM.

Yes, there will always be overhead in transferring the data, but using the database to sort requires that the indexes be transferred and sorted, then the data be transferred, which is an extra step. Of course, if it's all loaded into RAM at once, then there shouldn't be any difference in doing it in php or mysql. Since there are bound to be times when you aren't using a database, it seems like a good idea to have that capability in php!

I understand what you're saying about arrays and rows, but that's because of the way they're defined in the language, and it's the definition that's the problem! From Googling it, I see that it is a very common issue, so it just can't be that the way array sorting has been defined is optimal. It isn't the best way just because programmers have gotten used to it.

It seems to me that there are many functions in php that exist to do things that could be done by writing your own function. Sorting a two dimensional matrix isn't one of them, but it should be.

This post has been edited by CTphpnwb: 23 September 2008 - 08:34 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2