Multisorting an array, excluding certain results

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
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Multisorting an array, excluding certain results

Post by VirtuosiMedia »

Is there a function or a way of using array_multisort() that would allow me to exclude results from a multi-dimensional array?

As an example, suppose I want to populate a page with widgets from the database. I also want to divide each page into sections: top, left, center, right, and bottom and I want the widgets to appear in the correct section and in the proper order. Each widget is classified by a page id, a section id, a widget id, and then a section rank.

What I would like to do is perform 1 JOIN query to get all of the widgets that belong on the page and then sort the result set for each section so that it displays only the widgets for that section in order, while excluding all the widgets for every other section. My preference would be to only perform 1 query rather than a separate one for each section. Is there a way to do that easily, but efficiently?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Multisorting an array, excluding certain results

Post by Benjamin »

You'll most likely just have to iterate through the array and use the section id to populate 4 other arrays, 1 for each section. I can't think of another way to do it off the top of my head.
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Multisorting an array, excluding certain results

Post by WebbieDave »

Could the ORDER BY clause handle much of the work?

Code: Select all

...
WHERE page_id = 1
ORDER BY section_id, section_rank
Then while through, moving row arrays to a different section array when the section_id changes. Similar to what astions suggested except ever so slightly more optimizable therefore efficient as a result of the presort through SQL.
Last edited by WebbieDave on Thu Jul 10, 2008 3:21 pm, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Multisorting an array, excluding certain results

Post by Benjamin »

Sure, but then the PHP code is highly dependent on the order of the results from the query. I think I would just divide them up using PHP.
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Multisorting an array, excluding certain results

Post by WebbieDave »

If they're already sorted by section and rank, and his page has been structured to output in said order, he could possibly even just next through them, forgoing the partitioning with php altogether.

I too would simply iterate and assign to the appropriate section array, but in his quest for efficiency, I wanted to give him another approach to mull over.
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: Multisorting an array, excluding certain results

Post by VirtuosiMedia »

WebbieDave wrote:If they're already sorted by section and rank, and his page has been structured to output in said order, he could possibly even just next through them, forgoing the partitioning with php altogether.
Thanks guys. If I did that, though, wouldn't I also have to keep track of how many widgets are per section per page somewhere?

The separate arrays was something that I was thinking, but I was worried a little bit about efficiency, especially as it scales. The example provided was a little simplistic, there could be up to 15 sections per page. Also, the widgets would actually be links, so there could potentially quite a few of them. Multiply that by a large number of pages on a high-traffic website and it could get expensive. I like the idea of pre-sorting with the query for efficiency. Caching would also probably help.

So for your while loop suggestion, are you suggesting something like:

Code: Select all

 
while ($row) {
     if ($row['section'] == 1) {
           //Add to the $section1 array
     }
     if ($row['section'] == 2) {
           //Add to the $section2 array
     }
     //And so on
}
 
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Multisorting an array, excluding certain results

Post by Benjamin »

Well if you're looping through all the records from the database anyway, that would be a good place to organize them.

Code: Select all

 
$results = array();
 
while ($data = $db->get_row())
{
    $results[$data['section_id']][] = $data;
}
 
$section_one_array = $results[1];
$section_two_array = $results[2];
//etc
 
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: Multisorting an array, excluding certain results

Post by VirtuosiMedia »

That looks much cleaner, thank you. Keeping in mind that I'm getting hyperlink information, would I still be able to access everything as an associative array then?

Code: Select all

 
while ($section_one_array){
     echo $section_one_array['title'];
     echo $section_one_array['url'];
     //etc.
}
 
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Multisorting an array, excluding certain results

Post by Benjamin »

Each array would contain all the records returned in that section, so yes all the data would still be there. Also, the $results would still contain an array of all records returned.

Your code would look like this.

Code: Select all

 
foreach ($section_one_array as $data)
{
    echo $data['title'];
    echo $data['url'];
}
 
User avatar
VirtuosiMedia
Forum Contributor
Posts: 133
Joined: Thu Jun 12, 2008 6:16 pm

Re: Multisorting an array, excluding certain results

Post by VirtuosiMedia »

Thank you both, that should do what I'm looking for.

As a follow up question, am I right in wanting to do it all in one query and sorting it with PHP versus multiple queries?
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Multisorting an array, excluding certain results

Post by WebbieDave »

Absolutely. The database connect and query would be the most expensive part of the page.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Multisorting an array, excluding certain results

Post by Benjamin »

Yes, pulling the results with a single query is much better, although I would say you're a bit overly concerned about the performance impact of sorting it with PHP.
Post Reply