php - 如何创建返回结果集的数组并计算 PHP 中数据库中每个组的不同类型值

Question

我正在创建一个仪表板，其中将按公司显示产品详细信息。我不想在查询中使用分组依据，因为它在状态“y”的记录(公司)之间使用，应计算状态“y”的所有批准值(NO)。它可以是公司的单个、多个记录(产品)。

我尝试创建一个数组，但由于公司状态批准的一对多关系而失败。我想循环每个公司的值，因此它应该显示特定公司在日期期间的所有详细信息。

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dashboard";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT p.id, p.company, p.status
FROM product p 
where p.startDate between '2019-02-01' and '2019-02-08'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row
echo "<table border='1'>
<tr>
<th>company</th>
<th>total product count company wise</th>
<th>total count with y status</th>
<th>total count with approved status - NO</th>
</tr>";

$totalProductCountCompanyWise = array();
$countAllY = 0;

while($row = mysqli_fetch_array($result)) {
$key = $row['company'];
if(!array_key_exists($key,$totalProductCountCompanyWise)){
$totalProductCountCompanyWise[$key] = 1;
} else{
$totalProductCountCompanyWise[$key] += 1;
}

if($row['status'] == "y"){
$countAllY++;
}

$sql2 = "SELECT p.id, p.company, p.status , ps.approved
FROM product p 
join productstatus ps on p.id = ps.id 
where p.company = '".$key."' and ps.id = '".$row['id']."' ";
$result2 = mysqli_query($conn, $sql2);
$countNO = 0;
while($row2 = mysqli_fetch_array($result2)) {
if($row2['approved'] == "no"){
$countNO++;
}
}

$companyData = array(
array("company" => $row['company'],
"totalProductCountCompanyWise" => $totalProductCountCompanyWise[$key],
"totalCountWithYStatus" => $row['company'],
"totalCountWithApprovedStatusNO" => $row['company']
)
);


echo "<tr>";
echo "<td>" . $row['company'] . "</td>";
echo "<td>" . $totalProductCountCompanyWise[$key] . "</td>";
echo "<td>" . $countAllY . "</td>";
echo "<td>" . $countNO . "</td>";
echo "</tr>";
}
echo "</table>";
}
else {
echo "0 results";
}
mysqli_close($conn);

表引用

/*
CREATE TABLE `product` (
`id` int(11) NOT NULL,
`company` varchar(255) NOT NULL,
`productID` int(11) NOT NULL,
`status` varchar(255) NOT NULL,
`startDate` datetime NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `product` (`id`, `company`, `productID`, `status`, `startDate`) 
VALUES
(1, 'ABC', 13245, 'y', '2019-02-01 00:00:00'),
(2, 'amazon', 13215, 'n', '2019-02-02 00:00:00'),
(3, 'google', 13345, 'y', '2019-02-03 00:00:00'),
(4, 'amazon', 13145, 'y', '2019-02-04 00:00:00'),
(5, 'amazon', 13145, 'y', '2019-02-04 00:00:00'),
(6, 'google', 13188, 'n', '2019-02-07 00:00:00'),
(7, 'IBM', 13177, 'n', '2019-02-08 00:00:00');

ALTER TABLE `product`
ADD PRIMARY KEY (`id`);
ALTER TABLE `product`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

CREATE TABLE `productstatus` (
`id` int(11) NOT NULL,
`approved` varchar(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `productstatus` (`id`, `approved`) 

VALUES(1, 'yes'),(2, 'yes'),(3, 'no'),
(4, 'yes'),(5, 'no'),(6, 'yes'),(7, 'yes');

ALTER TABLE `productstatus`
ADD PRIMARY KEY (`id`);

ALTER TABLE `productstatus`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
*/

期望的输出

<table border='1'>
    <tr>
    <th>company</th>
    <th>total product count company wise</th>
    <th>total count with y status</th>
    <th>total count with approved status - NO</th>
    </tr>
    <tr>
    <th>ABC</th>
    <th>1</th>
    <th>1</th>
    <th>0</th>
    </tr>
    <tr>
    <th>amazon</th>
    <th>3</th>
    <th>2</th>
    <th>1</th>
    </tr>
    <tr>
    <th>Google</th>
    <th>2</th>
    <th>1</th>
    <th>1</th>
    </tr>
    <tr>
    <th>IBM</th>
    <th>1</th>
    <th>0</th>
    <th>0</th>
    </tr>
</table>

Answer 1

您可能希望对productstatus 表使用COUNT 和LEFT JOIN 进行聚合查询，并按product.company 进行分组。

示例:http://www.sqlfiddle.com/#!9/778178/5

SELECT 
    p.company, 
    COUNT(p.id) AS totalProductCountCompanyWise,
    SUM(CASE p.status WHEN 'y' THEN 1 ELSE 0 END) AS totalCountWithYStatus,
    SUM(CASE ps.approved WHEN 'no' THEN 1 ELSE 0 END) AS totalCountWithApprovedStatusNO
FROM product AS p 
LEFT JOIN productstatus AS ps 
ON p.id = ps.id 
WHERE p.startDate BETWEEN '2019-02-01' and '2019-02-08'
GROUP BY p.company

结果:

| company | totalProductCountCompanyWise | totalCountWithYStatus | totalCountWithApprovedStatusNO |
|---------|------------------------------|-----------------------|--------------------------------|
|     ABC |                            1 |                     1 |                              0 |
|  amazon |                            3 |                     2 |                              1 |
|  google |                            2 |                     1 |                              1 |
|     IBM |                            1 |                     0 |                              0 |