Location: PHPKode > scripts > phpFlickrSynch > phpflickrsynch/_classes/class_TableFlickrTag.php
<?php
	class TableFlickrTag {

		public function getTagId($tagName) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT tag_id id FROM flickr_tag WHERE tag_name = ?');
			$st->bindParam(1, $tagName, PDO::PARAM_STR);
			$tab = Helpers::fetchOneRow($st);
			return ($tab == NULL || $tab == false) ? 0 : $tab['id'];
		}

		/**
		  * Check if the tag already exists in the DB
		  * @param $tagID Id to check
		  * @return An array with corresponding tag name
		  */
		public function checkTag($tagId, $groupId = 0) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT tag_id id, tag_name name, tag_nb_image nb FROM flickr_tag WHERE tag_id = ?');
			$st->bindParam(1, $tagId, PDO::PARAM_INT);
			$res = Helpers::fetchOneRow($st);
			if ($res != false) {
				if ($groupId > 0) {
					$st1 = $FLDB->prepare('SELECT COUNT(a.img_id) nb FROM flickr_tag_photo a, flickr_photo b, flickr_portfolio c
WHERE a.img_id = b.img_id AND b.por_id = c.por_id AND tag_id = ? AND grp_id = ?;');
					$st1->bindParam(1, $tagId, PDO::PARAM_INT);
					$st1->bindParam(2, $groupId, PDO::PARAM_INT);
					$tmp = Helpers::fetchOneRow($st1);
					$res['nb'] = $tmp['nb'];
				}
				return $res;
			} else {
				return NULL;
			}
		}
	
		public function registerTags($img_id, $tags) {
			global $FLDB;
			$st = $FLDB->prepare('DELETE FROM flickr_tag_photo WHERE img_id = ?');
			$st->bindParam(1, $img_id, PDO::PARAM_INT);
			Helpers::executeStatement($st);
			
			foreach ($tags as $key => $tag) {				
				$tag_id = $this->registerTag($tag);
				if ($tag_id > 0) {
					$st1 = $FLDB->prepare('INSERT INTO flickr_tag_photo (tag_id, img_id) VALUES (?, ?)');
					$st1->bindParam(1, $tag_id, PDO::PARAM_INT);
					$st1->bindParam(2, $img_id, PDO::PARAM_INT);
					Helpers::executeStatement($st1);
				}
			}			
		}

		private function registerTag($tag) {
			global $FLDB;
			$canonic = $this->getCanonicalKeyword($tag);
						
			$st = $FLDB->prepare('SELECT tag_id FROM flickr_tag WHERE tag_canonic = ?');
			$st->bindParam(1, $canonic, PDO::PARAM_STR);
			$res = Helpers::fetchOneRow($st);
	
			if ($res != false && $res['tag_id'] > 0) {
				return $res['tag_id'];
			} else {	
				$st1 = $FLDB->prepare('INSERT INTO flickr_tag (tag_canonic, tag_name) VALUES (?, ?)');
				$st1->bindParam(1, $canonic, PDO::PARAM_STR);
				$st1->bindParam(2, $tag, PDO::PARAM_STR);
				return Helpers::executeInsertStatement($FLDB, $st1);
					/*
				$st2 = $FLDB->prepare('SELECT LAST_INSERT_ID() tag_id FROM flickr_tag');
				$st2->execute();
				$tab = $st2->fetch(PDO::FETCH_ASSOC);		
				return isset($tab['tag_id']) ? $tab['tag_id'] : 0;*/											
			}
		}

		public function getImageTags($img_id) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT t2.tag_id id, tag_name name FROM flickr_tag t1, flickr_tag_photo t2 WHERE t1.tag_id = t2.tag_id AND img_id = ?');
			$st->bindParam(1, $img_id, PDO::PARAM_INT);
			return Helpers::fetchAll($st);
		}
		
		private function getCanonicalKeyword($keyword) {
			$keyword = strtolower($keyword);
			$keyword = strtr($keyword,
			"\xe1\xc1\xe0\xc0\xe2\xc2\xe4\xc4\xe3\xc3\xe5\xc5".
			"\xaa\xe7\xc7\xe9\xc9\xe8\xc8\xea\xca\xeb\xcb\xed".
			"\xcd\xec\xcc\xee\xce\xef\xcf\xf1\xd1\xf3\xd3\xf2".
			"\xd2\xf4\xd4\xf6\xd6\xf5\xd5\x8\xd8\xba\xf0\xfa".
			"\xda\xf9\xd9\xfb\xdb\xfc\xdc\xfd\xdd\xff\xe6\xc6\xdf",
			"aAaAaAaAaAaAacCeEeEeEeEiIiIiIiInNoOoOoOoOoOoOoouUuUuUuUyYyaAs");	
			$keyword = str_replace("-", " ", $keyword);		
			return $keyword;
		}
		
		public function compute() {
			global $FLDB;
			$st = $FLDB->prepare('SELECT tag_id, COUNT(*) nb FROM flickr_tag_photo GROUP BY tag_id');
			$tab = Helpers::fetchAll($st);
			if ($tab) {
				$st1 = $FLDB->prepare('UPDATE flickr_tag SET tag_nb_image=? WHERE tag_id=?');
				foreach ($tab as $key => $val) {
					$st1->bindParam(1, $val['nb'], PDO::PARAM_INT);
					$st1->bindParam(2, $val['tag_id'], PDO::PARAM_INT);
					Helpers::executeStatement($st1);
				}
			}
		}

		/**
		  * Computes a tag cloud
		  * @param $pmin Minimum size of tag
		  * @param $pmax Maximum size of tag
		  * @param $order 1 means sorting by tag name ; 2 means sorting by popularity
		  * @param $limit Number of tags in the tag cloud ; default: 100		  
		  * @param $groupID Group ID - only returns tags with this group ID ; default: 0 means all		  		  
		  */
		public function getTagCloud($pmin, $pmax, $order=1, $limit=100, $groupId = 0) {
			global $FLDB;
			
			if ($groupId > 0) {
				$sql = 'SELECT DISTINCT(d.tag_id) id, tag_name name, tag_nb_image nb FROM flickr_tag d, flickr_tag_photo a, flickr_photo b, flickr_portfolio c WHERE a.img_id = b.img_id AND b.por_id = c.por_id AND c.grp_id = ? AND d.tag_id = a.tag_id AND tag_nb_image > 0';
			} else {
				$sql = 'SELECT tag_id id, tag_name name, tag_nb_image nb FROM flickr_tag WHERE tag_nb_image > 0';
			}
			
			if ($order == 2) {
				$sql .= ' ORDER BY tag_nb_image DESC';
			} else {
				$sql .= ' ORDER BY tag_name';			
			}
			$sql .= ' LIMIT ' . $limit;			
						
			$st = $FLDB->prepare($sql);
			if ($groupId > 0) {
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
			}			
			$tab = Helpers::fetchAll($st);
			
			$tags = array();			
			if ($tab) {
				$vmin = -1;
				$vmax = 0;
				foreach ($tab as $key => $val) {
					if ($val['nb'] > $vmax) $vmax = $val['nb'];
					if ($vmin == -1) $vmin = $val['nb'];
					else if ($val['nb'] < $vmin) $vmin = $val['nb'];				
				}				
				foreach ($tab as $key => $val) {
					if ($val['nb'] == $vmin) {
						$value = $pmin;
					} else {				
						$value = round(($pmax - $pmin) * (log($val['nb'] - $vmin)) / (log($vmax - $vmin)) + $pmin);
					}

					$tags[] = array(
						'value' => $value,
						'name' => $val['name'],
						'id' => $val['id']
					);
				}										
			}			
			return $tags;
		}		

		/**
		  * Returns the total number of tags
		  * @param $groupID Group ID - only returns tags  with this group ID ; default: 0 means all		  		  
		  */
		public function getNumberOfTags($groupId = 0) {
			global $FLDB;
			if ($groupId > 0) {
				$st = $FLDB->prepare('SELECT COUNT(DISTINCT(tag_id)) nb FROM flickr_tag_photo a, flickr_photo b, flickr_portfolio c WHERE a.img_id = b.img_id AND b.por_id = c.por_id AND c.grp_id = ?');
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
			} else {
				$st = $FLDB->prepare('SELECT COUNT(*) nb FROM flickr_tag');
			}
			
			$res = Helpers::fetchOneRow($st);		
			return ($res != false && isset($res['nb'])) ? $res['nb'] : 0;
		}		
	}
?>
Return current item: phpFlickrSynch