PHP搜索显示的重复结果与查询完全不同

Question

So I'm just trying to create a basic search bar to increase my php and mysql knowledge but I am seriously confused.

I have followed a tutorial and played around with the code myself but I am at a loss the search query kind of works it will display results but the results displayed are nothing like the search query it is displaying many duplicate results.

I have 2 different people with 2 different products linking to them and this is what it is showing.

And here is the query that I am searching.

and here is my code and I'm hoping someone with more knowledge might be able to help out, hoping its not just a simple mistake that I've made :O

<?php
$query = $_GET['q'];
// gets value sent over search form

$min_length = 3;
// you can set minimum length of the query if you want

if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

    $query = htmlspecialchars($query);
    // changes characters used in html to their equivalents, for example: < to &gt;

    $query = mysql_real_escape_string($query);
    // makes sure nobody uses SQL injection

    $raw_results = DB::query("SELECT  * FROM forsale, users WHERE forsale.productname LIKE '%" . $query .  "%' OR users.username LIKE '%" . $query ."%'");

    if (count($raw_results) > 0) {
      foreach($raw_results as $results) {

        echo $results['productname'];
        echo $results['username'];
        echo $results['price'];

      }
    }else{ // if there is no matching rows do following
        echo "No results";
      }
}else{ // if query length is less than minimum
  echo "Minimum length is ".$min_length;
}

?>

Thank you very much in advance.

Answer 1

As a quick fix to check if it works, you need to link the two tables together using the user_id...

$raw_results = DB::query("SELECT  * 
                           FROM forsale, users 
                           WHERE forsale.user_id = users.user_id
                               and (forsale.productname LIKE '%" . $query .  "%' 
                                 OR users.username LIKE '%" . $query ."%'"));

I would like to recommend moving to the newer JOIN format (https://dev.mysql.com/doc/refman/8.0/en/join.html) as well as looking into using prepared statements and bind variables.