searching.

[Anon]

I'm new to php, and mysql.
I have a database set up with several entries. Name, Password, City, State and Zip. Now I’m having trouble creating a script that can search by those keywords. And display to a user all the matches. I'm looking for help, or even better I’d like a web site or book that can teach me how to do it.

BEN

[glowball]

Say you want to search the City and State columns for the word "New" (to match things like "New York" in both columns). Change them all to lowercase so you don't have to worry about the case sensitivity.

$keyword = strToLower("New");

SELECT *
FROM tablename
WHERE LCASE(City) LIKE '$keyword'
OR LCASE(State) LIKE '$keyword'

I have Teach Yourself SQL in 10 Minutes by Ben Forta, has all the SQL you could need in real life and for under $10.

[[deleted]]

This will only report EXACT matches.
if $keyword = 'New', then only the matches for 'new' are reported.
If you also want 'new york' you'll have to add wildcards:

SELECT *
FROM tablename
WHERE LCASE(City) LIKE '%$keyword%'
OR LCASE(State) LIKE '%$keyword%'

Second note: this is also a very slow method because the LCASE() operation means that no indexes can be used (unless you have functional indexes for LCASE(City) and LCASE(State))

[Anon]

OK, so I went out and got Teach Yourself sql in 10mins, and I still have a question.
In my opening question I asked. How I would go about searching for name, password, city, state, and zip. Your reply made sense I can read sql, but just how do I implement that to my php document.
Does each one of my fields (City, state, zip) get their own sql command? Can I just make one? If you can reference a section to the book I can focus on just a chapter that deals with this subject.

Thanks, Ben.

[Anon]

Sorry i replyed to the wrong message....
this also went to a user by the name, Vincent....

OK, so I went out and got Teach Yourself sql in 10mins, and I still have a question.
In my opening question I asked. How I would go about searching for name, password, city, state, and zip. Your reply made sense I can read sql, but just how do I implement that to my php document.
Does each one of my fields (City, state, zip) get their own sql command? Can I just make one? If you can reference a section to the book I can focus on just a chapter that deals with this subject.

Thanks, Ben.

[glowball]

Yes, my apologies, I forgot the percent signs! Big typo on my part and I hope I didn't waste your time. vincent has a good point about the indices though.

I'm sure vincent will have a better idea, but to get you started you can do something akin to:

$keyword = strToLower("New");

SELECT *
FROM tablename
WHERE LCASE(City) LIKE '%$keyword%'
OR LCASE(State) LIKE '%$keyword%'
OR LCASE(Zip) LIKE '%$keyword%'

You can get MUCH fancier than that, especially in your ORDER BY clause, but that should at least get you matching rows.