Php mysql code for search text with apostrophe sign

If you are facing issue with a small problem of apostrophe sign, I will explain you, How to solve apostrophe sign issue in php mysql search string.

If you are using below mysql query

INSERT INTO `table_name` (`col1`) VALUES ('abc's site');

This is wrong way for writing mysql query. This is not valid statement. Because mysql search engine denied single quote ('), double quote ("), backslash (\) and NULL (the NULL byte) from mysql string.

Best solution for this issue is escaping characters.

For more see below details.

For escaping special characters, Add backslashes to the user define characters in a string:

For example:

<?php

$string = "Who's American President?";

echo $string . " (This string is not safe for database query.)<br>";

echo addslashes($string) . " (This string is safe for database query.)";

Result:

Who's American President? (This string is not safe for database query.)

Who\'s American President? (This string is safe for database query.)

?>

Also I will recommend you, Always use escape string function when adding user provided data into the Database.

See below query for escaping apostrophe sign

Example-1: (Insert query in Mysql)

$result = mysqli_query($conn, "INSERT INTO `table_name` (`col1`) VALUES ('".mysqli_real_escape_string($conn, $_POST['txt'])."') ") or die(mysql_error());

Example-2: (Search query in Mysql)

$getRecords = mysqli_query($conn, "SELECT * FROM `table_name` WHERE `col1` ='".mysqli_real_escape_string($conn, $searchKeywords)."'  ");