PHP 搜索结果串联

Question

我试图在 php 中找到一种方法，将所有相关的搜索返回结果从 mysql 数据库连接到一个由逗号分隔的字符串中。请参阅下面的 DB 表 php 输出格式和 php 代码。

沙滩 table :

bname    length  city_name
Beach A  4       City Z
Beach B  3       City X
Beach C  7       City E

Beach_Act_Type 表:

bname        act_type
Beach A      Swimming
Beach A      Surfing
Beach B      Surfing
Beach C      Swimming
Beach C      Fishing
Beach C      Surfing

我正在寻找的输出:

`beach`.`bname`    `beach_act_type`.`act_type`  
 Beach C           Swimming, Fishing, Surfing   

而不是这种输出格式:

`beach`.`bname`    `beach_act_type`.`act_type`  
 Beach C           Swimming 
 Beach C           Fishing  
 Beach C           Surfing  

我的 php 代码:

$conn = new mysqli('localhost','test','','beachproject');

if (!$conn) {
    die('DB Connection Error');
}

$search = $_POST['search'];
$search_sql="select `beach`.`bname`, `beach_act_type`.`act_type` 
from `beach` inner join `beach_act_types` 
on `beach`.`bname`=`beach_act_type`.`bname` 
where `beach`.`bname`='$search'";
$search_query=$conn->query($search_sql);

if ($search_query->num_rows > 0) {
    while($row = $search_query->fetch_assoc()) {
        echo "<table border='1' cellpadding='4'>
        <tr>
        <th>Beach Name</th>
        <th>Beach Activity Allowed</th>
        </tr>";

        echo "<tr>";
        echo "<td>" . $row['bname'] . "</td>";
        echo "<td>" . $row['act_type'] . "</td>";
        echo "</tr>";

        echo "</table>";
    }
} else {
    echo "No Search Results";
}

Answer 1

您要查找的函数是 GROUP_CONCAT。

SELECT 
    `beach`.`bname`, 
    GROUP_CONCAT(`beach_act_types`.`act_type`) types
FROM
    `beach`
INNER JOIN
    `beach_act_types` ON
        `beach`.`bname` = `beach_act_types`.`bname`
WHERE
    `beach`.`bname` = '$search'

PS. You should be warned that you have SQL injection present, consider switching to prepared statements instead.