php - MySQL Join and Distinct
66
I have two tables one called area and one called area covered. Table area contains the fields postcode and region.
e.g:
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,
thanks
Answer
Solution:
Instead of
distinctyou should usegroup by, and join them.Something in the lines of:
Preferably I would just
json_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?This will create a JSON structure that looks like;