Multisorting an array, excluding certain results
Moderator: General Moderators
- VirtuosiMedia
- Forum Contributor
- Posts: 133
- Joined: Thu Jun 12, 2008 6:16 pm
Multisorting an array, excluding certain results
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?
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
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
Could the ORDER BY clause handle much of the work?
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.
Code: Select all
...
WHERE page_id = 1
ORDER BY section_id, section_rank
Last edited by WebbieDave on Thu Jul 10, 2008 3:21 pm, edited 1 time in total.
Re: Multisorting an array, excluding certain results
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
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.
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.
- VirtuosiMedia
- Forum Contributor
- Posts: 133
- Joined: Thu Jun 12, 2008 6:16 pm
Re: Multisorting an array, excluding certain results
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?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.
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
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
- VirtuosiMedia
- Forum Contributor
- Posts: 133
- Joined: Thu Jun 12, 2008 6:16 pm
Re: Multisorting an array, excluding certain results
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
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.
Your code would look like this.
Code: Select all
foreach ($section_one_array as $data)
{
echo $data['title'];
echo $data['url'];
}
- VirtuosiMedia
- Forum Contributor
- Posts: 133
- Joined: Thu Jun 12, 2008 6:16 pm
Re: Multisorting an array, excluding certain results
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?
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
Absolutely. The database connect and query would be the most expensive part of the page.
Re: Multisorting an array, excluding certain results
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.