SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with distinct query

    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:

    Code:
    $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\n";
    }
    
    } 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
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,029
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you show a "CREATE TABLE" for the towns table?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this is what you asked for

    Code:
    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
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Had a play around without really knowing quite what I am doing and the following is getting close

    Code:
    $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\n";
    }
    
    } 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

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

    Code:
    $sql = "select DISTINCT town as town, county as county from towns where CONCAT(town, " ", county) LIKE '$q%' LIMIT 0, 50";
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey I think I have worked it out

    Code:
    $sql = "select town, county from towns where CONCAT_WS(' ',RTrim(town),RTrim(county)) LIKE '$q%'";
    $rsd = mysql_query($sql);
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •