autocomplete results show up but cant find uppercase letters

Question

The autocomplete is almost working as it should, except for a problem with words that start with an Uppercase.

for example the word 'Brussels':

I will be able to find it when I start typing in the searchbox 'russels', but 'Bru...' will not be found.

looking for words starting with a lowercase is not a problem, 'brussels' will show up once i start typing 'bru'.

Also words like New York will not show up when i start tying 'York', but will when i type 'ork'.

Search.php file

<?php
$dbServername = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName= "vlucht";

$conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName);

mysqli_set_charset($conn, 'utf8');

$id = $_GET['q'];
$sql = "SELECT discipline FROM overzicht where discipline like '%".$id."%'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
 while($row = $result->fetch_assoc()) {
   echo $row["discipline"]. "\n";
 }
} else {
 echo "0 results";
}
$conn->close();
?>

index.php file:

<html>
<head>
 <link rel="stylesheet" type="text/css" href="style.css" />
 <script type="text/javascript" src="jquery-1.4.2.min.js"></script>
 <script type="text/javascript" src="jquery.autocomplete.js"></script>
 <script>
 jQuery(function(){
 $("#search").autocomplete("search.php");
 });
 </script>
</head>
<body>

 Discipline : <input type="text" name="q" id="search" placeholder="Geef je discipline in">


</body>
</html>

Aside: you're wide open to [SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) attacks. — CD001, Aug 19 '19 at 15:55

Tim Biegeleisen · Answer 1 · 2019-08-19T16:09:21.413

1

MySQL's LIKE is usually case insensitive, unless you are using a collation (e.g. binary) where that would not be the case. Assuming that, for whatever reason, your collation is causing LIKE to be case sensitive, here is one way you may phrase your query to behave the way you want:

$id = $_GET['q'];
$disc = "%" . strtolower($id) . "%";
$sql = "SELECT discipline FROM overzicht WHERE LOWER(discipline) LIKE ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("s", $disc);
$stmt->execute();

Note that I am switching to using prepared statements with mysqli, which avoid things like SQL injection, to which your current script is prone.

edited Aug 19 '19 at 16:09

answered Aug 19 '19 at 15:59

Tim Biegeleisen

502,043
27
286
360

@Dharman I reworked my answer so that I am now directly binding a PHP string variable to the statement. All of the tutorials I read on mysqli statements are doing this. – Tim Biegeleisen Aug 19 '19 at 16:10

autocomplete results show up but cant find uppercase letters

1 Answers1