RE: [PHP] search is not case insensitive

> -----Original Message----- > From: tedd [mailto:tedd.sperling@gmail.com] > Sent: Tuesday, November 02, 2010 8:09 AM > To: Tommy Pham; gp@holisticgp.com.au; php-general@lists.php.net > Subject: 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. Tedd, That tells you that I haven't been keeping track of version changes for MySQL. I remember that when 5 was still an RC. > 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/ I totally agree on VARCHAR/TEXT over BLOBs as you can use the index (with/without FULLTEXT) more efficiently for faster query results. Regards, Tommy