I know I could do this with 3 different searches, but I presume there
is an easier way I can not see.

I have a form that is submitted to a PHP script that has 2 fields,
first name and last name.
If just the first name is submitted I want to search by first name and
return all that match.
If just the last name is submitted I want to search by last name and
return all that match.
If both are submitted, I want to search by last name and return all
that match both.

By "match" I mean that I want to return all that contain the first or
last name, e.g.: partial names are submitted and full names are found.

I have an index: fname that is the first then last names (2 fields)
I have an index: lname that is the last then first (2 fields)

Is this clear ?
I guess there are two questions:
1) how to match a partial name ?
2) can I do all 3 cases in one search ?

bill

bill wrote:
> I know I could do this with 3 different searches, but I presume there
> is an easier way I can not see.
>
> I have a form that is submitted to a PHP script that has 2 fields,
> first name and last name.
> If just the first name is submitted I want to search by first name and
> return all that match.
> If just the last name is submitted I want to search by last name and
> return all that match.
> If both are submitted, I want to search by last name and return all
> that match both.
>
> By "match" I mean that I want to return all that contain the first or
> last name, e.g.: partial names are submitted and full names are found.
>
> I have an index: fname that is the first then last names (2 fields)
> I have an index: lname that is the last then first (2 fields)
>
> Is this clear ?
> I guess there are two questions:
> 1) how to match a partial name ?
> 2) can I do all 3 cases in one search ?
>
> bill
>

Bill,

Generally when I need to do searches like this I create the SQL in a
programming language (i.e. PHP, Perl, etc.) according to the input.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Jul 4, 10:42 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> bill wrote:
> > I know I could do this with 3 different searches, but I presume there
> > is an easier way I can not see.
>
> > I have a form that is submitted to a PHP script that has 2 fields,
> > first name and last name.
> > If just the first name is submitted I want to search by first name and
> > return all that match.
> > If just the last name is submitted I want to search by last name and
> > return all that match.
> > If both are submitted, I want to search by last name and return all
> > that match both.
>
> > By "match" I mean that I want to return all that contain the first or
> > last name, e.g.: partial names are submitted and full names are found.
>
> > I have an index: fname that is the first then last names (2 fields)
> > I have an index: lname that is the last then first (2 fields)
>
> > Is this clear ?
> > I guess there are two questions:
> > 1) how to match a partial name ?
> > 2) can I do all 3 cases in one search ?
>
> > bill
>
> Bill,
>
> Generally when I need to do searches like this I create the SQL in a
> programming language (i.e. PHP, Perl, etc.) according to the input.
>
> --

Jerry,
Ok, I can do that. Now about the other part of the question:

what is the syntax for doing a selection on a partial key.
eg: if the user enters "Smi" when searching for Smith or Smilie
or . . .

bill wrote:
> On Jul 4, 10:42 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> bill wrote:
>>> I know I could do this with 3 different searches, but I presume there
>>> is an easier way I can not see.
>>> I have a form that is submitted to a PHP script that has 2 fields,
>>> first name and last name.
>>> If just the first name is submitted I want to search by first name and
>>> return all that match.
>>> If just the last name is submitted I want to search by last name and
>>> return all that match.
>>> If both are submitted, I want to search by last name and return all
>>> that match both.
>>> By "match" I mean that I want to return all that contain the first or
>>> last name, e.g.: partial names are submitted and full names are found.
>>> I have an index: fname that is the first then last names (2 fields)
>>> I have an index: lname that is the last then first (2 fields)
>>> Is this clear ?
>>> I guess there are two questions:
>>> 1) how to match a partial name ?
>>> 2) can I do all 3 cases in one search ?
>>> bill
>> Bill,
>>
>> Generally when I need to do searches like this I create the SQL in a
>> programming language (i.e. PHP, Perl, etc.) according to the input.
>>
>> --
>
> Jerry,
> Ok, I can do that. Now about the other part of the question:
>
> what is the syntax for doing a selection on a partial key.
> eg: if the user enters "Smi" when searching for Smith or Smilie
> or . . .
>
>

.... WHERE column_name LIKE 'Smi%';

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 4 Jul, 15:10, bill <will...@TechServSys.com> wrote:
> I know I could do this with 3 different searches, but I presume there
> is an easier way I can not see.
>
> I have a form that is submitted to a PHP script that has 2 fields,
> first name and last name.
> If just the first name is submitted I want to search by first name and
> return all that match.
> If just the last name is submitted I want to search by last name and
> return all that match.
> If both are submitted, I want to search by last name and return all
> that match both.
>
> By "match" I mean that I want to return all that contain the first or
> last name, e.g.: partial names are submitted and full names are found.
>
> I have an index: fname that is the first then last names (2 fields)
> I have an index: lname that is the last then first (2 fields)
>
> Is this clear ?
> I guess there are two questions:
> 1) how to match a partial name ?
> 2) can I do all 3 cases in one search ?
>
> bill

Regarding:

"If both are submitted, I want to search by last name and return all
that match both."

This is not possible. You will need to search by both last name and
first name, otherwise you have no way of knowing whether the first
name matches.

Also you say "By "match" I mean that I want to return all that contain
the first or last name, e.g.: partial names are submitted and full
names are found."

This contradicts the part where you say that you must match "both". If
you want a match that contains the first "or" last name, then that is
a search that matches "either".

Either = or
Both = and

Take some time to sort out your boolean logic

On Jul 5, 6:09 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 4 Jul, 15:10, bill <will...@TechServSys.com> wrote:
>
>
>
> > I know I could do this with 3 different searches, but I presume there
> > is an easier way I can not see.
>
> > I have a form that is submitted to a PHP script that has 2 fields,
> > first name and last name.
> > If just the first name is submitted I want to search by first name and
> > return all that match.
> > If just the last name is submitted I want to search by last name and
> > return all that match.
> > If both are submitted, I want to search by last name and return all
> > that match both.
>
> > By "match" I mean that I want to return all that contain the first or
> > last name, e.g.: partial names are submitted and full names are found.
>
> > I have an index: fname that is the first then last names (2 fields)
> > I have an index: lname that is the last then first (2 fields)
>
> > Is this clear ?
> > I guess there are two questions:
> > 1) how to match a partial name ?
> > 2) can I do all 3 cases in one search ?
>
> > bill
>
> Regarding:
>
> "If both are submitted, I want to search by last name and return all
> that match both."
>
> This is not possible. You will need to search by both last name and
> first name, otherwise you have no way of knowing whether the first
> name matches.
>
> Also you say "By "match" I mean that I want to return all that contain
> the first or last name, e.g.: partial names are submitted and full
> names are found."
>
> This contradicts the part where you say that you must match "both". If
> you want a match that contains the first "or" last name, then that is
> a search that matches "either".
>
> Either = or
> Both = and
>
> Take some time to sort out your boolean logic

Actually, my logic was fine, my syntax was sloppy.
my logic is both, but I meant in either (of 3 cases)

Anyway, I have it running now just fine, thanks for the help.

bill