<?php
if ( !class_exists('flagdb') ) :
/**
* FlAGallery Database Class
*
*/
class flagdb {
/**
* Holds the list of all galleries
*
* @access public
* @var object|array
*/
var $galleries = false;
/**
* Holds the list of all images
*
* @access public
* @var object|array
*/
var $images = false;
/**
* Holds the list of all comments
*
* @access public
* @var object|array
*/
var $comments = false;
var $paged;
/**
* PHP4 compatibility layer for calling the PHP5 constructor.
*
*/
function flagdb() {
return $this->__construct();
}
/**
* Init the Database Abstraction layer for FlAGallery
*
*/
function __construct() {
global $wpdb;
$this->galleries = array();
$this->images = array();
$this->comments = array();
register_shutdown_function(array(&$this, "__destruct"));
}
/**
* PHP5 style destructor and will run when database object is destroyed.
*
* @return bool Always true
*/
function __destruct() {
return true;
}
/**
* Get all the galleries
*
* @param string $order_by
* @param string $order_dir
* @param bool $counter (optional) Select true when you need to count the images
* @param int $limit number of paged galleries, 0 shows all galleries
* @param int $start the start index for paged galleries
* @param bool|int $exclude
* @return array $galleries
*/
function find_all_galleries($order_by = 'gid', $order_dir = 'ASC', $counter = false, $limit = 0, $start = 0, $exclude = 0) {
/** @var $wpdb wpdb */
global $wpdb;
$exclude_clause = ($exclude) ? ' AND exclude<>1 ' : '';
$order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC';
if( !in_array($order_by, array('title','rand')) ) {
$order_by = 'gid';
}
if( $order_by == 'rand') $order_by = 'RAND()';
$limit_by = ( $limit > 0 ) ? 'LIMIT ' . intval($start) . ',' . intval($limit) : '';
$this->galleries = $wpdb->get_results( "SELECT SQL_CALC_FOUND_ROWS * FROM $wpdb->flaggallery ORDER BY {$order_by} {$order_dir} {$limit_by}", OBJECT_K );
// Count the number of galleries and calculate the pagination
if ($limit > 0) {
$this->paged['total_objects'] = intval ( $wpdb->get_var( "SELECT FOUND_ROWS()" ) );
$this->paged['objects_per_page'] = count( $this->galleries );
$this->paged['max_objects_per_page'] = ( $limit > 0 ) ? ceil( $this->paged['total_objects'] / intval($limit)) : 1;
}
if ( !$this->galleries )
return array();
// get the galleries information
foreach ($this->galleries as $key => $value) {
$galleriesID[] = $key;
// init the counter values
$this->galleries[$key]->counter = 0;
wp_cache_add($key, $this->galleries[$key], 'flag_gallery');
}
// if we didn't need to count the images then stop here
if ( !$counter )
return $this->galleries;
// get the counter values
$picturesCounter = $wpdb->get_results('SELECT galleryid, COUNT(*) as counter FROM '.$wpdb->flagpictures.' WHERE galleryid IN (\''.implode('\',\'', $galleriesID).'\') ' . $exclude_clause . ' GROUP BY galleryid', OBJECT_K);
if ( !$picturesCounter )
return $this->galleries;
// add the counter to the gallery objekt
foreach ($picturesCounter as $key => $value) {
$this->galleries[$value->galleryid]->counter = $value->counter;
wp_cache_add($value->galleryid, $this->galleries[$value->galleryid], 'flag_gallery');
}
return $this->galleries;
}
/**
* Get all the Albums
*
* @param string $order_by
* @param string $order_dir
* @return object $albums
*/
function find_all_albums($order_by = 'id', $order_dir = 'DESC') {
/** @var $wpdb wpdb */
global $wpdb;
$order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC';
$albums = $wpdb->get_results( "SELECT * FROM $wpdb->flagalbum ORDER BY {$order_by} {$order_dir}", OBJECT_K );
return $albums;
}
/**
* Get all galleries from Album
*
* @param $id
* @return object $albums
*/
function get_album($id) {
/** @var $wpdb wpdb */
global $wpdb;
$id = $wpdb->escape($id);
$albums = $wpdb->get_var( "SELECT categories FROM $wpdb->flagalbum WHERE id = '{$id}'" );
return $albums;
}
/**
* Get a gallery given its ID
*
* @param int|string $id or $name
* @return object flagGallery (null if not found)
*/
static function find_gallery( $id ) {
/** @var $wpdb wpdb */
global $wpdb;
if( is_numeric($id) ) {
if ( $gallery = wp_cache_get($id, 'flag_gallery') )
return $gallery;
$gallery = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $wpdb->flaggallery WHERE gid = %d", $id ) );
} else
$gallery = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $wpdb->flaggallery WHERE name = %s", $id ) );
// Build the object from the query result
if ($gallery) {
$gallery->abspath = WINABSPATH . $gallery->path;
wp_cache_add($id, $gallery, 'flag_gallery');
return $gallery;
} else
return false;
}
/**
* This function return all information about the gallery and the images inside
*
* @param int|string $id or $name
* @param string $order_by
* @param string $order_dir (ASC |DESC)
* @param bool|int $exclude
* @param int $limit number of paged galleries, 0 shows all galleries
* @param int $start the start index for paged galleries
* @return array containing the flagImage objects representing the images in the gallery.
*/
function get_gallery($id, $order_by = 'sortorder', $order_dir = 'ASC', $exclude = 0, $limit = 0, $start = 0) {
/** @var $wpdb wpdb */
global $wpdb;
// init the gallery as empty array
$gallery = array();
// Check for the exclude setting
$exclude_clause = ($exclude) ? ' AND tt.exclude<>1 ' : '';
// Say no to any other value
$order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC';
$order_by = ( empty($order_by) ) ? 'tt.sortorder' : 'tt.'.$order_by;
if($order_by == 'tt.rand()'){
$order_by = 'rand()';
$order_dir = '';
}
// Should we limit this query ?
$limit_by = ( $limit > 0 ) ? 'LIMIT ' . intval($start) . ',' . intval($limit) : '';
// Query database
if( is_numeric($id) )
$result = $wpdb->get_results( $wpdb->prepare( "SELECT SQL_CALC_FOUND_ROWS tt.*, t.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE t.gid = %d {$exclude_clause} ORDER BY {$order_by} {$order_dir} {$limit_by}", $id ), OBJECT_K );
else
$result = $wpdb->get_results( $wpdb->prepare( "SELECT SQL_CALC_FOUND_ROWS tt.*, t.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE t.name = %s {$exclude_clause} ORDER BY {$order_by} {$order_dir} {$limit_by}", $id ), OBJECT_K );
// Count the number of images and calculate the pagination
if ($limit > 0) {
$this->paged['total_objects'] = intval ( $wpdb->get_var( "SELECT FOUND_ROWS()" ) );
$this->paged['objects_per_page'] = max ( count( $result ), $limit );
$this->paged['max_objects_per_page'] = ( $limit > 0 ) ? ceil( $this->paged['total_objects'] / intval($limit)) : 1;
}
// Build the object
if ($result) {
// Now added all image data
foreach ($result as $key => $value)
$gallery[$key] = new flagImage( $value );
}
// Could not add to cache, the structure is different to find_gallery() cache_add, need rework
//wp_cache_add($id, $gallery, 'flag_gallery');
return $gallery;
}
/**
* This function return all information about the gallery and the images inside
*
* @param int|string $id or $name
* @param string $order_by
* @param string $order_dir
* @param bool|int $exclude
* @return array containing the flagImage objects representing the images in the gallery.
*/
static function get_ids_from_gallery($id, $order_by = 'sortorder', $order_dir = 'ASC', $exclude = 0) {
/** @var $wpdb wpdb */
global $wpdb;
// Check for the exclude setting
$exclude_clause = ($exclude) ? ' AND tt.exclude<>1 ' : '';
// Say no to any other value
$order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC';
$order_by = ( empty($order_by) ) ? 'sortorder' : $order_by;
// Query database
if( is_numeric($id) )
$result = $wpdb->get_col( $wpdb->prepare( "SELECT tt.pid FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE t.gid = %d $exclude_clause ORDER BY tt.{$order_by} $order_dir", $id ) );
else
$result = $wpdb->get_col( $wpdb->prepare( "SELECT tt.pid FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE t.name = %s $exclude_clause ORDER BY tt.{$order_by} $order_dir", $id ) );
return $result;
}
/**
* Delete a gallery AND all the pictures associated to this gallery!
*
* @gid The gallery ID
*/
function delete_gallery($gid) {
/** @var $wpdb wpdb */
global $wpdb;
$wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->flagpictures WHERE galleryid = %d", $gid) );
$wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->flaggallery WHERE gid = %d", $gid) );
wp_cache_delete($gid, 'flag_gallery');
return true;
}
/**
* Insert an image in the database
*
* @param $gid
* @param $filename
* @param $alttext
* @param $desc
* @param int $exclude
* @return int the ID of the inserted image
*/
static function insert_image($gid, $filename, $alttext, $desc, $exclude = 0) {
/** @var $wpdb wpdb */
global $wpdb;
$result = $wpdb->query(
"INSERT INTO $wpdb->flagpictures (galleryid, filename, description, alttext, exclude) VALUES "
. "('$gid', '$filename', '$desc', '$alttext', '$exclude');");
$pid = (int) $wpdb->insert_id;
wp_cache_delete($gid, 'flag_gallery');
return $pid;
}
/**
* flagdb::update_image() - Insert an image in the database
*
* @param int $pid id of the image
* @param bool $galleryid
* @param bool $filename
* @param bool $description
* @param bool $alttext
* @param int $exclude
* @param bool $sortorder
* @return bool result of the ID of the inserted image
*/
static function update_image($pid, $galleryid = false, $filename = false, $description = false, $alttext = false, $exclude = 0, $sortorder = false) {
/** @var $wpdb wpdb */
global $wpdb;
$sql = array();
$pid = (int) $pid;
$update = array(
'galleryid' => $galleryid,
'filename' => $filename,
'description' => $description,
'alttext' => $alttext,
'exclude' => $exclude,
'sortorder' => $sortorder);
// create the sql parameter "name = value"
foreach ($update as $key => $value)
if ($value)
$sql[] = $key . " = '" . $value . "'";
// create the final string
$sql = implode(', ', $sql);
$result = false;
if ( !empty($sql) && $pid != 0)
$result = $wpdb->query( "UPDATE $wpdb->flagpictures SET $sql WHERE pid = $pid" );
wp_cache_delete($pid, 'flag_image');
return $result;
}
/**
* Get an image given its ID
*
* @param int $id The image ID
* @return object A flagImage object representing the image (false if not found)
*/
static function find_image( $id ) {
/** @var $wpdb wpdb */
global $wpdb;
if ( $image = wp_cache_get($id, 'flag_image') )
return $image;
// Query database
$result = $wpdb->get_row( $wpdb->prepare( "SELECT tt.*, t.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE tt.pid = %d ", $id ) );
// Build the object from the query result
if ($result) {
$image = new flagImage($result);
return $image;
}
return false;
}
/**
* Get images given a list of IDs
*
* @param $pids array of picture_ids
* @param int $exclude
* @param string $order
* @return array of flagImage objects representing the images
*/
static function find_images_in_list( $pids, $exclude = 0, $order = 'ASC' ) {
/** @var $wpdb wpdb */
global $wpdb;
$result = array();
// Check for the exclude setting
$exclude_clause = ($exclude) ? ' AND t.exclude <> 1 ' : '';
// Check for the order setting
$order_clause = ($order == 'RAND') ? 'ORDER BY rand() ' : ' ORDER BY t.pid ASC' ;
if ( is_array($pids) ) {
$id_list = "'" . implode("', '", $pids) . "'";
// Save Query database
$images = $wpdb->get_results("SELECT t.*, tt.* FROM $wpdb->flagpictures AS t INNER JOIN $wpdb->flaggallery AS tt ON t.galleryid = tt.gid WHERE t.pid IN ($id_list) $exclude_clause $order_clause", OBJECT_K);
// Build the image objects from the query result
if ($images) {
foreach ($images as $key => $image)
$result[$key] = new flagImage( $image );
}
}
return $result;
}
/**
* Add an image to the database
*
* @param bool $id
* @param bool $filename
* @param string $description
* @param string $alttext
* @param bool $meta_data
* @param int $post_id
* @param string $imagedate
* @param int $exclude
* @param int $sortorder
* @return bool result of the ID of the inserted image
*/
function add_image( $id = false, $filename = false, $description = '', $alttext = '', $meta_data = false, $post_id = 0, $imagedate = '0000-00-00 00:00:00', $exclude = 0, $sortorder = 0 ) {
/** @var $wpdb wpdb */
global $wpdb;
if ( is_array($meta_data) )
$meta_data = serialize($meta_data);
// Add the image
if ( false === $wpdb->query( $wpdb->prepare("INSERT INTO $wpdb->flagpictures (galleryid, filename, description, alttext, meta_data, post_id, imagedate, exclude, sortorder)
VALUES (%d, %s, %s, %s, %s, %d, %s, %d, %d)", $id, $filename, $description, $alttext, $meta_data, $post_id, $imagedate, $exclude, $sortorder ) ) ) {
return false;
}
$imageID = (int) $wpdb->insert_id;
// Remove from cache the galley, needs to be rebuild now
wp_cache_delete( $id, 'flag_gallery');
//and give me the new id
return $imageID;
}
/**
* Delete an image entry from the database
*/
static function delete_image($pid) {
/** @var $wpdb wpdb */
global $wpdb;
// Delete the image row
$result = $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->flagpictures WHERE pid = %d", $pid) );
// Remove from cache
wp_cache_delete( $pid, 'flag_image');
return $result;
}
/**
* Get the last images registered in the database with a maximum number of $limit results
*
* @param integer $page
* @param integer $limit
* @param int $exclude
* @param int $galleryId Only look for images with this gallery id, or in all galleries if id is 0
* @param string $orderby
* @param string $orderby is one of "id" (default, order by pid), "date" (order by exif date), sort (order by user sort order)
* @return array
*/
function find_last_images($page = 0, $limit = 30, $exclude = 0, $galleryId = 0, $orderby = "id") {
/** @var $wpdb wpdb */
global $wpdb;
// Check for the exclude setting
$exclude_clause = ($exclude) ? ' AND exclude<>1 ' : '';
$offset = (int) $page * $limit;
$galleryId = (int) $galleryId;
$gallery_clause = ($galleryId === 0) ? '' : ' AND galleryid = ' . $galleryId . ' ';
// default order by pid
$order = 'pid DESC';
switch ($orderby) {
case 'date':
$order = 'imagedate DESC';
break;
case 'sort':
$order = 'sortorder ASC';
break;
}
$result = array();
$gallery_cache = array();
// Query database
$images = $wpdb->get_results("SELECT * FROM $wpdb->flagpictures WHERE 1=1 $exclude_clause $gallery_clause ORDER BY $order LIMIT $offset, $limit");
// Build the object from the query result
if ($images) {
foreach ($images as $key => $image) {
// cache a gallery , so we didn't need to lookup twice
if (!array_key_exists($image->galleryid, $gallery_cache))
$gallery_cache[$image->galleryid] = flagdb::find_gallery($image->galleryid);
// Join gallery information with picture information
foreach ($gallery_cache[$image->galleryid] as $index => $value)
$image->$index = $value;
// Now get the complete image data
$result[$key] = new flagImage( $image );
}
}
return $result;
}
/**
* flagdb::get_random_images() - Get an random image from one ore more gally
*
* @param integer $number of images
* @param integer $galleryID optional a Gallery
* @param int $exclude
* @return object flagImage representing the image (null if not found)
*/
function get_random_images($number = 1, $galleryID = 0, $exclude = 0) {
/** @var $wpdb wpdb */
global $wpdb;
// Check for the exclude setting
$exclude_clause = ($exclude) ? ' AND tt.exclude != 1 ' : '';
$number = (int) $number;
$galleryID = (int) $galleryID;
$images = array();
// Query database
if ($galleryID == 0)
$result = $wpdb->get_results("SELECT t.*, tt.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE 1=1 $exclude_clause ORDER by rand() limit $number");
else
$result = $wpdb->get_results("SELECT t.*, tt.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE t.gid = $galleryID $exclude_clause ORDER by rand() limit {$number}");
// Return the object from the query result
if ($result) {
foreach ($result as $image) {
$images[] = new flagImage( $image );
}
return $images;
}
return null;
}
/**
* search for images and return the result
*
* @since 0.40
* @param string $request
* @return Array Result of the request
*/
function search_for_images( $request ) {
/** @var $wpdb wpdb */
global $wpdb;
// If a search pattern is specified, load the posts that match
if ( !empty($request) ) {
// added slashes screw with quote grouping when done early, so done later
$request = stripslashes($request);
// split the words it a array if seperated by a space or comma
preg_match_all('/".*?("|$)|((?<=[\\s",+])|^)[^\\s",+]+/', $request, $matches);
$search_terms = array_map(create_function('$a', 'return trim($a, "\\"\'\\n\\r ");'), $matches[0]);
$n = '%';
$search = '';
$searchand = '';
foreach( (array) $search_terms as $term) {
$term = addslashes_gpc($term);
$search .= "{$searchand}((tt.description LIKE '{$n}{$term}{$n}') OR (tt.alttext LIKE '{$n}{$term}{$n}') OR (tt.filename LIKE '{$n}{$term}{$n}'))";
$searchand = ' AND ';
}
$term = $wpdb->escape($request);
if (count($search_terms) > 1 && $search_terms[0] != $request )
$search .= " OR (tt.description LIKE '{$n}{$term}{$n}') OR (tt.alttext LIKE '{$n}{$term}{$n}') OR (tt.filename LIKE '{$n}{$term}{$n}')";
if ( !empty($search) )
$search = " AND ({$search}) ";
}
// build the final query
$query = "SELECT t.*, tt.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE 1=1 $search ORDER BY tt.pid ASC ";
$result = $wpdb->get_results($query);
// Return the object from the query result
if ($result) {
$images = array();
foreach ($result as $image) {
$images[] = new flagImage( $image );
}
return $images;
}
return null;
}
/**
* search for a filename
*
* @since 0.40
* @param string $filename
* @param bool|int $galleryID
* @return Array Result of the request
*/
function search_for_file( $filename, $galleryID = false ) {
/** @var $wpdb wpdb */
global $wpdb;
$where_clause = '';
// If a search pattern is specified, load the posts that match
if ( !empty($filename) ) {
// added slashes screw with quote grouping when done early, so done later
$term = $wpdb->escape($filename);
if ( is_numeric($galleryID) ) {
$id = (int) $galleryID;
$where_clause = " AND tt.galleryid = {$id}";
}
}
// build the final query
$query = "SELECT t.*, tt.* FROM $wpdb->flaggallery AS t INNER JOIN $wpdb->flagpictures AS tt ON t.gid = tt.galleryid WHERE tt.filename = '{$term}' {$where_clause} ORDER BY tt.pid ASC ";
$result = $wpdb->get_row($query);
// Return the object from the query result
if ($result) {
$image = new flagImage( $result );
return $image;
}
return null;
}
/**
* Update or add meta data for an image
*
* @param int $id The image ID
* @param $new_values
* @internal param array $values An array with existing or new values
* @return bool result of query
*/
static function update_image_meta( $id, $new_values ) {
/** @var $wpdb wpdb */
global $wpdb;
// Query database for existing values
// Use cache object
$old_values = $wpdb->get_var( $wpdb->prepare( "SELECT meta_data FROM $wpdb->flagpictures WHERE pid = %d ", $id ) );
$old_values = unserialize( $old_values );
$meta = array_merge( (array)$old_values, (array)$new_values );
$result = $wpdb->query( $wpdb->prepare("UPDATE $wpdb->flagpictures SET meta_data = %s WHERE pid = %d", serialize($meta), $id) );
wp_cache_delete($id, 'flag_image');
return $result;
}
}
endif;
if ( ! isset($GLOBALS['flagdb']) ) {
/**
* Initate the FlAGallery Database Object, for later cache reasons
* @global object $flagdb Creates a new flagdb object
*/
unset($GLOBALS['flagdb']);
$GLOBALS['flagdb'] =& new flagdb();
}
?>