How to search values from database using ā, è

Question

I want search ā, è.. this special characters and show in page, but when i search the value its converting into different characters. am getting 'nor result found

my html:

<form action="" method="GET">
  <input type="text" name="name" id="name">
  <input type="submit" name="submit">
</form>

this is my php code

    <?php
$conn = mysqli_connect("localhost","root","","test2");

// Check connection
if (mysqli_connect_errno()){
  echo "Failed to connect
   to MySQL: " . mysqli_connect_error();
}

if(isset($_GET['submit'])){
  $ksl = $_GET['name'];
  $sql = "SELECT * FROM app WHERE texts LIKE '%$ksl%' ";

  if($result = mysqli_query($conn, $sql)){
      if(mysqli_num_rows($result) > 0){
          while($row = mysqli_fetch_array($result)){
            echo $row['texts'];
          }
      } else{
          echo "No records matching your query were found.";
      }
  } else{
      echo "ERROR: Could not able to execute $sql. " . mysqli_error($conn);
  }

  // Close connection
  mysqli_close($conn);

}


?>

my js code

<script type="text/javascript">
  $(document).ready(function() {
    $('#name').keyup(function(){
      var gk = $(this).val();

      $.ajax({
        type: 'GET',
        url: 'test.php',
        data: {
          'submit': true,
          'name' : gk,
        },
        success: function (data) {
            $('#testa').html(data);
        }
      });
    })
  });
</script>

how to use special characters in search?

Answer 1

As far as I'm aware you can't do this in mysql. Some workarounds of this use HEX values instead to search for these accents, a work around that I have used ages ago, which is not an ideal way but it did work is to do something like this

SELECT * FROM `app` WHERE `text` REGEXP '(s|š|Š|[sšŠ])(i|í|Í|[iíÍ])(r|ŕ|Ŕ| 
.   ř|Ř|[rŕŔřŘ])(e|é|É|ě|Ě|[eéÉěĚ])(n|ň|Ň|[nňŇ])(A|a|á|Á|ä|Ä|0|[AaáÁäÄ0])'

Or from doing some more research I found a way to use something like this

SELECT whateverYouWant
FROM app 
WHERE text <> CONVERT(columnToCheck USING ASCII)

The CONVERT(col USING charset) function will turns the unconvertable characters into replacement characters. Then, the converted and unconverted text will be unequal.

Take a look at this link to find more information about this: https://dev.mysql.com/doc/refman/5.7/en/charset-repertoire.html