Clash Royale CLAN TAG#URR8PPP

PHP search displaying duplicate results that are nothing like query

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.

forsale

user

2 Answers
2

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.

$query

Try this:

$raw_results = DB::query("SELECT f.* FROM forsale f LEFT JOIN users u ON f.user_id = u.id WHERE f.productname LIKE '%" . $query . "%' OR u.username LIKE '%" . $query ."%'");

$raw_results = DB::query("SELECT f.* FROM forsale f LEFT JOIN users u ON f.user_id = u.id WHERE f.productname LIKE '%" . $query . "%' OR u.username LIKE '%" . $query ."%'");

You need to actually LEFT JOIN them in order for the query to work.

LEFT JOIN

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.