Thread: search/select case-insensitivly.
I want a PHP program to search case-insensitivly. for example: select Name from mytable where Name = '$Name'; Here $Name is what users' input maybe JOHN, john. How to let it match John in table and find that record? Thanks. Zhidian Du _________________________________________________________________ Surf the Web without missing calls! Get MSN Broadband. http://resourcecenter.msn.com/access/plans/freeactivation.asp
On the PostgreSQL function side of things, you could do:
SELECT name FROM mytable WHERE Name ILIKE('$Name');
Hope this helps! :)
Cheers,
Roj Niyogi
niyogi@pghoster.com
pgHoster - PostgreSQL Web Hosting
http://www.pghoster.com
Zhidian Du wrote:
> I want a PHP program to search case-insensitivly.
>
> for example:
> select Name from mytable where Name = '$Name';
>
>
> Here $Name is what users' input maybe JOHN, john. How to let it match
> John in table and find that record?
>
>
> Thanks.
>
> Zhidian Du
>
>
>
>
>
> _________________________________________________________________
> Surf the Web without missing calls! Get MSN Broadband.
> http://resourcecenter.msn.com/access/plans/freeactivation.asp
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Tue, 2002-10-22 at 17:01, Zhidian Du wrote:
> I want a PHP program to search case-insensitivly.
>
> for example:
> select Name from mytable where Name = '$Name';
>
>
> Here $Name is what users' input maybe JOHN, john. How to let it match John
> in table and find that record?
Although you can simply do as another poster said and use the ILIKE
operator, there are a few things you may want to consider.
You can also do something like:
"SELECT name FROM mytable WHERE lower(name) = '" . strtolower($Name) .
"'; "
This means that PostgreSQL will use an index, if there is an index on
lower(name):
CREATE INDEX lcname ON mytable( lower(name) );
This will give you the most efficient access if you have many records in
'mytable', whereas ILIKE will require a full-table scan.
Regards,
Andrew.
--
---------------------------------------------------------------------
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
Survey for free with http://survey.net.nz/
---------------------------------------------------------------------
The best way is to always save data in your database in the same format ... upper or lower ... then create an index on your field classicaly ... CREATE INDEX ix_name ON mytable(name); SELECT name FROM mytable WHERE name = '" . strtolower($Name) ."'; You will ever use the index ... and it's simple to implement ... You can also easily do an update of your database like : update mytable set name=lower(name); to get all the old data in the good format ... Do not forget to vacuum the table after that ! Regards, Le Mardi 22 Octobre 2002 10:12, Andrew McMillan a écrit : > On Tue, 2002-10-22 at 17:01, Zhidian Du wrote: > > I want a PHP program to search case-insensitivly. > > > > for example: > > select Name from mytable where Name = '$Name'; > > > > > > Here $Name is what users' input maybe JOHN, john. How to let it match > > John in table and find that record? > > Although you can simply do as another poster said and use the ILIKE > operator, there are a few things you may want to consider. > > You can also do something like: > > "SELECT name FROM mytable WHERE lower(name) = '" . strtolower($Name) . > "'; " > > This means that PostgreSQL will use an index, if there is an index on > lower(name): > > CREATE INDEX lcname ON mytable( lower(name) ); > > This will give you the most efficient access if you have many records in > 'mytable', whereas ILIKE will require a full-table scan. > > Regards, > Andrew. -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Tel. 33-144949901 Fax. 33-144949902
On Tue, 2002-10-22 at 22:10, Hervé Piedvache wrote:
> The best way is to always save data in your database in the same format ...
> upper or lower ... then create an index on your field classicaly ...
>
> CREATE INDEX ix_name ON mytable(name);
> SELECT name FROM mytable WHERE name = '" . strtolower($Name) ."';
>
> You will ever use the index ... and it's simple to implement ...
> You can also easily do an update of your database like :
> update mytable set name=lower(name);
> to get all the old data in the good format ...
> Do not forget to vacuum the table after that !
Yes, of course you can do that - I was assuming there was a reason why
the poster wanted case preserved.
I generally preserve case for email addresses, for example. Sometimes
the LHS of an e-mail address is case sensitive, even though the RHS
(domain name) may not be. Searching on such a field, however, I would
probably want to search case insensitively.
There are other reasons for preserving case, of course - especially the
case that people write their names. I usually write my own name
McMillan, and it _infuriates_ me when I see software that has translated
that to Mcmillan. Lest you think that is easily computed, I know people
with names which are correctly capitalised as Macindoe and Macinnes -
the capitalisation is idiosyncratic. If you remove the capitalisation
when you write those values to the file you are removing potentially
valuable information.
Finally, you should also consider:
update mytable set name=lower(name) WHERE name != lower(name);
will require a lot less vacuuming :-)
Cheers,
Andrew.
--
---------------------------------------------------------------------
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
Survey for free with http://survey.net.nz/
---------------------------------------------------------------------
On Mon, Oct 21, 2002 at 22:01:19 -0600,
Zhidian Du <duzhidian@hotmail.com> wrote:
> I want a PHP program to search case-insensitivly.
>
> for example:
> select Name from mytable where Name = '$Name';
>
>
> Here $Name is what users' input maybe JOHN, john. How to let it match John
> in table and find that record?
As mentioned in other replies you can use the lower or upper functions
to get consistant case.
If you want case preerved in the table, you can make the searches faster
(at the expense of slowing down updates) by creating an index using a function.
Something like:
create index on table mytable(lower(Name));
Will speed up searches like:
select Name from mytable where lower(Name) = lower('$Name');
As long as I know, some databases will find John even if you ask for
JOHN.
If not, then here is your SELECT (for mySQL).
$sql = "SELECT * WHERE UPPER(name) = UPPER('$name')";
Your problem is solved now.
--
Maxim Maletsky
maxim@php.net
www.PHPBeginner.com // where PHP Begins
Hervé Piedvache <herve@elma.fr> wrote... :
> The best way is to always save data in your database in the same format ...
> upper or lower ... then create an index on your field classicaly ...
>
> CREATE INDEX ix_name ON mytable(name);
> SELECT name FROM mytable WHERE name = '" . strtolower($Name) ."';
>
> You will ever use the index ... and it's simple to implement ...
> You can also easily do an update of your database like :
> update mytable set name=lower(name);
> to get all the old data in the good format ...
> Do not forget to vacuum the table after that !
>
> Regards,
>
> Le Mardi 22 Octobre 2002 10:12, Andrew McMillan a écrit :
> > On Tue, 2002-10-22 at 17:01, Zhidian Du wrote:
> > > I want a PHP program to search case-insensitivly.
> > >
> > > for example:
> > > select Name from mytable where Name = '$Name';
> > >
> > >
> > > Here $Name is what users' input maybe JOHN, john. How to let it match
> > > John in table and find that record?
> >
> > Although you can simply do as another poster said and use the ILIKE
> > operator, there are a few things you may want to consider.
> >
> > You can also do something like:
> >
> > "SELECT name FROM mytable WHERE lower(name) = '" . strtolower($Name) .
> > "'; "
> >
> > This means that PostgreSQL will use an index, if there is an index on
> > lower(name):
> >
> > CREATE INDEX lcname ON mytable( lower(name) );
> >
> > This will give you the most efficient access if you have many records in
> > 'mytable', whereas ILIKE will require a full-table scan.
> >
> > Regards,
> > Andrew.
>
> --
> Hervé Piedvache
>
> Elma Ingénierie Informatique
> 6 rue du Faubourg Saint-Honoré
> F-75008 - Paris - France
> Tel. 33-144949901
> Fax. 33-144949902
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org