Stupid Question SQL Searching

[dmurray1414]

I have a database full of names and contact info. The names are split into last
and first, as would be expected. I would like to create an idiot-proof method
of searching for people in the DB, using 1 form only. A person would then be
able to enter first name, last name, first and last, etc. I can get the first
name search working (John), and I can get the last name search working (Doe),
but I can't think of how to form a query that would take care of a first AND
last name search (John Doe) since they are in different fields. I'm sure it's
something stupid I'm overlooking, but a push in the right direction would be
appreciated.

Thanks!

[Damnit Spock!]

SELECT whatever
FROM data table
WHERE firstname = form.firstname AND lastname = form.lastname
ORDER however
etc...

[dmurray1414]

Thanks, but not quite what I'm looking for. I only have one form on the search
page, not one for first name and one for last. It works fine for searching by
last name only or first name only, but if someone enters a first name AND a
last name, I don't know what to do since the name is seperated into first and
last in the DB.

[mxstu]

You only have one "textbox" on the form? Don't you have one box for "firstName" and a separate field for "lastName"?

[jonwrob]

Assuming you meant you had one field on your form, I have a stupid question for
you, how do you know if the person typed a first name or last name in the field?

If they type both, separated by a space, then do that. Separate the form
field value into two variables on the space (look up functions find, left, and
right for how to do this) and use that in your where clause.

JR

[mxstu]

jonwrob,

Not to mention how do you know if the user typed: firstname (space) last name -OR- lastname (space) first name, and what happens if either name has a space in it?

[jonwrob]

That's a fair point.

To the OP: You really need to think about how you want your search form to
deal with those issues.

The one additional thing you might want to consider, would be to concatenate
your first and last name columns. I don't know what db your using. In Oracle,
the concatenation operator is ||, so you could search your two columns as one
like:

firstname || lastname
or even
firstname || ' ' || lastname

JR

[dmurray1414]

Thanks for the replies guys. It's like this - I want to have an address book
database searchable, but I want to make it easy. I would rather spend more time
making it simple now than deal with people who can't figure it out later.
Therefore, I would like only one text box to search for a name. Right now, I
have it working so that you can enter either a first name or last name and it
will find anyone who matches that. That was simple using WHERE first =
'#text.text#' OR WHERE last = '#text.text#' . I am counting on people also
trying to enter the full name instead of just first or last, and I need to be
able to have it respond to that. So, if someone asks for "John Doe" I need to
have my query be able to match it to an entry that has a "Doe" last name and a
"John" first name. I just don't know how to write that. Make sense?

Dan

[Anj01]

Hi

You could first concatenate the entered text :
Like if I entered AAA XXX YYY as the name
you could strip this text as AAA,XXX,YYY

in the query you could write firstname in ( AAA,XXX,YYY) or lastname in
(AAA,XXX,YYY)

Anj