Unable to get search result in mysqli

Question

I'm a new user here and having a problem with my search result. I'm currently build a website with a search for posts feature. It work fine with mysql. And then I read about PHP will remove some of old feature. So i tried to convert from mysql to mysqli but I got lot of error. I don't know what else to do because I've tried lot of suggestions from here and google but it still won't work. There are only 2 fields (title, keywords) in my table (search_table). Result I need is 'title' will appear if user search for any 'keywords'.

My form:

<form name="search_form" action="search.php" method="post">
<input type="text" name="search_posts" placeholder="Search for posts" size="20" maxlength="50"/>
<input type="submit" name="submit" value="Search" />
</form>

My working mysql:

<?php
$host='localhost';
$mysql_username='root';
$mysql_password='';
$database='my_db';
$connection_error='<b>Unable to connect to the database!</b>';
if(!@mysql_connect( $host , $mysql_username , $mysql_password ) || !@mysql_select_db($database))
{
die ($connection_error);
}

$search = mysql_real_escape_string(trim($_POST['search_posts']));
$find_posts = mysql_query("SELECT * FROM `search_table` WHERE `keywords` LIKE '%$search%'") ;
$count = mysql_num_rows($find_posts);
if($count == 0){
    echo 'There are no search result!';
} else {
while($row = mysql_fetch_assoc($find_posts))
{
    $title = $row['title'];
    $keywords = $row['keywords'];
    echo "<a href='#'>$title</a> >>> $keywords<br />";
}
}
?>

Converted mysqli (problem):

<?php
$host = 'localhost';
$mysql_username = 'root';
$mysql_password = '';
$my_database = 'my_db';
$mysqli = new mysqli($host,$mysql_username,$mysql_password,$my_database);
if($mysqli->connect_errno) {
    printf("Connection to database failed: %s\n", $mysqli->connect_error);
    exit(); 
}

$search_posts = $mysqli->real_escape_string(trim($_POST['search_posts']));
$find_posts = $mysqli->query("SELECT * FROM `search_table` WHERE `keywords`='?'") or die($mysqli->error.__LINE__);

if($count == 0) {
echo 'NO SEARCH RESULTS FOUND!';
}
else {
while($row = $find_posts->fetch_assoc())
{
    $title = $row['title'];
    $keywords = $row['keywords'];
    echo "<a href='#'>$title</a> >>> $keywords<br />";
}
}
$mysqli->close();
?>

P/S : Sorry for my bad english. I hope you guys can help me or explain to me in simple way since I'm don't have much knowledge in this field (self taught-try & error stuff ^_^). I will appreciate if you guys have better solution as long as it will work as the old one. Thanks in advance.

Answer 1

When you put the "?" in the query, that's how you do a "prepared statement". But, (a) the mysqli->query function does not do prepared statements, it's does plain old one-shot statements. And (b) even if it did, you never bind a value to the "?". So you are searching for records where keywords equals a literal question mark.

You can do the query just like you did it with old MySQL, building the parameters in as a string:

$find_posts = $mysqli->query("SELECT * FROM `search_table`
  WHERE keywords like '%$search_posts%'");

Or you could do it as a prepared statement:

$pq =$mysqli->prepare("SELECT * FROM `search_table`
  WHERE keywords like ?");
$pq->bind_param('i', '%' . $search_posts . '%');
$pq->execute();
$find_posts=$pq->get_result();

Also note that changing the test from LIKE to = is going to change your results. You probably don't want to do that.