php - 连接表的 foreach 循环

Question

我试图在循环中列出用户，但我想将 city_name 保留在循环之外。我需要另一个查询或 if 语句吗？

我真正想要的结果:

伦敦用户:詹姆斯，鲍勃

纽约用户:大卫

users table
====================
id  user_name   user_city_id  user_type
1   Helen       2             NA
2   James       1             PREMIUM
3   David       1             NORMAL
4   Bob         1             NORMAL

cities table
=============
city_id  city_name  city_status
1        London     1
2        New York   1
3        Paris      0

<?php 

    $getcities = $db->get_rows("
        SELECT * 
        FROM users
        JOIN cities 
        ON cities.city_id = users.user_city_id
        WHERE city_status = '1'
        AND user_type IN ('NORMAL','PREMIUM')
    ");

?>

<h1><?php echo $row->city_name ?> USERS</h1>

<?php
    foreach($getcities as $row) {
?>

<h2><?php echo $row->user_name ?></h2>

<?php
    }
?>

Answer 1

您可以使用 SQL 查询来完成。

    SELECT cities.city_name, GROUP_CONCAT(users.user_name) AS names
    FROM users INNER JOIN cities ON cities.city_id = users.user_city_id
    WHERE city_status = '1' AND user_type IN ('NORMAL','PREMIUM')
    GROUP BY cities.city_id

在你的 PHP 中

    foreach($getcities as $row) {
          echo $row->city_name . ": " . $row->names;
    }