MySQL的查询，以多维的PHP阵列 [英] MySQL queries to multi-dimensional php array
问题描述
我建立中，我有我需要创建从多维PHP数组多个MySQL表的应用程序，但我有点困惑如何查询。我已经搜查各地相当多的解决方案，但其中许多人来自同一个数据库表。
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:
有将总是只有一个时间（一个以上的每一天，但仅一次一个将在循环中列出）的客户名称。不同量的卡车可能是对每个作业携带不同类型和材料的量。一些规模较小的作业将只有一个卡车和一种材料，但较大的作业可能有一个以上的卡车载着一个以上的材料类型。上面的图片将被视为携带多种材料多辆卡车较大的工作。一个较小的工作看起来一样，但只会有客户名称，并显示TRUCK 8：和材料1（或任何卡车计划在那一天，它的材料）
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).
这些表是我使用的：
- job_truck
- job_truck_material
- 材料
上面会输出：
日期：2015年2月27日
the above would output: Date: 2/27/2015
- 伯：1619（DOE）
- 卡车7：材料1
- 卡车60：材料1
-
TRUCK 8：（空）
- JOB: 1619 (Doe)
- TRUCK 7: MATERIAL 1
- TRUCK 60: MATERIAL 1
TRUCK 8: (empty)
伯：439（琼斯）
我第一次查询工作表（未显示），并返回 jobs.id ， jobs.name 和 jobs.date 。最终， jobs.date 将查询的WHERE子句中，当我需要访问基于日期（显然）的信息。下一步，我认为，将是查询 job_truck 表...两个表之间存在的联系 job_truck.id 和 materials.job_truck 。下一步将是通过查询 materials.material 并链接到 job_truck_material.material 来找出每个卡车材料 materials.id （ job_truck_material.material 作为材料表的ID）。
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).
我试过加入表，但这样做，我只回像每个项目之一：1岗位，1卡车，1物质。我敢肯定，连接表可能是要走的路，但是这就是为什么我在这里，是因为我不知道正确的结构。我也试着嵌套循环，但没有制定出太好。
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.
我真的希望我解释说，正确...任何帮助是极大的AP preciated。在此先感谢！
I really hope that I explained that correctly... Any help is greatly appreciated. Thanks in advance!
推荐答案
尝试查询在加入，然后迭代渲染到多维的所有行：
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'];
}
这篇关于MySQL的查询，以多维的PHP阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
