I want to get the contents from a row in the Postgresql database and compare the lowercase version of it to a lowercase version of a 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 that make query not working at all.
EDIT
I am trying to implement an autocomplete Jquery UI like here: http://jqueryui.com/demos/autocomplete/#remote for 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
})
})
all above work great.. exactly like in Demo here: http://jqueryui.com/demos/autocomplete/#remote
my problem is that the names in database stored in Uppercase (e.g. LORI) and of course the user prefers to insert a lowercase in search box to search for name (e.g. lori). but since it 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 drop down list! pretty weird!
thanks in advance.
SELECT LOWER(name) FROM user_names
WHERE name ILIKE '%$search%' ORDER BY name ASC