mysql - 需要帮助重构查询

Question

我有一个对于 twitter 预输入搜索运行良好的查询。它连接了多个字段，以便搜索字符串包含 customer_name、地址、最多 4 个用于搜索的电话号码。目前，我将客户表的地址部分分为门牌号的 [地址] 和街道名称的 [街道]。但是，我想实现街道表的使用，并将街道列替换为 [steeet_id]。我完全无法重组查询以使用 [street_id] 并仍然显示街道名称。下面是运行查询并返回 json 数组的页面。任何帮助将不胜感激。

<?php
if(isset($_POST['query'])) {
    //connect to database
    include 'connect.php';
    //retrieve the query
    $query = $_POST['query'];
    //search database for all similar items
    $sql = mysql_query("SELECT * FROM (
    SELECT customer_id, customer_name, address, street, phone_1, phone_2, phone_3, phone_4, CONCAT(customer_name, ' ', address, ' ', street, ' ', phone_1, ' ', phone_2, ' ', phone_3, ' ', phone_4) as `mysearch`
    FROM customers) base
    WHERE `mysearch` LIKE '%{$query}%'
    ORDER BY customer_name ASC, street ASC, address ASC
    ");
    $array = array();
    while ($row = mysql_fetch_assoc($sql)) {
        $array[] = $row['customer_name'] . " " . $row['address'] . " " . $row['street'] . " " . $row['phone_1'].  " " . $row['phone_2']. " " .  $row['phone_4']. " " . $row['phone_3']." id#" . $row['customer_id'];
    }
    //return JSON array
    echo json_encode($array);
}
?>

Answer 1

只需将 streets 表加入到您的内部查询中(我省略了外部部分，因为它不会改变):

SELECT customer_id, customer_name, address, street, phone_1, phone_2, phone_3, 
       phone_4, CONCAT(customer_name, ' ', address, ' ', street, ' ', phone_1, 
                      ' ', phone_2, ' ', phone_3, ' ', phone_4) as `mysearch`
    FROM customers
    INNER JOIN streets ON streets.street_id = customers.street_id

因此，您需要添加 INNER JOIN ... 部分。

我假设streets 表有一个street_id 字段。使用它实际拥有的名称(可能只是 id)。