summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorAndreas Unterkircher <unki@netshadow.at>2007-06-13 04:31:46 +0000
committerAndreas Unterkircher <unki@netshadow.at>2007-06-13 04:31:46 +0000
commitd2108af33e8fa5dbddab409e379edfd3c92ea492 (patch)
tree4819386d900273868e8961f55144aaf161975644
parentb4b0a0aa7c7e8a598e296f42a55e96d614abff05 (diff)
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
-rw-r--r--phpfspot.class.php56
1 files 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;
}