Solved

Mysql Database query - sort unique

Posted on 2013-11-08
5
620 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 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 110

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

739 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