I have a fix. Thanks to the list for steering me in the right direction Just in case anyone else is lost, here it is, via use of the CONVERT function to convert the binary data to text which can then be searched case insensitively in mysql: Replace this line: "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"; with: "AND CONVERT(C.description USING latin1) LIKE '%".($search_for)."%' AND C.start_time > $start_time AND C.start_time < $end_time ORDER BY C.start_time"; (the only bit that changes is the text betw the first two 'ANDS') It comes from PBCS online appointment software. Thanks Michael -----Original Message----- From: Dr Michael Daly [mailto:gp@xxxxxxxxxxxxxxxxx] Sent: Sunday, 31 October 2010 3:47 PM To: php-general@xxxxxxxxxxxxx Subject: search is not case insensitive 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. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
