First of all my Search query value is from a user's input. I have tried all the answers on this one and all the results Google have given me, 90% of the answers says put '%''%' and the other 10% says a more complicated answers.
For some reason all of those did not work for me.
How ever I remembered that in MySQL (phpmyadmin) there is this built in search function so I tried it just to see how MySQL handles a search with an apostrophe, turns out MySQL just escaping apostrophe with a backslash LIKE '%\'%'
so why just I replace apostrophe with a \'
in every user's query.
This is what I come up with:
if(!empty($user_search)) {
$r_user_search = str_ireplace("'","\'","$user_search");
$find_it = "SELECT * FROM table WHERE column LIKE '%$r_user_search%'";
$results = $pdo->prepare($find_it);
$results->execute();
This solves my problem. Also please correct me if this is still has security issues.