1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Can anyone help with PHP/MySQL search script?

Discussion in 'HTML, Graphics & Programming' started by toastyman, 30 May 2006.

  1. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    Hi to all,

    I've tried to write this script myself, but am currently low in the experience of search algorithms, so thought i'd come on here to see if anyone fancies taking up this small project.

    This would be easy enough to do if it was a simple enter keywords and return results job, but its not...

    I have a form which has the following form fields:
    Country (select box)
    City (select box)
    Number of days (text box)
    Distance (text box)
    Age group (select box)

    The user can build their query using these form fields, and use as many or as little of them as desired.

    Country, city and age group are exact values, which should return from the database any records which match the criteria exactly.
    Number of days and distance are approximate values, which should return say, the most relevant records.

    The script will also need to order the results in order of relevance.

    Would anyone be interested in doing this script? I'd be happy to put credits on all the search results pages.. the directory in question is tipped by several magazines to become a national resource for hikers and campers over the coming year, so there's a big opportunity for publicity here.

    Post here and i'll give you further details and FTP access over msn or something.

    Cheers
     
  2. Beansprout

    Man of Honour

    Joined: 31 Jan 2004

    Posts: 16,313

    Location: Plymouth

    Actually it's easier than keyword searching because keyword searching is natural language and so on whereas this is simple discreet values :)

    In psuedo-code:

    Code:
    daysmargin = 3, for example.
    distancemargin = 50, for example
    
    SELECT *
    FROM table
    WHERE country = country
              AND city = city,
              AND days <= days + margin AND days >= days - margin,
              AND distance <= distance + distancemargin AND distance >= distance - distancemargin,
              AND agegroup = agegroup;
    
    I'm not sure if MySQL can do anything to automatically weight the results with the closest day and distance. But that's not a problem - you can do that in PHP by going through the result set and first listing the results which exactly match the search term :)
     
  3. toastyman

    Gangster

    Joined: 30 Dec 2005

    Posts: 421

    Ok i'll give it another shot then. :p

    That psuedo code really helps. Its gonna be a lot of predefined queries and switch statements I think.

    One thing that isn't working for me is this query:
    Code:
    SELECT `hikeid` FROM `hiking_daydetails` WHERE `dayid`='1' AND `legid`='1' AND `currentgridreference` LIKE 'ST%' || 'SP%' || 'TQ%' || 'NY%' || 'TL%' || 'TR%' || 'SJ%' || 'SU%' || 'SK%' || 'NZ%' || 'SX%' || 'SO%' || 'TA%' || 'SD%' || 'SE%' || 'TG%' || 'SZ%' || 'SW%'
    
    It doesn't return any results, however, there are over 50 records which should match the above criteria..what am I doing wrong?! I guess its something to do with the LIKE and ORs.