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