时间戳

首页 > 解决方案 > autocomplete results show up but cant find uppercase letters

php - autocomplete results show up but cant find uppercase letters

问题描述

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>

标签： phpjquerymysqluppercase

解决方案

MySQLLIKE通常不区分大小写，除非您使用排序规则（例如二进制），但情况并非如此。假设，无论出于何种原因，您的排序规则导致LIKE区分大小写，这是您可以用一种方式来表达您的查询以按照您想要的方式运行：

$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();

请注意，我正在切换到使用带有 的准备好的语句mysqli，这可以避免当前脚本容易发生的 SQL 注入之类的事情。

推荐阅读