Search function SQL/PHP - wrong output

Question

I have a search function that can search for user, user's motto and user's tags.

If you search for example "Vegard" it shows the user several times because this user has more than one tag (It has five tags, so the result is shown 5 times of the same user with the different tags) I want to only show the specific user with all the tags on that user. The image below shows the result. My outcome should only include one user with all the tags inside that user.

Example: enter image description here

My code

$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 LEFT JOIN user_tags ON users.id = user_tags.user_id
        WHERE (`username` LIKE '%".$query."%') OR (`motto` LIKE '%".$query."%')OR (`tag` LIKE '%".$query."%') ORDER BY account_created DESC") or die(mysql_error());
    $num_rows = mysql_num_rows($raw_results);

    if(mysql_num_rows($raw_results) > 0){
        while($results = mysql_fetch_array($raw_results)){
            $timestamp=$results['account_created'];
            echo '
            <a href="http://prosjektdatabasen.com/index.php?url=prosjekt&username='.htmlspecialchars($results['username']).'"><div class="searchbg">
            <text style="float:right; font-size:12px; color:#2e7297;">' . htmlspecialchars(gmdate("d.m.y", $timestamp)) . '
            </text> 
            <text style="font-size:17px; color:#2b2929; font-weight:bold; line-height:140%;">'.htmlspecialchars($results['username']).'</text><br />

            <text style="color:#353839; line-height:130%; font-size:14px;">' . htmlspecialchars(wordwrap(substr($results['motto'], 0,110), 58 ,"\n", TRUE)) . '...</text>
            <br /><text style="color:#4c6b76; font-size:12px;line-height:150%;">Tags: '.$results['tag'].' </text></div>
            </a>';
        }
    }
}

Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 23 '15 at 18:55

vdwijngaert · Answer 1 · 2015-04-23T19:55:43.920

0

Have you tried grouping it by userid?

$raw_results = mysql_query("SELECT *, GROUP_CONCAT(user_tags.tag) as tags FROM users LEFT JOIN user_tags ON users.id = user_tags.user_id
    WHERE (`username` LIKE '%".$query."%') OR (`motto` LIKE '%".$query."%')OR (`tag` LIKE '%".$query."%') GROUP BY users.id ORDER BY account_created DESC") or die(mysql_error());

Alternatively, try distinct:

$raw_results = mysql_query("SELECT DISTINCT * FROM users LEFT JOIN user_tags ON users.id = user_tags.user_id
    WHERE (`username` LIKE '%".$query."%') OR (`motto` LIKE '%".$query."%')OR (`tag` LIKE '%".$query."%') ORDER BY account_created DESC") or die(mysql_error());

edited Apr 23 '15 at 19:55

answered Apr 23 '15 at 19:51

vdwijngaert

1,515
11
24

1

Throw a `GROUP_CONCAT(tag) as tags` and you can have a csv list of the tags as well. – dmgig Apr 23 '15 at 19:53
Now it shows only one user as requested, but still it just show one tag. All tags for that user should be visual. Tested with group_concat and distinct – Vegard Berg Apr 24 '15 at 09:40

Search function SQL/PHP - wrong output

1 Answers1