--- In firebird-php@yahoogroups.com, Lester Caine wrote:

>
> jackdmason wrote:
> > This solution will not work. Both InterBase and Firebird allow case insensitive searches, as does MySQL. The php interface should allow that option to be used as part of a query.
> >
> > You cannot just capitalize the first and last name and accomplish anything. "McGrath" would not be found, nor would "van Duran", "MacDougal", etc., nor can you catch instances where a name has been entered incorrectly, such as Mcgee, variant spellings, etc.
> >
> > At present we have to have our web server call a store server to process InterBase accesses because of this very reason; not only with names, but with book titles, authors (may or may not a name or company), etc.
> >
> > We just need the php interface, at least for Firebird, to be fixed so we can do case insensitive searches. Delphi can do it, C++ can do it. Why cannot php do it?
>
> Please don't top post ...
>
> I do not understand your problem?
>
> I made no mention of just capitalizing some letters. Shadow columns which are
> all upper case or some other filtered version of the data work well cross
> database and can be tailored to make indexing of that data work to our
> advantage. This has nothing to do with the language we are using to access the
> database?
>
> The next question has to be "Which abstraction layer are you using in PHP to
> access the data?" There is nothing that I know of in PHP that prevents the same
> queries used in other languages from working in PHP. However some PHP
> applications will get in the way of that.
>
> One thing that should perhaps have been listed is the COLLATE filters which will
> provide case insensitive searches directly in the database.
> http://www.destructor.de/firebird/caseinsensitivesearch.htm provides a very nice
> historic view of the development of this. Personally I'm still using the older
> style of working since my PHP applications can be used against a number of
> database engines. If I was to drop all cross database capability then it would
> be worth switching to the other methods, but it is also worth pointing out that
> the UNICODE_CI method does not provide an easy way of testing the 'style' of the
> inputs. When data has not been correctly entered with the preferred style of
> upper and lower case, the incorrect entries will be displayed as if they were
> correct, while the 'older fashioned' way of working at least allows some level
> of checking. THAT is where upper casing the correct letters can be important?
> You just have to look at paypal traffic to see the results of not bothering at all.
>
> --
> Lester Caine - G8HFL
> -----------------------------
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>

Thanks, Lester. I apologize for not understanding your first post on this. Reading the article you referenced explained your solution, which would surely work.

However, the first part of the article, to which you referred, would add extra fields to the record (1 per existing text key) and increase the overhead search time.

The very end of the article alludes to the actual solution:

"Starting with Firebird 2.1.2 (or earlier, at least 2.1.2 is
what the Release Notes seem to tell), you can use a new Case
Insensitive Collation named UNICODE_CI for the UTF8 character
set (Unicode).

So when your default character set is UTF8, your table
declaration might look like this:

CREATE TABLE PERSONS (

PERS_ID INTEGER NOT NULL PRIMARY KEY,

LAST_NAME VARCHAR(50) COLLATE UNICODE_CI,
FIRST_NAME VARCHAR(50) COLLATE UNICODE_CI

);

Your Index is a regular index declaration without any specials:

CREATE INDEX IDX_PERSON_LASTNAME ON PERSONS (LAST_NAME);

And your search expressions need no UPPER or whatsoever:

SELECT * FROM PERSONS WHERE LAST_NAME = :SEARCH_STRING

That's it."

The databases we access are over 12 years old, and while we could rebuild them, that still skirts the issue. InterBase 6 and all Firebird derivatives have been able to do case insensitive searches without having to add fields or special collations. There is a query parameter that specifies the search is to be case insensitive.

This parameter is available for Delphi, C++, IBO_Objects, etc. but does not appear to be available via PHP. That is the issue. The option may actually be available via PHP but just undocumented since the PHP documentation is abysmally sparse.

The option should be available actually for the fetch (in addition to IBASE_TEXT and IBASE_UNIXTIME) but since it is not documented, it is hard to know.

Who maintains the Firebird PHP interface? That is where this problem needs to be resolved with either better documentation or implementation of a missing Firebird capability.