RE: [PHP] search is not case insensitive

At 10:23 PM -0700 11/1/10, Tommy Pham wrote:

-----Original Message-----

From: tedd [mailto:tedd.sperling@gmail.com] Sent: Sunday, October 31, 2010 9:00 AM To: gp@holisticgp.com.au; php-general@lists.php.net Subject: Re: [PHP] search is not case insensitive At 3:47 PM +1100 10/31/10, Dr Michael Daly wrote:

Hi Using a php search form produces a nil return on any information that is capitalised within a mysql database; retrieval is fine for non-capitalised data. Could someone tweak this please? The relevant code I think is as follows: // Description is a BLOB in MySQL... we need to UPPER the blob //values to make the search case-insensitive. $query = "SELECT C.*, A.surname, A.name, A.surname_prefix, A.id

AS user

FROM pbcs_user A, pbcs_join_table_user_app B, pbcs_appointment C

".

"WHERE A.id = B.user_id AND B.appointment_id = C.id ". "AND LOWER(C.description) LIKE '%".strtolower($search_for)."%' AND C.start_time > $start_time AND C.start_time < $end_time ORDER BY C.start_time"; $result = pbcs_db_query($query); Thanks Michael Melb, Aust.

Why are you using a BLOB? You are just storing text data, right? If so, then a VARCHAR will work. Additionally, using a BLOB changes things somewhat in that all data are stored as binary strings and as such makes all comparisons case-sensitive. Too many double negatives for me. Cheers, tedd -- ------- http://sperling.com/

IIRC, the VARCHAR (for MySQL) has a limit of 255 chars. You may have to use one of the *TEXT variants if you need to store a lot of text. Regards, Tommy

Tommy: If you are using a version of MySQL that is older than v 5.0.3, then you are right -- but after that version VARCHAR can hold up to 65,535 characters. How much does the OP need? If that is not enough room, then why not use LONGTEXT (4G)? The differences are how the data is stored. In BLOBS the data is stored in binary strings with no char set and comparisons are based on numeric values of bytes. Whereas, TEXT data are treated as character strings which have a char set and can be sorted and compared based upon collation of the char set. Cheers, tedd -- ------- http://sperling.com/