At 10:23 PM -0700 11/1/10, Tommy Pham wrote:
> -----Original Message-----
From: tedd [mailto:tedd.sperling@xxxxxxxxx]
Sent: Sunday, October 31, 2010 9:00 AM
To: gp@xxxxxxxxxxxxxxxxx; php-general@xxxxxxxxxxxxx
Subject: 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/
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
