From d2108af33e8fa5dbddab409e379edfd3c92ea492 Mon Sep 17 00:00:00 2001
From: Andreas Unterkircher <unki@netshadow.at>
Date: Wed, 13 Jun 2007 04:31:46 +0000
Subject: [PATCH] more sophisticated way to query all images from the selected
 tag in an AND condition

git-svn-id: file:///var/lib/svn/phpfspot/trunk@89 fa6a889d-dae6-447d-9e79-4ba9a3039384
---
 phpfspot.class.php | 56 ++++++++++++++++++++++++----------------------
 1 file changed, 29 insertions(+), 27 deletions(-)

diff --git a/phpfspot.class.php b/phpfspot.class.php
index 0dea249..e3a1ba6 100644
--- a/phpfspot.class.php
+++ b/phpfspot.class.php
@@ -183,7 +183,7 @@ class PHPFSPOT {
       if(isset($_SESSION['selected_tags'])) {
          $key = array_search($tag, $_SESSION['selected_tags']);
          unset($_SESSION['selected_tags'][$key]);
-	 sort($_SESSION['selected_tags']);
+         sort($_SESSION['selected_tags']);
       }
 
    } // delTag()
@@ -232,38 +232,40 @@ class PHPFSPOT {
                WHERE pt.tag_id IN (". $selected .")
                ORDER BY p.time ASC
             ");
-            while($row = $this->db->db_fetch_object($result)) {
-               array_push($tagged_photos, $row['photo_id']);
-            }
          }
          elseif($_SESSION['tag_condition'] == 'and') {
-            $result = $this->db->db_query("
-               SELECT DISTINCT photo_id, tag_id
-                  FROM photo_tags pt
-               INNER JOIN photos p
-                  on p.id=pt.photo_id
-               WHERE pt.tag_id IN (". $selected .")
-               ORDER BY p.time ASC
-            ");
 
-            /* now we need to check if each object fulfills the condition
-               and has all the selected tags assigned
+            /* Join together a table looking like
+
+               pt1.photo_id pt1.tag_id pt2.photo_id pt2.tag_id ...
+
+               so the query can quickly return all images matching the
+               selected tags in an AND condition
+
             */
-            $match_object = Array();
-            $matches_needed = count($_SESSION['selected_tags']);
-            while($row = $this->db->db_fetch_object($result)) {
-               /* set the counter for this object */
-               if(!isset($match_object[$row['photo_id']]))
-                  $match_object[$row['photo_id']] = $matches_needed;
-               
-               /* we have a match? decrement the counter */
-               if(in_array($row['tag_id'], $_SESSION['selected_tags']))
-                  $match_object[$row['photo_id']]--;
 
-               /* if the object has all necessary tags, add it to the result */
-               if($match_object[$row['photo_id']] == 0)
-                  array_push($tagged_photos, $row['photo_id']);
+            $query_str = "
+               SELECT DISTINCT pt1.photo_id
+                  FROM photo_tags pt1
+            ";
+
+            for($i = 0; $i < count($_SESSION['selected_tags']); $i++) {
+               $query_str.= "
+                  INNER JOIN photo_tags pt". ($i+2) ."
+                     ON pt1.photo_id=pt". ($i+2) .".photo_id
+               ";
+            }
+            $query_str.= "WHERE pt1.tag_id=". $_SESSION['selected_tags'][0];
+            for($i = 1; $i < count($_SESSION['selected_tags']); $i++) {
+               $query_str.= "
+                  AND pt". ($i+1) .".tag_id=". $_SESSION['selected_tags'][$i] ."
+               "; 
             }
+            $result = $this->db->db_query($query_str);
+         }
+
+         while($row = $this->db->db_fetch_object($result)) {
+            array_push($tagged_photos, $row['photo_id']);
          }
          return $tagged_photos;
       }
-- 
2.25.1