RE: [PHP] search is not case insensitive

> -----Original Message----- > From: ash@ashleysheridan.co.uk [mailto:ash@ashleysheridan.co.uk] > Sent: Sunday, October 31, 2010 2:10 AM > To: Dr Michael Daly; php-general@lists.php.net > Subject: Re: [PHP] search is not case insensitive > > This isn't a php question but a mysql one. Take out the lower() part of the > sql statement, as like is case insensitive by default. > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > ----- Reply message ----- > From: "Dr Michael Daly" <gp@holisticgp.com.au> > Date: Sun, Oct 31, 2010 04:47 > Subject: [PHP] search is not case insensitive > To: <php-general@lists.php.net> > > 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. > You could also remove the strtolower(). Just to be sure that field C.description is set with case-insensitive collation, you could use phpMyAdmin to look at the table structure or run SQL 'show create table pbcs_appointment;' on your MySQL tool. The description column should have *_ci (for case-insensitive whereas *_cs is case-sensitive). Regards, Tommy