php - MySQL Join and Distinct


I have two tables one called area and one called area covered. Table area contains the fields postcode and region.


area table contains postcode - AB10 region - Aberdeen

area covered table contains id - 1 postcode - AB10 date - 1364989057

Now I have a form which searches for either the postcode or the region. I'm using JQuery's autocomplete and can get either postcode or the region but not both.

at the moment I have:

$result = $db->query("SELECT DISTINCT `postcode` FROM `areaCovered` WHERE `postcode` LIKE '%$search%' ORDER BY `postcode` ASC") or die('Something went wrong');

Then I use the data retrieved from the database result and put into JSON:

$json = '[';
$first = true;
while ($row = $result->fetch_assoc())
    if (!$first) { $json .=  ','; } else { $first = false; }
    $json .= '{"value":"'.$row['postcode'].'"}';
$json .= ']';
echo $json;

How can I firstly join the two tables to search for either postcode or region that exists only in the area covered table and then output the result whether it is region or the postcode.

I hope that makes sense to you,





Instead ofdistinct you should usegroup by, and join them.

Something in the lines of:

    a.`postcode` as postcode,
    a.`region` as region,
    `area` as a
    inner join
        `areaCovered` as ac
    a.`postcode` like '%$search%'
    a.`region` like '%$search%'
group by
order by
    a.`postcode` asc

Preferably I would justjson_encode() the whole result set and parse it client side, but it looks like you may need to have a special JSON structure for a jQuery plugin?

$list = array();
while ($row = $result->fetch_assoc()) {
        array('value' => $row['postcode'] . ', ' . $row['region'])

echo json_encode($list);

This will create a JSON structure that looks like;

        "value": "123 45, Region 1"
        "value": "678 90, Region 2"

People are also looking for solutions to the problem: php - Postegre sql update statement quotes issue


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.