Location: PHPKode > scripts > phpFlickrSynch > phpflickrsynch/_classes/class_TableFlickrPhoto.php
<?php
	/**
	  * Access layer to the "flickr_photo" table, which contains the description of each photo
	  */
	class TableFlickrPhoto {

		/**
		  * Check if the Flickr photo has already been registered in the "flickr_photo" table
		  * @param $flickrid FlickrID of the photo to check
		  * @return the internal ID of the existing photo ($img_id) ; 0 if not existing photo found
		  */
		public function photoAlreadyExists($flickrid) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT img_id FROM flickr_photo WHERE flickr_id = ?');
			$st->bindParam(1, $flickrid, PDO::PARAM_STR, 50);	
			$res = Helpers::fetchOneRow($st);	
			return ($res != false && isset($res['img_id'])) ? $res['img_id'] : 0;
		}

		/**
		  * Check if the photo already exists in the DB
		  * @param $imgID Id to check
		  * @return An array with corresponding FlickrID and Photo album ID
		  */
		public function checkImage($imgId) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT flickr_id, por_id FROM flickr_photo WHERE img_id = ?');
			$st->bindParam(1, $imgId, PDO::PARAM_INT);
			$res = Helpers::fetchOneRow($st);
			if ($res != false) {
				return array(
					'0' => $res['flickr_id'],
					'1' => $res['por_id']);		
			} else {
				return NULL;
			}
		}
		
		/**
		  * Get all the details of a photo
		  * @param $img_id Photo album ID
		  * @return An array of all the properties of the album
		  */
		public function getDetails($img_id) {
			global $FLDB;
			$st = $FLDB->prepare('SELECT flickr_woeid woeid, flickr_lat lat, flickr_lon lon, flickr_title title, img_model model, img_exposure exposure, img_aperture aperture, img_iso_speed iso_speed, img_datetime datetime, img_focal_length focal_length, img_desc description, img_secret secret, img_server server, img_farm farm, img_originalsecret originalsecret, img_originalformat originalformat, img_size_thumbnail size_thumbnail, img_size_small size_small, img_size_medium size_medium, img_size_original size_original, flickr_url FROM flickr_photo WHERE img_id = ?');
			$st->bindParam(1, $img_id, PDO::PARAM_INT);		
			return Helpers::fetchOneRow($st);
		}		
			
		/**
		  * Insert a new record in the "flickr_photo" (mapping between Flickr photo and own photo number)
		  * @param $flickrid FlickrID of the photo to register
		  * @param $por_id The photo album the photo needs to be registered into
		  * @return the internal ID of the newly created photo_album ($img_id) ; 0 if the insertion failed		  
		  */
		public function insertPhoto($flickrid, $por_id) {			
			global $FLDB;
			$st = $FLDB->prepare('INSERT INTO flickr_photo (flickr_id, por_id) VALUES (?, ?)');
			$st->bindParam(1, $flickrid, PDO::PARAM_STR);
			$st->bindParam(2, $por_id, PDO::PARAM_INT);			
			return Helpers::executeInsertStatement($FLDB, $st);
		}	
		
		/**
		  * Helper function to read exif values
		  */
		private function getExifValue($exifs, $value) {
			if (isset($exifs[$value])) { return $exifs[$value];} else { return NULL; }
		}
		
		/**
		  * Update photo by setting all its features
		  * @param $img_id ID of the image to update
		  * @param $title Title of the photo
		  * @param $desc Description of the photo
		  * @param $lon Longitude
		  * @param $lat Latitude
		  * @param $woeid WhereOnEarthID
		  * @param $urlinfo 
		  * @param $exifs
		  * @param $sizes
		  * @return true/false if the update was successful
		  */
		public function updatePhoto($img_id, $title, $desc, $lon, $lat, $woeid, $urlinfo, $exifs = array(), $sizes = array()) {
			global $FLDB;
			$st = $FLDB->prepare('UPDATE flickr_photo SET img_lastupdate=NOW(), flickr_lat=?, flickr_lon=?, flickr_title=?, flickr_woeid=?, img_model=?, img_exposure=?, img_aperture=?, img_iso_speed=?, img_datetime=?, img_focal_length=?, img_desc=?, img_secret=?, img_server=?, img_farm=?, img_originalsecret=?, img_originalformat=?, img_size_thumbnail=?, img_size_small=?, img_size_medium=?, img_size_original=?, flickr_url=? WHERE img_id=?');
									
			$lat = (strlen($lat) == 0) ? NULL :  $lat;
			$lon = (strlen($lon) == 0) ? NULL :  $lon;
		
			$st->bindParam(1, $lat, PDO::PARAM_STR);
			$st->bindParam(2, $lon, PDO::PARAM_STR);
			$st->bindParam(3, $title, PDO::PARAM_STR, 60);
			$st->bindParam(4, $woeid, PDO::PARAM_INT);

			$st->bindParam(5, $this->getExifValue($exifs, 'Model'), PDO::PARAM_STR, 20);
			$st->bindParam(6, $this->getExifValue($exifs, 'Exposure'), PDO::PARAM_STR, 20);
			$st->bindParam(7, $this->getExifValue($exifs, 'Aperture'), PDO::PARAM_STR, 20);
			$st->bindParam(8, $this->getExifValue($exifs, 'ISO Speed'), PDO::PARAM_STR, 20);
			$st->bindParam(9, $this->getExifValue($exifs, 'Date and Time (Original)'), PDO::PARAM_STR, 20);
			$st->bindParam(10, $this->getExifValue($exifs, 'Focal Length'), PDO::PARAM_STR, 20);
			$st->bindParam(11, $desc, PDO::PARAM_STR);
	
			$st->bindParam(12, $urlinfo['secret'], PDO::PARAM_STR);
			$st->bindParam(13, $urlinfo['server'], PDO::PARAM_STR);
			$st->bindParam(14, $urlinfo['farm'], PDO::PARAM_INT);
			$st->bindParam(15, $urlinfo['originalsecret'], PDO::PARAM_STR);
			$st->bindParam(16, $urlinfo['originalformat'], PDO::PARAM_STR);
			
			$st->bindParam(17, $sizes['Thumbnail'], PDO::PARAM_STR);
			$st->bindParam(18, $sizes['Small'], PDO::PARAM_STR); 
			$st->bindParam(19, $sizes['Medium'], PDO::PARAM_STR); 
			$st->bindParam(20, $sizes['Original'], PDO::PARAM_STR);
			
			$st->bindParam(21, $urlinfo['flickr_url'], PDO::PARAM_STR);
			
			$st->bindParam(22, $img_id, PDO::PARAM_INT);
			return Helpers::executeStatement($st);
		}
		
		/**
		  * In order to build a navigation for images, retrieves the previous and next image for the given one (based on internal IMG_ID)
		  * @param por_id Photo album ID the image belongs to
		  * @param img_id The photo
		  * @return Array with 0 index containing the photo ID of the previous one ; 1 index the ID of the next ones
		  */
		public function getPreviousNextOnes($por_id, $img_id) {		
			global $FLDB;
			$st = $FLDB->prepare('SELECT img_id FROM flickr_photo WHERE por_id = ? ORDER BY img_id');
			$st->bindParam(1, $por_id, PDO::PARAM_INT);
			$tab = Helpers::fetchAll($st);

			$previous = 0;
			$next = 0;
			foreach ($tab as $key => $val) {
				if ($val['img_id'] == $img_id) {
					$previous = isset($tab[$key - 1]) ? $tab[$key - 1]['img_id'] : '0';
					$next = isset($tab[$key + 1]) ? $tab[$key + 1]['img_id'] : '0';
				}
			}
			return array( '0' => $previous, '1' => $next );
		}
				
		/**
		  * Retrieve a list of photos taken nearby a given location
		  * @param $woeid WhereOnEarth ID
		  * @param $limit Number of photos to be returned ; default is 10
		  * @param @size Size of the photos to be returned (Thumbnail, Medium...)
		  * @return The collection of photos as an array
		  */
		public function getNearbyPhotos($woeid, $limit = 10, $size = 'Thumbnail') {
			global $FLDB;
			$st = $FLDB->prepare($this->getSQLstatementToRetrieveListOfPhoto() . 'WHERE a.flickr_woeid=? LIMIT ' . $limit);
			$st->bindParam(1, $woeid, PDO::PARAM_INT);
			return $this->_formatResultSet(Helpers::fetchAll($st), $size);
		}

		/**
		  * Retrieve a list of photos from the given photo album
		  * @param $por_id Photo album ID
		  * @param $limit Number of photos to be returned ; default is 3
		  * @param @size Size of the photos to be returned (Thumbnail, Medium...)
		  * @return The collection of photos as an array
		  */		
		public function getPhotos($por_id, $limit = 3, $size = 'Thumbnail') {
			global $FLDB;
			$st = $FLDB->prepare($this->getSQLstatementToRetrieveListOfPhoto() . 'WHERE a.por_id=? ORDER BY a.img_id LIMIT ' . $limit);
			$st->bindParam(1, $por_id, PDO::PARAM_INT);	
			return $this->_formatResultSet(Helpers::fetchAll($st), $size);		
		}

		/**
		  * Retrieve a list of photos containing geo data (latitude / longitude) from the given photo album
		  * @param $por_id Photo album ID
		  * @param $limit Number of photos to be returned ; default is 3
		  * @param @size Size of the photos to be returned (Thumbnail, Medium...)
		  * @return The collection of photos as an array
		  */			
		public function getPhotosWithGeoData($por_id, $limit = 3, $size = 'Thumbnail') {
			global $FLDB;
			$st = $FLDB->prepare($this->getSQLstatementToRetrieveListOfPhoto() . 'WHERE a.flickr_lat IS NOT NULL AND a.por_id = ? LIMIT ' . $limit);
			$st->bindParam(1, $por_id, PDO::PARAM_INT);
			return $this->_formatResultSet(Helpers::fetchAll($st), $size);	
		}		

		/**
		  * Retrieve a list of photos tagged with a given tag
		  * @param $tag_id Tag ID
		  * @param $limit Number of photos to be returned ; default is 3
		  * @param $size Size of the photos to be returned (Thumbnail, Medium...)
		  */
		public function getPhotosTaggedWith($tag_id, $limit = 3, $size = 'Thumbnail', $groupId = 0) {
			global $FLDB;
			
			$sql = $this->getSQLstatementToRetrieveListOfPhoto();
			if ($groupId > 0) {
				$sql .= ', flickr_tag_photo b, flickr_portfolio c WHERE a.img_id = b.img_id AND a.por_id = c.por_id AND tag_id=? AND grp_id=? ORDER BY a.img_id DESC LIMIT ' . $limit;
				$st = $FLDB->prepare($sql);
				$st->bindParam(1, $tag_id, PDO::PARAM_INT);	
				$st->bindParam(2, $groupId, PDO::PARAM_INT);	
			} else {
				$sql .= ', flickr_tag_photo b WHERE a.img_id = b.img_id AND tag_id=? LIMIT ' . $limit;
				$st = $FLDB->prepare($sql);
				$st->bindParam(1, $tag_id, PDO::PARAM_INT);	
			}
						
			return $this->_formatResultSet(Helpers::fetchAll($st), $size);	
		}
		
		/**
		  * Retrieve a list of photos by their IDs
		  * @param $aImagesId Could be an array or a number. Array: array of image IDs. Number: a given image IDs
		  * @param $lastId Not used if $aImagesId is an array. If $aImagesId is a number, $lastId must be > $aImagesId - the series of images will be from $aImagesId to $lastId
		  * @param @size Size of the photos to be returned (Thumbnail, Medium...)
		  */		  
		public function getPhotosByIds($aImagesId, $lastId = 0, $size = 'Thumbnail') {
			global $FLDB;
			$sImagesId = '0';
			if (is_array($aImagesId)) {
				foreach ($aImagesId as $key => $val) $sImagesId .= ',' . $val;		
			} else {
				for ($i = $aImagesId; $i <= $lastId; $i++) $sImagesId .= ',' . $i;		
			}
 			
			$st = $FLDB->prepare($this->getSQLstatementToRetrieveListOfPhoto() . ' WHERE a.img_id IN (' . $sImagesId . ')');
			return $this->_formatResultSet(Helpers::fetchAll($st), $size);	 
		}
		
		public function search($searchterm, $limit = 3, $size = 'Thumbnail', $groupId = 0) {
			global $FLDB;
			if ($groupId > 0) {
				$sql = $this->getSQLstatementToRetrieveListOfPhoto() . ', flickr_portfolio c WHERE a.por_id = c.por_id AND grp_id=? ';
				$sql .= 'AND MATCH (a.flickr_title) AGAINST (?  IN BOOLEAN MODE) LIMIT ' . $limit;

				$st = $FLDB->prepare($sql);
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
				$st->bindParam(2, Helpers::getSearchString($searchterm), PDO::PARAM_STR);	
			} else {
				$st = $FLDB->prepare($this->getSQLstatementToRetrieveListOfPhoto() . ' WHERE MATCH (a.flickr_title) AGAINST (?  IN BOOLEAN MODE) LIMIT ' . $limit);
				$st->bindParam(1, Helpers::getSearchString($searchterm), PDO::PARAM_STR);			
			}				
			return $this->_formatResultSet(Helpers::fetchAll($st), $size);
		}
		
		/**
		  * Format resultset when retrieving list of photos from the DB.
		  * In particular, build Flickr URL from data available in the DB.
		  */
		private function _formatResultSet($tab, $size) {
			$res = array();	
			$F = new FlickrUtils();	
			$field = $F->getSizeField($size);
				
			if ($tab) {
				foreach ($tab as $key => $val) {
					$farm = $val['farm'];
					$server = $val['server'];
					$id = $val['flickr_id'];
					$flickrurl = $val['flickr_url'];
					$secret = $val['secret'];
					$originalsecret = $val['originalsecret'];
					$originalformat = $val['originalformat'];
					$sizefield = $val[$field];
					
					$photodetails = $F->buildPhotoURL ($farm, $server, $id, $secret, $originalsecret, $originalformat, $size, $sizefield);
					$photodetailsOriginal = $F->buildPhotoURL ($farm, $server, $id, $secret, $originalsecret, $originalformat, 'Original', $sizefield);
					$res[$key] = array(
						'flickrUrl' => $flickrurl,			
						'url' => $photodetails['source'],
						'originalUrl' => $photodetailsOriginal['source'],
						'title' => $val['title'], 
						'id' => $val['id'],
						'lat' => $val['lat'],
						'lon' => $val['lon'],
						'width' => $photodetails['width'],
						'height' => $photodetails['height']
					);												
				}
			}
			return $res;		
		}
		
		/**
		  * SQL SELECT statement used to retrieve list of photos from the DB
		  */
		private function getSQLstatementToRetrieveListOfPhoto() {
			return 'SELECT a.img_id id, a.flickr_url, a.flickr_id, a.flickr_title title, a.flickr_lat lat, a.flickr_lon lon, a.img_secret secret, a.img_server server, a.img_farm farm, a.img_originalsecret originalsecret, a.img_originalformat originalformat, a.img_size_thumbnail size_thumbnail, a.img_size_small size_small, a.img_size_medium size_medium, a.img_size_original size_original FROM flickr_photo a ';
		}		
		
		/**
		  * Retrieves all the photo data for a given photo album (used in the admin section)
		  * @param $por_id Photo album ID
		  */
		public function getAllData($por_id) {
			global $FLDB;
			$st = $FLDB->prepare("SELECT * FROM flickr_photo WHERE por_id=? ORDER BY img_id");
			$st->bindParam(1, $por_id, PDO::PARAM_INT);
			return Helpers::fetchAll($st);		
		}		
		
		/**
		  * Returns the total number of photos available in the database
		  */
		public function getNumberOfPhotos($groupId = 0) {
			global $FLDB;
			
			if ($groupId > 0) {
				$sql = 'SELECT COUNT(img_id) nb FROM flickr_photo a, flickr_portfolio b WHERE a.por_id = b.por_id AND grp_id=?';
				$st = $FLDB->prepare($sql);
				$st->bindParam(1, $groupId, PDO::PARAM_INT);
			} else {
				$st = $FLDB->prepare('SELECT COUNT(*) nb FROM flickr_photo');
			}
						
			$res = Helpers::fetchOneRow($st);	
			return ($res != false && isset($res['nb'])) ? $res['nb'] : 0;
		}
	}
?>
Return current item: phpFlickrSynch