RE: [PHP] search is not case insensitive
At 10:23 PM -0700 11/1/10, Tommy Pham wrote: -Original Message- From: tedd [mailto:tedd.sperl...@gmail.com] Sent: Sunday, October 31, 2010 9:00 AM To: g...@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 Tommy: If you are using a version of MySQL that is older than v 5.0.3, then you are right -- but after that version VARCHAR can hold up to 65,535 characters. How much does the OP need? If that is not enough room, then why not use LONGTEXT (4G)? The differences are how the data is stored. In BLOBS the data is stored in binary strings with no char set and comparisons are based on numeric values of bytes. Whereas, TEXT data are treated as character strings which have a char set and can be sorted and compared based upon collation of the char set. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] search is not case insensitive
-Original Message- From: tedd [mailto:tedd.sperl...@gmail.com] Sent: Tuesday, November 02, 2010 8:09 AM To: Tommy Pham; g...@holisticgp.com.au; php-general@lists.php.net Subject: RE: [PHP] search is not case insensitive At 10:23 PM -0700 11/1/10, Tommy Pham wrote: -Original Message- From: tedd [mailto:tedd.sperl...@gmail.com] Sent: Sunday, October 31, 2010 9:00 AM To: g...@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 Tommy: If you are using a version of MySQL that is older than v 5.0.3, then you are right -- but after that version VARCHAR can hold up to 65,535 characters. Tedd, That tells you that I haven't been keeping track of version changes for MySQL. I remember that when 5 was still an RC. How much does the OP need? If that is not enough room, then why not use LONGTEXT (4G)? The differences are how the data is stored. In BLOBS the data is stored in binary strings with no char set and comparisons are based on numeric values of bytes. Whereas, TEXT data are treated as character strings which have a char set and can be sorted and compared based upon collation of the char set. Cheers, tedd -- --- http://sperling.com/ I totally agree on VARCHAR/TEXT over BLOBs as you can use the index (with/without FULLTEXT) more efficiently for faster query results. Regards, Tommy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] search is not case insensitive
-Original Message- From: a...@ashleysheridan.co.uk [mailto:a...@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 g...@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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] search is not case insensitive
-Original Message- From: tedd [mailto:tedd.sperl...@gmail.com] Sent: Sunday, October 31, 2010 9:00 AM To: g...@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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
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 g...@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. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] search is not case insensitive
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 g...@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. -- 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
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/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
