Multiple concurrent Mysql queries

[cjohnsonuk]

I need to display a page of helpdesk calls as tables.

The calls need to be grouped by room.

The rooms need to be grouped by location.

 

I have a table called locations with a locationID and locationName

I have a table called rooms where each row has a locationID, roomID and roomName

I have a table called calls where each call has, amongst other things a roomID, locationID, callID etc

 

I can get each queries for table but I'm stuck on how to write the code.  The logic I'm trying to use is:

 

For each location in location

  Start a location section

    For each room where locationID = location

      Start a room table

        For each call where roomID = room

            print row of call details

 

 

but this means that I'm still pulling locations from the locations table whilst pulling rooms from the room table whilst pulling calls from the call table.

 

My next thought was to pull each query into an array then step through the arrays

 

...and that's when I realised just how rusty my php is.

 

Does anyone have an elegant solution to this...

[Mchl]

Read about SQL joins. Instead of looping through results in PHP, let the MySQL do the job.

[cjohnsonuk]

I found the tutorial on the joins. I can read my location, room and help desk call information into one array but is there an elegant way to display  the resulting parsed array nicely into 1 div per location and 1 table per room with each call going in on its own table row?

[ialsoagree]

On MySQL:

 

<?php
$sql = 'SELECT location.locationName, room.roomName, call.* FROM location, room, call WHERE call.roomID = room.roomID AND room.locationID = location.locationID ORDER BY locationID ASC, roomID ASC, callID ASC';
?>

 

If I screwed up anything, it may be that the order by columns need a table prefix, in which case you'll probably want to use room.locationID, call.roomID, and call.callID but I don't think you should need the prefixes.

 

If my logic is right, you should wind up getting arrays with all the calls for a particular room in a particular location, followed by all the call's for the next room in the same location etc., followed by all the calls for the first room in the next location, followed by all the calls for the second room in the second location etc.

 

Record the location and room for the first query (building divs and tables as necessary) and insert calls as long as the room doesn't change. If the room changes, make sure the location is the same and if it is, build a new table, otherwise, build a new div.

 

If you don't want the highest ID's last, then change every occurance of ASC to DESC (ascending, small to large, vs. DESC, large to small).

[cjohnsonuk]

Thanks for this.  As I said my php is a little rusty and I was just wanting to see if I could find a elegant solution.  If I want to list all locations and all rooms, even those without calls so as to show that they have 0 outstanding calls is that possible from the query.  My thoughts would be to read all the locations into one array, all the rooms into another and all the calls into a 3rd, then parse through each location, each room and each call..