thanks for this Ash...I didn't really understand but I do now! unfortunately the fix didn't work, possibly bec the mysql data is in binary format (default then becomes: case sensitive). I got a 'syntax error' result I'll find a mysql forum Michael 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@xxxxxxxxxxxxxxxxx> Date: Sun, Oct 31, 2010 04:47 Subject: search is not case insensitive To: <php-general@xxxxxxxxxxxxx> 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 Dr Michael Daly MB, BS GradDip(Integrative Medicine), GradCert(Evidence Based Practice), M Bus(Information Innovation), GradDip(Document Management) 03 9521 0352 0413 879 029 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
