Page 1 of 1

Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 3:07 pm
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?

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 3:12 pm
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.

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 3:19 pm
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.

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 3:21 pm
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.

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 3:28 pm
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.

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 3:49 pm
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
}
 

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 4:02 pm
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
 

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 4:17 pm
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.
}
 

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 4:21 pm
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'];
}
 

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 4:37 pm
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?

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 4:40 pm
by WebbieDave
Absolutely. The database connect and query would be the most expensive part of the page.

Re: Multisorting an array, excluding certain results

Posted: Thu Jul 10, 2008 4:40 pm
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.