mySQL PHP查询将结果安排在组中

My problem is with a customer service database i am currently writing. I don't have great knowledge of sql queries and i'm really having a hard time getting the query right.

我的问题是我正在写的客户服务数据库。我对sql查询知之甚少,而且我真的很难获得正确的查询。

i have two tables:

我有两张桌子:

**Customers:**
idcustomer
company_name
tel_number
address
postcode

**customer_machines**
id_customer_machine
id_customer
date_last_service
date_service_due

I want to select results that show which machines are due for a service between a given set of dates (for example machines due in month 9).

我想选择一些结果,显示在给定的一组日期(例如第9个月到期的机器)之间的服务应该到期的机器。

SELECT customer_machines.* from customer_machines WHERE (customer_machines.date_next_service BETWEEN '2013-09-01' AND '2013-10-01') ORDER BY date_next_service ASC;

this is fine however some customer's have more than one machine e.g.

这很好,但有些客户有多台机器,例如

+---------------------+-------------+-------------------+-------------------+
| id_customer_machine | id_customer | date_last_service | date_next_service |
+---------------------+-------------+-------------------+-------------------+
|                   1 |           1 | 2012-09-02        | 2013-09-02        |
|                   2 |           2 | 2012-09-14        | 2013-09-14        |
|                   3 |           3 | 2012-09-30        | 2013-09-30        |
|                   5 |           3 | 2012-09-30        | 2013-09-30        |
+---------------------+-------------+-------------------+-------------------+

I need to group machines by which customer owns them, join the customer's details and output to a table in the browser something like:

我需要对客户拥有它们的机器进行分组,加入客户的详细信息并输出到浏览器中的表格,例如:

Customer 1     address of customer 1     tel of customer 1     postcode of customer 1
machine-id-1                                 date-last-service      date-next-service
------------------------------------------------------------------------------------
customer 2     address of customer 2     tel of customer 2     postcode of customer 2
machine-id-2                                 date-last-service     date-next-service
-------------------------------------------------------------------------------------
customer 3     address of customer 3     tel of customer 3     postcode of customer 3
machine-id-3                                date-last-service     date-next-service
machine-id-5                                date-last-service     date-next-service

Can i arrange the results together like this with a single query? so far i have only tried nesting queries in php with no success.

我可以使用单个查询将结果排列在一起吗?到目前为止,我只在php中尝试嵌套查询,但没有成功。

If you could point me in the right direction that would be great

如果你能指出我正确的方向,这将是伟大的

thanks

3 个解决方案

#1

SQL does not have a feature to build nested results, only plain tables. In some simple cases, you can use GROUP_CONCAT to concatenate values. For example:

SQL没有构建嵌套结果的功能,只有普通表。在一些简单的情况下,您可以使用GROUP_CONCAT来连接值。例如:

SELECT 
  id_customer, 
  GROUP_CONCAT(id_customer_machine SEPARATOR ', ') as id_customer_machines
FROM customer_machines;

would output you:

输出你:

+-------------+----------------------+
| id_customer | id_customer_machines |
+------------------------------------+
|           1 |                    1 |
|           2 |                    2 |
|           3 |                 3, 5 | 
+-------------+----------------------+

But since you need to group more than one column, this would not help you. In your case you have two options:

但是,由于您需要对多个列进行分组,这对您没有帮助。在您的情况下,您有两个选择:

1. Fetch all customer ids, then iterate over them, fetching customer machines for every id_customer. Simpler, but not efficient (numCustomers + 1 queries).

获取所有客户ID,然后迭代它们,为每个id_customer获取客户机器。更简单,但效率不高(numCustomers + 1查询)。

2. Fetch plain data from database and make structured array from it by means of PHP. Efficient, but more coding.

从数据库中获取纯数据,并通过PHP从中生成结构化数组。高效,但更多编码。

#2

One option would be to select relevant data from both tables e.g :

一种选择是从两个表中选择相关数据,例如:

