<?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;
}
}
?>