Can anyone help with PHP/MySQL search script?

[toastyman]

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

 

[Beansprout]

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:

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

 

[toastyman]

Ok i'll give it another shot then.

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:

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.