SELECT c.*, cm.id_customer_machine, cm.date_last_service, cm.date_service_due from Customers c, customer_machines cm WHERE c.idcustomer = cm.customer_id AND (customer_machines.date_next_service BETWEEN '2013-09-01' AND '2013-10-01') ORDER BY date_next_service ASC;

来自客户c的SELECT c。*,cm.id_customer_machine,cm.date_last_service,cm.date_service_due,customer_machines cm WHERE c.idcustomer = cm.customer_id AND(customer_machines.date_next_service BETWEEN'2013-09-01'AND'2013-10-01 ')ORDER BY date_next_service ASC;

However, that may not be ideal because you will end up with duplicated customer data too for each machine found.

但是,这可能并不理想,因为对于找到的每台机器,您最终也会得到重复的客户数据。

As an alternative you could loop through each customer and select all the results from the second table based on the users id and build it into an array or object e.g :

作为替代方案,您可以遍历每个客户并根据用户ID从第二个表中选择所有结果,并将其构建到数组或对象中,例如:

$customerArr = array();

$ customerArr = array();

$con=mysqli_connect("localhost","user","pass","schema");

$customerResult = mysqli_query($con,"SELECT * FROM Customers");

$ customerResult = mysqli_query($ con,“SELECT * FROM Customers”);

while($customerRow = mysqli_fetch_array($customerResult)){

while($ customerRow = mysqli_fetch_array($ customerResult)){

$customerArr[$customerRow['idcustomer']];

$macineResult = mysqli_query($con,"SELECT * FROM customer_machines WHERE customer_id = 'customerRow['idcustomer']'");

$ macineResult = mysqli_query($ con,“SELECT * FROM customer_machines WHERE customer_id ='customerRow ['idcustomer']'”);

while($machineRow = mysqli_fetch_array($machineResult)){

while($ machineRow = mysqli_fetch_array($ machineResult)){

$customerArr[$customerRow['idcustomer']][$machineRow['id_customer_machine']]['date_last_service'] = $machineRow['date_last_service'];

$ customerArr [$ customerRow ['idcustomer']] [$ machineRow ['id_customer_machine']] ['date_last_service'] = $ machineRow ['date_last_service'];

$customerArr[$customerRow['idcustomer']][$machineRow['id_customer_machine']]['date_next_service'] = $machineRow['date_next_service'];

$ customerArr [$ customerRow ['idcustomer']] [$ machineRow ['id_customer_machine']] ['date_next_service'] = $ machineRow ['date_next_service'];

}

}

Hope this helps.

希望这可以帮助。

#3

You can do this with a single query, but the loop in your PHP code will get a bit more complicated than using separate queries

您可以使用单个查询执行此操作,但PHP代码中的循环将比使用单独的查询更复杂

SELECT * from customer_machines
INNER JOIN customers USING(customer_id)
WHERE (customer_machines.date_next_service BETWEEN '2013-09-01' AND '2013-10-01')
ORDER BY date_next_service ASC;

This should give you something like:

这应该给你这样的东西:

customer_id | machine_id | company_name | etc | other column | more data | ...
    1       |     1      | xxxx
    2       |     2      | xxxx
    3       |     3      | xxxx
    3       |     5      | xxxx

your loop:

while($row = mysql_fetch_array($query)) {
    $data[ $row[customer_id][] = $row;
}

this will give you an array similar to:

这会给你一个类似于的数组:

array(
    [1] => array(
         [0] => array(
              [company_name] => name,
              the remaining customer and machine data
         )
    ),
    [2] => array(
         [0] => array(
              [company_name] => name,
              the remaining customer and machine data
         )
    ),
    [3] => array(
         [0] => array(
              [company_name] => name,
              the remaining customer and machine data
         ),
         [1] => array(
              [company_name] => name,
              the remaining customer and machine data
         )
    )

Just iterate over that array to print the data; You will need 2 loops to do this.

只需迭代该数组即可打印数据;你需要2个循环才能做到这一点。

An other solution is to first select all customers, loop over it and foreach customer select the machine info you want to display. You will get a lot of queries by doing this.

另一种解决方案是首先选择所有客户,循环并预先取出客户选择要显示的机器信息。这样做会得到很多疑问。