Help with query to search for names and dates

[JohnnyT]

Hi,

I'm trying to write a script that will allow a user to search my database for peoples names or their year of birth.

I was thinking along the lines of:
SELECT * FROM my_db WHERE first_name LIKE '%$searchString%'

Is the above a good way of searching for somebodies name?

I'm strugging with searching for the year of birth because ideally I want to make it check within 5 years either way.

For instance:
SELECT * FROM my_db WHERE (birth_year - '$inputYear')<=5 OR ('$inputYear' - birth_year)<=5

So... my questions are:
Is the first example the best way of searching for a name from a user input within a database?

Can someone help me with the sql syntax for my second query?

Many thanks for all your help

Cheers

John ;-)



I don't make mistakes, I'm merely beta-testing life.

 

[audiopro]

The like command can be too greedy but try this untested code.

SELECT * FROM my_db WHERE first_name LIKE '%$searchString% AND ABS(birth_year - $inputYear)<6

Keith

http://www.studiosoft.co.uk

 

[JohnnyT]

Keith

Many thanks for that mate. I'd never heard of ABS but (after googling) it looks like its just what I need.

Would you recommend something else apart from LIKE for doing what I'm requiring?

Thanks again

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[audiopro]

Depends how accurate you need to be and what the search requires.
You could always give them both options.

Keith

http://www.studiosoft.co.uk

 

[JohnnyT]

Keith

I'm going to be making an 'advanced' search page. So I was thinking of adding some intelligence to the query. They can fill in as many boxes as they like and it will try to match them exactly, but if there's no matches, it will become fuzzier.

I'll have a play about with it. I just wondered if there were any commands that were similar to LIKE...

Many thanks for all your help

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[ingresman]

Like is a bit of a beast in this case. For example if you have a table with 1,000,000 rows and you try to find "%fred", every row in the table will have to be read (unless it is restricted by another part of the where clause) to see if it is present. For example if you have the value
"alfred" MYSQL will have to look at the entire string looking for the pattern fred. Because in general you can't say you have found everything unless you have looked everywhere you have an issue. This is always a problem in searching. However if you search for "fred%" the optimiser will know that it can go to the index and only read the rows that start with fred which might be a much smaller number. The upshot is you will almost certainly have to read every row in the table (table scan) if you have % at the start.
Not sure if this helps really. You could look at full text search indexes to see if that gets you round the issue ?

 

[JohnnyT]

Thanks for the info. You've made a really good point.

Cheers

John ;-)

I don't make mistakes, I'm merely beta-testing life.