SitePoint Sponsor

User Tag List

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

    Search query help

    My search functionality is not great on my site and I could do with some advice on improving this.

    Essentially my database holds the following information about each accommodation business.

    address1
    address2 (optional)
    townCity
    county
    Postcode

    Btw this is a UK based site.

    So lets say we have the address

    Crown Hill (address1)
    West Buckland (address2)
    Wellington (townCity)
    Somerset (county)
    TA21 9SZ (postcode)

    My site has a search form allowing a user to type in a search location.

    So for example, someone may type in "West Buckland, Somerset"

    Currently in my code, I explode the location that the user types in, into an array then use code like the following which kind of works if their search location is only one element e.g "West Buckland"

    I am not currently checking against address1 as have assumed this is just a street name - which I know will not always be the case

    Code:
    $loc_array = explode(",", urldecode($this->loc));
    
    $sql = "SELECT ? FROM accommodation WHERE  (accommodation.address2 LIKE '%".$db->prepare($loc_array[0])."%' OR accommodation.townCity LIKE '%".$db->prepare 
    ($loc_array[0])."%' OR accommodation.county LIKE '%".$db->prepare($loc_array[0])."%' OR accommodation.postcode LIKE '%".$db->prepare($loc_array[0])."%')";
    My code doesn't work if someone enters something like
    "West Buckland, Somerset"

    Any advice how to put this query together much appreciated. I cannot think of any way of doing it efficiently - my only thoughts are using a very long set of "AND" and "OR" conditionals which I am sure would be really inefficient.

    Thanks in advance

    Paul
    Mediakitchen Limited
    App Development | Website Design & Development | Flash Game Development
    Somerset, UK
    http://www.mediakitchen.co.uk

  2. #2
    SitePoint Zealot
    Join Date
    May 2005
    Location
    Suwanee GA
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did something like this recently. I replaced commas, spaces, quotes, etc with wildcards% and did a concatenation of all fields to test against.

    select address1+address2+townCity+county+postcode as longaddress
    from table
    where address1+address2+townCity+county+postcode like '%West%Buckland%%Somerset%'


    matches Crown HillWest BucklandWellingtonSomersetTA21 9SZ

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Taunton, UK
    Posts
    787
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks esearing - I will give that a go.

    Cheers

    Paul
    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
  •