>我目前正在我的网站上根据存储在 mysql 数据库中的信息填充数据表
。该表的格式为:
Table 1
ID | DataA | Other Info | Count Data
======================================
1 | A | Other Info | 2
2 | B | Other Info | 3
Table 2
ID | Input
==========
1 | A
2 | A
3 | B
4 | B
5 | B
Count data in table 1
旨在显示entry form the DataA
列在input column in Table2
中出现的频率
更新。。。。
CREATE TABLE driver (
`ID` INTEGER,
`DataA` VARCHAR(1),
`Other Info` VARCHAR(10),
primary key (ID)
);
$sql = "SELECT * FROM `driver` ORDER BY drivercompany";
$query = mysqli_query($conn, $sql);
if (mysqli_num_rows($query) > 0) {
// output data of each row
while ($result = mysqli_fetch_assoc($query)) {
echo "<tr>
<td class='class'>" . $result['id'] . "</td>
<td>" . $result['DataA'] . "</td>
<td>" . $result['otherinfo'] . "</td>
<td></td> /**count goes here**/
</tr>";
}
}
?>
使用 db fiddle,我需要获取计数的查询是:
SELECT driver.*, COUNT(add_job.Input) AS counter
FROM driver
LEFT JOIN add_job ON add_job.Input = driver.DataA
GROUP BY driver.ID
我尝试将其添加到我的代码中,如下所示:
$sql = "SELECT * FROM `driver` ORDER BY drivercompany";
$query = mysqli_query($conn, $sql);
$count = "SELECT `driver`.*, COUNT(`add_job`.`adddriver`) AS counter
FROM `driver`
LEFT JOIN `add_job` ON `add_job`.`adddriver` = `driver`.`drivercompany`
GROUP BY `driver`.`id`";
$counter = mysqli_query($conn, $count);
while($result = mysqli_fetch_assoc($query,$counter)) { //and other variations
我无法确定如何让$count和$query一起工作
您可以联接两个表,按主键table1
分组,并使用单个查询计算table2
中的出现次数。
假设ID
是table1
中的主键,请将查询更改为:
$sql = "
SELECT t1.*, COUNT(t2.Input) AS counter
FROM table1 t1
LEFT JOIN table2 t2 ON t2.Input = t1.DataA
GROUP BY t1.ID
";
结果将是:
| ID | DataA | Other Info | Count Data | counter |
| --- | ----- | ---------- | ---------- | ------- |
| 1 | A | Other Info | 2 | 2 |
| 2 | B | Other Info | 3 | 3 |
在 db-fiddle 中查看结果
然后,您可以在输出中使用$result['counter']
。例如:
echo "
<tr>
<td class='class'>{$result['id']}</td>
<td>{$result['DataA']}</td>
<td>{$result['otherinfo']}</td>
<td>{$result['counter']}</td>
</tr>
";
注意:如果您在MySQL <5.7或任何版本的MariaDB中启用了ONLY_FULL_GROUP_BY
模式,则查询将引发错误。在这种情况下,您应该将其更改为
SELECT t1.id, t1.DataA, t1.otherinfo, COUNT(t2.Input) AS counter
FROM table1 t1
LEFT JOIN table2 t2 ON t2.Input = t1.DataA
GROUP BY t1.id, t1.DataA, t1.otherinfo
在 GROUP BY 子句中包括所有选定的非聚合列。