Working with arrays

semtex

I am using a mysql join query to fetch data out of the database into an array. Here's a shortened but representative version of what mysql returns:

Array
(
[0] => Array
(
[speelpleinid] => 1
[speelpleinnaam] => karrestraat
[toestelid] => 11
[toesteltype] => 1
[toestelnaam] => veertje hond
[meldingid] => 1
)

[1] => Array
    (
        [speelpleinid] => 1
        [speelpleinnaam] => karrestraat
        [toestelid] => 12
        [toesteltype] => 1
        [toestelnaam] => glijbaan groen
        [meldingid] => 2
    )

[2] => Array
    (
        [speelpleinid] => 2
        [speelpleinnaam] => sportpark
        [toestelid] => 4
        [toesteltype] => 1
        [toestelnaam] => schommel
        [meldingid] => 5
    )

[3] => Array
    (
        [speelpleinid] => 2
        [speelpleinnaam] => sportpark
        [toestelid] => 5
        [toesteltype] => 1
        [toestelnaam] => zandbak
        [meldingid] => 6
    )

)

Now that I have all the data in the array, I want to run some code on it. To begin with I need to return all 'speelpleinnaam' and 'speelpleinid' fields, sorted by 'speelpleinnaam'. Like this:
1 – karrestraat
2 – sportpark

Is this easy to do?

laserlight

Why not get the SQL statement to do that for you as well?

semtex

Thanks for the reply. My current code has the mysql statement do that. But the page I'm working on has a few foreach() loops and the bigger the database becomes the more loops there are and in the end I'm ending up with a page that performs more than a 1000 database queries.

That's why I thought to do just one query (with join) and then try to sort/manipulate the results based on the mutidimensional array that got returned.

All the needed data is now in the array so instead of doing over a 1000 db queries there's only one or two left which greatly improves performance.

It's just that I have not that much experience with grouping and sorting multidimensional arrays. I've checked php's array functions but couldn't find anything that does a simple thing such as mysql's GROUP BY. Or did I overlook it?

laserlight

semtex wrote:
But the page I'm working on has a few foreach() loops and the bigger the database becomes the more loops there are and in the end I'm ending up with a page that performs more than a 1000 database queries.

That is weird. Why would the number of foreach loops have to vary with the number of rows in the database tables? Likewise, why would you need more queries?

semtex wrote:
All the needed data is now in the array so instead of doing over a 1000 db queries there's only one or two left which greatly improves performance.

If you really need to do all that work, then whether you place the burden on the database or your script, the work still has to be done. Chances are, with appropriate indices the database will do the work better than you can, so in fact you may decrease performance.

semtex

Alright, let me sketch what I'm trying to accomplish.

I have a form class that creates form elements based on database information. The problem is that the form takes more than 20 seconds to load on my localhost. On the web server it's even worse: the form doesn't load at all because of limited memory resources.

I was hoping you could give me some tips on optimizing my form setup. The web application I'm working on is an online park administration tool for a company that repairs playground equipment for its clients. Every few months the company needs to check the status of the playground equipment. They can do that by filling in the form that loads form fields based on a few predefined checkpoints.

Eg. "Is there still enough sand in the area around the playground device?" etc.

I have a database with a clients table, a playgrounds table, an equipment table and a checkpoints table. Every client owns a few playgrounds. Every playground consists of some devices (equipment). And every device has a few checkpoints that need to be checked. Basically the form displays input fields for every checkpoint for every device for every playground of that particular client.

Currently I query the playgrounds table to find all playgrounds the logged in client owns.
Then for every playground I display a subform.
In the subform I have another subform for every playground device.
Then I query the database to find the checkpoints for every device.
Based on those checkpoints the form displays some form fields in the devices subform.

Of course I use a lot of foreach() loops to be able to display all necessary form fields.

foreach($playgrounds as $playground) {
$devices = $playgrounds->fetchAll($playgrounds->select()->where('playground_id = ?',$playground->id);
foreach($devices as $device) {
$checkpoints = $checkpoints->fetchAll($checkpoints->select()->where('device_id = ?',$device->id);
foreach($checkpoints as $checkpoint) {
//create form fields
}
}
}

So as you can see there's LOTS of queries being performed and the more playgrounds and devices and checkpoints the more queries get performed. It just crashes the database server. I was hoping that doing just one query and then looping through the array would mean a major performance boost.

laserlight

You can accomplish this with just one query. Select the playground, device and checkpoint information for the client, sorted according to playground, device and checkpoint.

Now, you have all the data you want in sorted order. So, you iterate over the rows of the result set. You get first row's playground, device and checkpoint data and print them. Then you check the next row: if the playground data is different, you know you need to create a new subform at the level of playgrounds. If not, check the device data: if it is different, again you need to create a new subform at the level of devices. If not, just print the checkpoint data in the current subform.

semtex

Very insightful. Thanks a lot. I'll dig into this.