Re: 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/