?
Solved

Mysql Database query - sort unique

Posted on 2013-11-08
5
Medium Priority
?
633 Views
Last Modified: 2013-11-12
I am have a database table that has a tags column.  These tags are basically search terms someone can attach to their entries.

I am trying to make a search tag page, where someone can enter multiple tags and then we pull all the entries in the DB with those tags.

This is working well, however, some posts have the multiple of the same tags.

For example:

POST1: I love food TAGS: food love
POST2: For love is just right TAGS: love food

Now if I run a search for "food love" it will return both posts twice.

Here is my code:

$tags = explode(" ", $search);
foreach ($tags as $tag) {
        $query = " SELECT * FROM 2do_todolist WHERE tags like '%$tag%' ";
        $result = mysql_query($query);
        if ( $result ) {
                while ($row = mysql_fetch_assoc($result)) {
                      $dt = $row['d_completed'];
                      $dtd = new DateTime("@$dt");
                      echo "<b>" . $row['title'] . "- Completed on: " . $dtd->format('Y-m-d')  . "</b>" . "<BR>" . nl2br($row['note']);
                      echo "<BR><hr><BR>";
                }
        }

}

Open in new window


Is there a way I can sort it to only show unique results?
0
Comment
Question by:savone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
dimmergeek earned 2000 total points
ID: 39634733
It is generally bad practive to "SELECT *'.  You should really get in the habit of specifying your field names.

As for your question, you will see each record twice because they both contain the words food and love and your script is looping through all search terms.  You are telling it to bring back both records.

A better solution may be to append each sear term to a where clause as below:

$tags = explode(" ", $search);
$whereclause = " tags like ''";
foreach ($tags as $tag) {
        $whereclause .= " OR tags like '%" . $tag . ";
}
$query = " SELECT 'd_completed', 'title', 'note' FROM 2do_todolist WHERE " . $whereclause;
$result = mysql_query($query);
 if ( $result ) {
      while ($row = mysql_fetch_assoc($result)) {
           $dt = $row['d_completed'];
           $dtd = new DateTime("@$dt");
            echo "<b>" . $row['title'] . "- Completed on: " . $dtd->format('Y-m-d')  . "</b>" . "<BR>" . nl2br($row['note']);
            echo "<BR><hr><BR>";
       }
}

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39634878
You can use SELECT DISTINCT or GROUP BY to organize the results set.  But you may have a bigger problem going forward.  PHP is doing away with the MySQL extension and your script must change as a result.  This article explains why and teaches what you must do to keep things running after MySQL is eliminated.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Also, I would guess that the search terms are external data, and as such they must be treated as tainted information and an attack vector.  Please see anti-practice #18 and #19 in this article.  It's important to sanitize external data.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html
0
 
LVL 23

Author Comment

by:savone
ID: 39641593
@dimmergeek, thanks for the help.  I added your code and am receiving the following error:

Parse error: syntax error, unexpected 'SELECT' (T_STRING) in /var/www/html/todo/themes/default/tagsearch.php on line 180

I am posting the code with the line numbers so maybe you can help.

175 $tags = explode(" ", $search);
176 $whereclause = " tags like ''";
177 foreach ($tags as $tag) {
178         $whereclause .= " OR tags like '%" . $tag . ";
179 }
180 $query = " SELECT 'd_completed', 'title', 'note' FROM 2do_todolist WHERE " . $whereclause;
181 $result = mysql_query($query);
182  if ( $result ) {
183       while ($row = mysql_fetch_assoc($result)) {
184            $dt = $row['d_completed'];
185            $dtd = new DateTime("@$dt");
186             echo "<b>" . $row['title'] . "- Completed on: " . $dtd->format('Y-m-d')  . "</b>" . "<BR>" . nl2br($row['note']);
187             echo "<BR><hr><BR>";
188        }
189 }       
190 
191 }
192 ?>

Open in new window

0
 
LVL 23

Author Comment

by:savone
ID: 39641704
I got it to work, turned out the single quotes are the select fields were causing issues.

Thanks!
0
 
LVL 7

Expert Comment

by:dimmergeek
ID: 39642021
Thanks!  Sorry I did not check back sooner.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question