• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

Mysql Database query - sort unique

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
savone
Asked:
savone
  • 2
  • 2
1 Solution
 
dimmergeekCommented:
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
 
Ray PaseurCommented:
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
 
savoneAuthor Commented:
@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
 
savoneAuthor Commented:
I got it to work, turned out the single quotes are the select fields were causing issues.

Thanks!
0
 
dimmergeekCommented:
Thanks!  Sorry I did not check back sooner.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now