Help with distinct query

Databases
1

Just trying to implement an autocomplete using the jquery autocomplete and could do with some help with my query.

This is what I have so far:



$q = strtolower($_GET["q"]);

if (!$q) return;

	 
$sql = "select DISTINCT town as town from towns where town LIKE '$q%'";
$rsd = mysql_query($sql);

$num_rows = mysql_num_rows($rsd);

if ($num_rows > 0) {

while($rs = mysql_fetch_array($rsd)) {
 
$cname = $rs['town'];
echo "$cname\
";
}

} else {

	echo "no matches found";

}

This works well matching what the user types with the town field in my database. However what I need to do is match what they type with several fields from the same table.

The additional field is called “county”

So for example, if somebody types

“West Buckland”

I need it to return the following 2 matches

“West Buckland, Devon”
“West Buckland, Somerset”

Any help much appreciated

2

Can you show a “CREATE TABLE” for the towns table?

3

I think this is what you asked for



CREATE TABLE `towns` (
  `town_id` int(11) NOT NULL,
  `town` varchar(150) collate utf8_unicode_ci NOT NULL,
  `county` varchar(150) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`town_id`),
  KEY `town` (`town`,`county`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Towns';

Is that what you mean?

Thanks

Paul

4

Had a play around without really knowing quite what I am doing and the following is getting close



$sql = "select DISTINCT town as town, county from towns where town LIKE '$q%'";
$rsd = mysql_query($sql);

$num_rows = mysql_num_rows($rsd);

if ($num_rows > 0) {

while($rs = mysql_fetch_array($rsd)) {
 
$cname = $rs['town']. ", " . $rs['county'];
echo "$cname\
";
}

} else {

	echo "no matches found";

}

However it falls down if I type in say “West Buckland So” it does not find a match with “West Buckland, Somerset”

Tried this too but no joy



$sql = "select DISTINCT town as town, county as county from towns where town + " " + county LIKE '$q%' LIMIT 0, 50";

And something similar



$sql = "select DISTINCT town as town, county as county from towns where CONCAT(town, " ", county) LIKE '$q%' LIMIT 0, 50";
5

Hey I think I have worked it out



$sql = "select town, county from towns where CONCAT_WS(' ',RTrim(town),RTrim(county)) LIKE '$q%'";
$rsd = mysql_query($sql);