php - MySQL结合了几个select语句

Question

想知道如何/是否可以组合这两个 select 语句。

        $searchCat = $mysqli->query("SELECT category_id, category.name FROM category WHERE category.name = '$searchTerm'");

    while ($r=$searchCat->fetch_assoc()) {
        $category_id=$r["category_id"];

        $searchBus = $mysqli->query("SELECT business.name, business.street FROM business WHERE business.category_id = '$category_id'");             

        while ($r=$searchBus->fetch_assoc()) {
            $name=$r["name"];
            $street=$r["street"];

            echo "$name, $street<br>";
        }
    } // end of while

对于输出，我只需要 Business 表中的数据，第一个 select 找到搜索词的相关 ID，该 Id 也包含在 Business 表中。

那么我可以在第一个选择获取 id 然后将其传递给第二个选择的情况下执行连接或合并，从而允许我跳过第一个 While 吗？

Answer 1

你也可以把它做成单个查询。

$query = "SELECT `business`.`name`, `business`.`street` 
          FROM `business`, `category` 
          WHERE 
             `business`.`category_id` = `category`.`category_id` 
              AND 
             `category`.`name` = '$searchTerm'";

$searchBus = $mysqli->query($query);