CODEWITHSUNDEEP
mysql
Rob Alston
Rob Alston

Reputation: 39

need help restructuring a query

I have a query working fine for a twitter typeahead search. It concantinates several fields so that the search string contains customer_name, address, up to 4 phone numbers to search by. I currently have the address portion of the customer table broken into [address] for the house number and [street] for the street name. However, I want to implement the use of a streets table and replace the streets column with [steeet_id]. I am at a complete loss to restructure the query to use the [street_id] and still display the name of the street. Below is the page that runs the query and returns the json array. Any help will be greatly appreciated.

<?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);
}
?>

Upvotes: 0

Views: 108

Answers (1)

trincot
trincot

Reputation: 351369

Just join that streets table into your inner query ( I left out the outer part, as it does not change):

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

So the part INNER JOIN ... is what you need to add.

I have assumed the streets table has a street_id field. Use the name it actually has (might be just id).

Upvotes: 1

Related Questions