Wrong output with search function SQL

Question

I have tried to connect a metadata tag to a user, I got the relationship between them but when I search the tag I get a large output. I have a tag named Chips, but the output is 17 different users. None of them have nothing todo with the word or the tag.

To clearify what I'm trying todo: I'm making a search function that can search for username, motto and tag. http://gyazo.com/d334447761152394ca1eecc392c35e92

<?php

    $varr = preg_replace("/%u([0-9a-f]{3,4})/i","&#x\\1;",urldecode($url)); 
    $varr = html_entity_decode($url,null,'UTF-8');

    $query = $_GET['query']; 
    $min_length = 2;

    if(strlen($query) >= $min_length){ 

    $query = htmlspecialchars($query); 
    $query = mysql_real_escape_string($query);

    $raw_results = mysql_query("SELECT * FROM users, user_tags
                WHERE (`username` LIKE '%".$query."%') OR (`tag` LIKE '%".$query."%') OR (`motto` LIKE '%".$query."%')") or die(mysql_error());
    $num_rows = mysql_num_rows($raw_results);
?>

There must be a matching word `Chips` thats why results are returned :P — Umair Ayub, Apr 22 '15 at 10:33
They have a connection because of the user_id. I thought it wouldnt return anything if the word didnt match. It should only match the tag, because none of the users are named ch, chi, chip, chips in their name. — Vegard Berg, Apr 22 '15 at 10:36
Please be aware that the mysql_ functions are now no longer just discouraged (as it was over the last years), but officially [deprecated](http://php.net/manual/en/migration55.deprecated.php). You should really use [MySQLi](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/ref.pdo-mysql.php), as this code will stop working very soon. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for more information. — Oldskool, Apr 22 '15 at 10:39

score 2 · Accepted Answer · answered Apr 22 '15 at 10:38

This is your query:

SELECT *
FROM users, user_tags
WHERE . . .

The problem has nothing to do with your WHERE clause. It is the comma in the FROM. Simple rule: Never use commas in the FROM clause. Always use explicit JOIN syntax.

The correct syntax looks more like this (but depends on your column names):

SELECT *
FROM users u JOIN
     user_tags ut
     ON u.userId = ut.userId
WHERE . . .

Wrong output with search function SQL

1 Answers1