如何在php中统计来自同一城市的用户

Question

我需要统计来自同一城市的用户。记录在单个表中。以下是我到目前为止所做的。

<?php
$SelectCities = mysqli_query($con, "SELECT * FROM users ");
while($row=mysqli_fetch_assoc($SelectCities)){

$total_cities       =   mysqli_query($con,"SELECT * FROM users WHERE user_id='$user_id' AND user_type='seller' AND user_status = 'Active'");
$total_cities_count =   mysqli_num_rows($total_cities);
?>
<div class="col-md-4 col-sm-6 col-lg-4">
<ul class="locat-list">
<li><?php echo $row['city'];?> <span>( <?php echo $total_cities_count; ?> )</span></li>
</ul>
</div>
<?php } ?>

这是我的数据库

我的数据库结构

Answer 1

以下是在SQL中执行此操作的最简单方法。希望能有所帮助：

SELECT city, COUNT(userid) 
FROM users 
GROUP BY city 
ORDER BY city ASC

Answer 2

在这里，我在我的员工表中计算城市用户数。使用CCD_ 1。

<?php
// This is a my database connection
$host_name = 'localhost';
$db_name = 'stackoverflow';
$user_name = 'root';
$password = '';
$conn = new PDO("mysql:host=$host_name; dbname=$db_name;", $user_name, $password);
$sql = "SELECT city, COUNT(*) as employee_number FROM employee GROUP BY city"; //This is a query count number of employee city wise
$count_user_cityWise = $conn->prepare($sql);
$count_user_cityWise->execute();
?>
<table>
<thead>
<tr>
<th>City</th>
<th>Number of employee</th>
</tr>
</thead>
<tbody>
<?php
while ($data = $count_user_cityWise->fetch()) {
echo "<tr>";
echo "<td>";
echo $data['city'];
echo "<td>";
echo "<td>";
echo $data['employee_number'];
echo "<td>";
echo "</tr>";
}
?>
</tbody>
</table
?>