Thread: using 'lower' in an sql statement?

Hi All,

i have the following code which i am using to try and pullout the records which include a string the user inputs.

for example if there is a record in the db which stores "this is my first string"

and the user searches for "this is my" then i want it to find it

and i have got this working but i run into trouble when the user inputs "This Is My" then it wont find the record because of the uppercase in the inputted string.

so i have tried this

PHP Code:

$sql = "SELECT * FROM info WHERE LOWER(data) LIKE LOWER('%".$input."%')"; 


but that doesnt find a match for lower case input nor uppercase?

anyone have any ideas
thanks
Luke

p.s the field type is a blob and lower doesnt work with it

so the doc says this

Code:

 LOWER() (and           UPPER()) are ineffective when           applied to binary strings           (BINARY,           VARBINARY,           BLOB). To perform  lettercase           conversion, convert the string to a nonbinary string:         
 mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York    | new york                          |

but i am unable to incoroperate into my code

anyone able to assist me with this?
thanks
+-------------+-----------------------------------+

Shot in the dark here, but do you have to use MySQL SET? What about something like this:

PHP Code:

$sql = 'SELECT * FROM `info` WHERE LOWER(`data`) LIKE LOWER(CONVERT(\''.mysql_real_escape_string($input).'\' USING latin1));'; 


Give that a shot.

hi mate,

thanks for your reply,

unfortunatly i am unable to get this to work (thought i have been up the pub all day :D so that may have something to do with it!

i have changed your code slightly because its the data field which needs to be converted, unless your example converts the entire process? so i have this

PHP Code:

$sql = "SELECT * FROM info WHERE LOWER($input) LIKE LOWER(CONVERT(\''.mysql_real_escape_string(data).'\' USING latin1))"; 


but this throws out a Incorrect parameters in the call to native function 'LOWER'

your original code didnt throw out any errors but did not also find any data in the db

again thanks for your help with this, appreciate it.
Luke

Your edited code is slightly wrong. You are enclosing your SQL query in double quotes, and mine is enclosed in single quotes. You have to do some editing to make it would for double quotes:

PHP Code:

$sql = "SELECT * FROM info WHERE LOWER($input) LIKE LOWER(CONVERT('".mysql_real_escape_string(data)."' USING latin1))"; 


Give that a shot.

the above give the following error

Code:

Notice:  Use of undefined constant data - assumed  'data'
Unknown column 'empty' in 'where clause'

this 'might' be because the $input is the data the user enters so should that not be the one which i use mysql_real_escape_string on?
data is the db column

i tried this

PHP Code:

$sql = "SELECT * FROM info WHERE LOWER('".mysql_real_escape_string($input)."' LIKE LOWER(CONVERT(data) USING latin1))"; 


but that throws up

Code:

You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near ') USING  latin1)' at line 1

cheers mate
Luke