-1

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
TLP Masih
  • 285
  • 1
  • 2
  • 9
  • 1
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman May 09 '21 at 16:32
  • @dharman Yes my bad! I updated my post by final code. someone helped me to fix the code. can you look at final code and check if it's safe or not? Please <3 – TLP Masih May 09 '21 at 16:50
  • No, please accept and upvote the answer instead. Do not put the solution in the question. I will revert it now, so please do as described here https://stackoverflow.com/help/someone-answers – Dharman May 09 '21 at 16:52
  • If you want confirmation, then yes. The new code is ok, but you need to understand what SQL injections is. Never put variables directly in SQL. This is a very bad way of doing SQLs. The code given in the answer is correct because it binds the data separately. – Dharman May 09 '21 at 16:59

3 Answers3

2

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();
Dharman
  • 30,962
  • 25
  • 85
  • 135
Kinglish
  • 23,358
  • 3
  • 22
  • 43
  • The code won't run at all! The page will stop loading at this code! Can you please edit your answer and put a full code? I think i missed something – TLP Masih May 09 '21 at 16:12
  • @TLPMasih The page should stop loading, but you need to look for the error. Enable error reporting. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) – Dharman May 09 '21 at 16:34
1

For you requirement

title = "'.$gamecompany.'"

is not going to work. You'll need to either use likewise search or full-text search

Likewise

title like '$gamecompany'

Full-Text - For full-text to work, you'll need to have full-text index for that column

MATCH (title) AGAINST (:gamecompany IN NATURAL LANGUAGE MODE)

You can create Full-text index like this

ALTER TABLE games  ADD FULLTEXT(title)
Haridarshan
  • 1,898
  • 1
  • 23
  • 38
0

Try using the LIKE keyword inside the query , and for the Arabic part make sure both the web app and the database uses the same encoding , i once had this problem and when both of them followed the same encode it worked out.

Mohammad Nehme
  • 1