0

I am trying to make an accent insensitive search using collate. But it doesn't work. Furthermore, I heard that my code below might not be optimal if my database becomes really large. Thus, any ideas on how to make this code efficient even for large databases is highly appreciated as well. 

<?php
$search_FirstName="%".$_POST['First_Name']."%";
$search_LastName="%".$_POST['Last_Name']."%";
$search_Email="%".$_POST['Email']."%";

$stmt = $con->prepare('SELECT * FROM persons WHERE FirstName LIKE ? collate utf8_general_ci AND LastName LIKE ? collate utf8_general_ci AND Email LIKE ? collate utf8_general_ci');
$stmt->execute([$search_FirstName,$search_LastName,$search_Email]);
?>
user5095266
  • 119
  • 2
  • 7
  • Your code looks fine. Can you give examples that don't work? `collate` may not map every character to the one you expect (but it should work for characters like `ü`, `ź` or `é`). Performance: `collate` will usually prevent mysql from using indexes, so you have to use a column with the correct collation to use indexes (you can do this by e.g. add a column with a utf8-copy of your data, you can do this e.g. in a trigger or as a generated column). – Solarflare Jun 12 '16 at 09:46
  • While probably not directly effecting you on this instance, it is worth you reading [This SO Q&A about UTF8](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) and seeing that the basic UTF8 used in MySQL is pretty fundamentally flawed and should be replaced with prejudice by `utf8mb4_` character set collations. – Martin Jun 13 '16 at 22:50

1 Answers1

0

I think that you can directly change the collation of your database to latin1_swedish_ci.

And after you could directly write your request like that : $stmt = $con->prepare('SELECT * FROM persons WHERE FirstName LIKE ? AND LastName LIKE ? AND Email LIKE ?');

gfitas
  • 45
  • 7