Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Mysql Database query - sort unique

Posted on 2013-11-08
5
Medium Priority
?
644 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

578 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