RE: [PHP] search is not case insensitive

> -----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