PHP MySqli show result for similar terms (Keyword)

Let me explain fast what i want to do! I want to show similar rows from my database by a PHP term. I have a table called “games” and a column called “title” that titles are looks like “Rockstar – GTA V”. So i want to remove all words after dash and use new string as keyword to search in database.

My CMS use this code to show post title inside the loop:

$_smarty_tpl->tpl_vars['game']->value['title']

I just found a code to convert “Rockstar – GTA V” to “Rockstar”:

 <?php $mygame = strstr($_smarty_tpl->tpl_vars['game']->value['title'], '-', true); echo($mygame); ?>

When i put this code in my “Single template file”, it work fine and trim the title as i want and it work good in every game’s single page.

So i want to make a section in single page to display all games made by that company (i mean that trimmed word from title). I tried some codes and nothing! This is what i tried:

<?php
$connect = mysqli_connect("localhost", "dbname", "dbpass", "dbuser");
$connect->set_charset('utf8mb4');
mysqli_set_charset($link, 'utf8mb4');

$gamecompany = strstr($_smarty_tpl->tpl_vars['game']->value['title'], '-', true);

$query = 'SELECT * FROM games WHERE title = "'.$gamecompany.'" ORDER BY game_id ASC LIMIT 50';
    
$result = mysqli_query($connect, $query);
if(mysqli_num_rows($result) > 0)
{
    $output .= '<div class="list">';
    
    while($row = mysqli_fetch_array($result))
    {
        $output .= '<li class="game"><a href="https://example.com/'.$row["game_id"].'/" target="_blank">'.$row["title"].'</a></li>';
    }
    $output .= '</div>';
    echo $output;
}
else
{
    echo 'Nothing Found';
}
?>

So i used $gamecompany to trim and get a game’s company and use it as a keyword in query. But everytime it just show “Nothing Found“. When i have some games with keyword “Rockstar” in my database But it won’t display that and just pass the conditions statement and can’t show nothing. Tried another keywords (Directly in my code) but won’t work!

And one note: My titles are in “Arabic” language and it should be UTF8. Is this my problem? or just a wrong coding?

Answer

Using LIKE you can find all occurences with ‘Rockstar’, but to be safe, convert it to lower case and remove any extra spaces that might occur. Also, lets protect ourselves from SQL attacks with a prepared statement.

$gamecompany = strtolower(trim(strstr($_smarty_tpl->tpl_vars['game']->value['title'], '-', true))); // put it in lower case, trim any excess white space

$query = 'SELECT * FROM games WHERE LOWER(title) LIKE ? ORDER BY game_id ASC LIMIT 50';
$stmt = $conn->prepare($query);
$value = "%$gamecompany%"; // The % allows us to find any titles that have our search string in them
$stmt->bind_param("s", $value);
$stmt->execute();
$result = $stmt->get_result();

Advertisement

Answer