Solved

Mysql Database query - sort unique

Posted on 2013-11-08
5
604 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
  • 2
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
dimmergeek earned 500 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 109

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Fulfillment API php code sample 1 41
Mysql Left Join Case 10 56
Add email address from a web page to outlook 2010 contacts ? 8 28
Creating Functions in phpMyAdmin 8 18
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

803 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