Postgresql lowercase to compare data

Question

I want to get content from a string in a Postgresql database and compare its version with lowercase user input to check if it exists in the database.

I tried:

"SELECT LOWER(name) FROM user_names WHERE name LIKE '%$search%' ORDER BY name ASC" 

but making a request does not work at all.

EDIT

I'm trying to implement jQuery UI autocomplete, like here: http://jqueryui.com/demos/autocomplete/#remote for the search box (for names)

using javascript and php.

PHP code:

 $search = ($_GET['term']); if (!$con) { die('Could not connect: ' . pg_last_error ());} else { $sql = "SELECT name FROM users_table WHERE name LIKE '%$search%' ORDER BY name ASC"; $result = pg_query($sql); $json = '['; $first = true; while ($row = pg_fetch_array($result)) { if (!$first) { $json .= ','; } else { $first = false; } $json .= '{"value":"'.$row['name'].'"}'; } $json .= ']'; echo $json; exit(); } 

JavaScript Code:

  $(document).ready(function() { $('#auto').autocomplete( { source: "./file.php", minLength: 3 }) }) 

everything above works fine .. exactly the same as in the demo here: http://jqueryui.com/demos/autocomplete/#remote

my problem is that the names in the database are stored in uppercase (e.g. LORI) and of course the user prefers to insert lowercase letters in the search field to search for the name (e.g. lori). but since it is stored in uppercase, I need to convert it.

I tried as your suggestion:

 $sql = "SELECT LOWER(name) FROM users_table WHERE name ILIKE '%$search%' ORDER BY name ASC"; 

then i got an empty dropdown list! quite strange!

early.

Answer 1

Google is your friend :

 SELECT LOWER(name) FROM user_names WHERE name ILIKE '%$search%' ORDER BY name ASC