MySQL queries to multi-dimensional php array

Question

I'm building an application in which I have multiple MySQL tables that I need to create a multi-dimensional php array from but I'm a bit confused about how to query it. I've searched around quite a bit for a solution but many of them come from a single database table.

The application is a scheduling software for a company, and this particular part is so they can display the customers name and the trucks that'll be at the customers house, as well as the materials that will be on each truck.

My goal is it to look similar to this:

There will always be only one customer name at a time (more then one per day but only one at a time will be listed in the loop). Different amounts of trucks could be on each job carrying different types and amounts of materials. Some smaller jobs will only have one truck and one material, but larger jobs may have more than one truck carrying more than one material type. The image above would be considered a larger job with multiple trucks carrying multiple materials. A smaller job would look the same but would only have the customers name, and show "TRUCK 8:" and "MATERIAL 1" (or whatever truck is scheduled for that day and the material on it).

These are the tables are I'm using:

• job_truck

• job_truck_material

• materials

the above would output: Date: 2/27/2015

• JOB: 1619 (Doe)
• TRUCK 7: MATERIAL 1
• TRUCK 60: MATERIAL 1

• TRUCK 8: (empty)

  ---

• JOB: 439 (Jones)

• TRUCK 35: (empty)

I first query the jobs table (not shown) and return jobs.id, jobs.name, and jobs.date. Eventually the jobs.date would be in the WHERE clause of the query when I need to access the information based on the date (obviously). The next step, I assume, would be to query the job_truck table... The link between the two tables being job_truck.id and materials.job_truck. Next would be to find out the materials for each truck by querying for materials.material and linking to job_truck_material.material to materials.id (job_truck_material.material being the id of the materials table).

I've tried joining the tables but doing that I'm only returning one of each item like: 1-job, 1-truck, 1-material. I'm sure that joining the tables is probably the way to go but thats why I'm here, because I'm not sure of the correct structure. I've also tried nesting the loops but that didn't work out well either.

I really hope that I explained that correctly... Any help is greatly appreciated. Thanks in advance!

Answer 1

Try querying all rows on a JOIN and then iterating to render to multi-dimensional:

$query = "SELECT *, materials.id AS material_id FROM job_truck JOIN job_truck_material ON job_truck_material.job_truck = job_truck.id JOIN materials ON job_truck_material.material = job_truck_material.id";
$jobs = array();

foreach ($conn->query($query ) as $row) {
    $job_id = $row['jobid'];
    $truck_id = $row['truck_id'];
    if(empty($jobs[$job_id])) {
        $jobs[$job_id] = array();
    }
    if(empty($jobs[$job_id][$truck_id])) {
        $jobs[$job_id][$truck_id] = array();
    }
    $jobs[$job_id][$truck_id][$row['material_id']] = $row['material'];
}