Re: Incorrect Query

Hi Andrew,

Sorry I should have said the values were just examples, its a java program so
the id, firstname, and lastname are variables taken from the user entry, I left
the id blank in the example as more often than not the user will only be
entering the persons name.

The names are stored with initcap() values in the database, but when the person
is doing a search to retrieve this data they may enter the name all in lower
case, so I probably don't need initcap() in the select clause, thanks for your
advice I will look at a different way of comparing the values in my java code.

I'll say hi to Andrew for you.

Regards,

Sharon Cowling

Quoting Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>:

> On Wed, 2002-05-08 at 17:06, Sharon Cowling wrote:
> > Hi,
> >
> > I'm working on an application, the user enters the customers id or
> > their firstname or their lastname or their firstname and lastname.
> > The problem is when they enter their firstname AND their lastname
> > it returns as if you had entered either their firstname OR their
> > lastname. I must be missing brackets somewhere, but I can't figure
> > it out, I've tried everything I could think of, does anyone have
> > any ideas?
> >
> > Query:
> > select person_id, initcap(firstname), initcap(lastname), dob, street, city
>
> > from person5
> > where person_id = ''
> > or ( ( firstname = initcap('sharon') )
> > or ( lastname = initcap('cowling') ) )
> > or ( ( firstname = initcap('sharon')
> > and lastname = initcap('cowling') ) )
> > order by lastname;
>
> Hi Sharon,
>
> Several points here:
>
> Firstly, you are presumably building this SQL in a program. You should
> probably look at the values for firstname and lastname in your program,
> and construct your SQL differently, as appropriate.
>
> If that's not possible, and you have to have a static SQL string that
> you replace values into, then you will want to do something like this:
>
> SELECT ...
> WHERE (firstname = '$fname' AND '' = '$lname' )
> OR (lastname = '$lname' AND '' = '$fname' )
> OR (firstname = '$fname' AND lastname = '$lname' )
>
> This means that you compare firstname with $fname _only_ if $lname is
> empty, and vice versa. I'll leave it to you to decide if the person_id
> = '' was doing the correct thing...
>
> Thirdly, if you don't trust your database to contain 'initcap()' values
> (looking at your output specifiers), how can you compare against
> initcap() values in the where clause! In the where clause you should
> really be something like:
> WHERE lower(firstname) = lower('$fname') ...
>
> Cheers,
> Andrew.
>
> PS. Say "Hi" to Andrew McClure from me :-)
> --
> --------------------------------------------------------------------
> Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
> WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
> Are you enrolled at http://schoolreunions.co.nz/ yet?
>

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/