SQL Query Help...Searching?

Question

I have probably a very basic SQL query, but I can't figure it out.

Basically I have a table with a bunch of names in it. These are separated. First Name, Middle Name, Last Name all are 3 different columns.

I will accept a search string from the user to search these names. I'm using a basic SELECT FROM WHERE in which I see if any of the fields are like the search string. This works perfect if there is 1 name inputted -- either a first, middle, or last.

HOWEVER....if they put in 2 names, such as John Doe, it doesn't work at all because it doesn't do multicolumn searching.

Is there an easy way to search for a full name "John Doe" where last_name column has Doe and first_name column has John?

Answer 1

You'll have to parse out your entry into two variables, once you've done that, and you know which one's first name and which one's last name, you can have add an "and" in your where clause, such that:



select * from NAME_TBL where FIRST_NAME = $FIRST_ENTRY and LAST_NAME = $LAST_ENTRY;

Answer 2

on the way into the query you need to parse the data out into three columns... remember that sql has no idea what your columns are nor cares.. so it has no way of intelligently knowning that 'john doe' is a first and last name.. or a middle and last.. or a middle and first.



Sorry, you gotta do the leg work up front.

Answer 3

SELECT * FROM nameoftable

WHERE last_name='doe' AND first_name='John';