nggdb

NextGEN Gallery Database Class.

Defined (1)

The class is defined in the following location(s).

/lib/ngg-db.php  
  1. class nggdb { 
  2.  
  3. /** 
  4. * Holds the list of all galleries 
  5. * @since 1.1.0 
  6. * @access public 
  7. * @var object|array 
  8. */ 
  9. var $galleries = false; 
  10.  
  11. /** 
  12. * Holds the list of all images 
  13. * @since 1.3.0 
  14. * @access public 
  15. * @var object|array 
  16. */ 
  17. var $images = false; 
  18.  
  19. /** 
  20. * Holds the list of all albums 
  21. * @since 1.3.0 
  22. * @access public 
  23. * @var object|array 
  24. */ 
  25. var $albums = false; 
  26.  
  27. /** 
  28. * The array for the pagination 
  29. * @since 1.1.0 
  30. * @access public 
  31. * @var array 
  32. */ 
  33. var $paged = false; 
  34.  
  35. /** 
  36. * Init the Database Abstraction layer for NextGEN Gallery 
  37. */ 
  38. function __construct() { 
  39. global $wpdb; 
  40.  
  41. $this->galleries = array(); 
  42. $this->images = array(); 
  43. $this->albums = array(); 
  44. $this->paged = array(); 
  45.  
  46. register_shutdown_function(array(&$this, '__destruct')); 
  47.  
  48.  
  49. /** 
  50. * PHP5 style destructor and will run when database object is destroyed. 
  51. * @return bool Always true 
  52. */ 
  53. function __destruct() { 
  54. return true; 
  55.  
  56. /** 
  57. * Get all the album and unserialize the content 
  58. * @since 1.3.0 
  59. * @param string $order_by 
  60. * @param string $order_dir 
  61. * @param int $limit number of albums, 0 shows all albums 
  62. * @param int $start the start index for paged albums 
  63. * @return array $album 
  64. */ 
  65. function find_all_album( $order_by = 'id', $order_dir = 'ASC', $limit = 0, $start = 0) { 
  66. global $wpdb; 
  67.  
  68. $order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC'; 
  69. $limit_by = ( $limit > 0 ) ? 'LIMIT ' . intval($start) . ', ' . intval($limit) : ''; 
  70. $this->albums = $wpdb->get_results("SELECT * FROM $wpdb->nggalbum ORDER BY {$order_by} {$order_dir} {$limit_by}" , OBJECT_K ); 
  71.  
  72. if ( !$this->albums ) 
  73. return array(); 
  74.  
  75. foreach ($this->albums as $key => $value) { 
  76. $this->albums[$key]->galleries = empty ($this->albums[$key]->sortorder) ? array() : (array) unserialize($this->albums[$key]->sortorder) ; 
  77. $this->albums[$key]->name = stripslashes( $this->albums[$key]->name ); 
  78. $this->albums[$key]->albumdesc = stripslashes( $this->albums[$key]->albumdesc ); 
  79. wp_cache_add($key, $this->albums[$key], 'ngg_album'); 
  80.  
  81. return $this->albums; 
  82.  
  83. static function count_galleries() { 
  84.  
  85. global $wpdb; 
  86.  
  87. return $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->nggallery" ); 
  88.  
  89. /** 
  90. * @param $id int The gallery ID. 
  91. * @return null|string 
  92. */ 
  93. static function count_images_in_gallery( $id ) { 
  94.  
  95. global $wpdb; 
  96.  
  97. return $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->nggpictures WHERE galleryid = %d", $id ) ); 
  98.  
  99. /** 
  100. * Get all the galleries 
  101. * @param string $order_by 
  102. * @param string $order_dir 
  103. * @param bool $counter (optional) Select true when you need to count the images 
  104. * @param int $limit number of paged galleries, 0 shows all galleries 
  105. * @param int $start the start index for paged galleries 
  106. * @param bool $exclude 
  107. * @return array $galleries 
  108. */ 
  109. function find_all_galleries($order_by = 'gid', $order_dir = 'ASC', $counter = false, $limit = 0, $start = 0, $exclude = true) { 
  110. global $wpdb; 
  111.  
  112. // Check for the exclude setting 
  113. $exclude_clause = ($exclude) ? ' AND exclude<>1 ' : ''; 
  114. $order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC'; 
  115. $limit_by = ( $limit > 0 ) ? 'LIMIT ' . intval($start) . ', ' . intval($limit) : ''; 
  116. $this->galleries = $wpdb->get_results( "SELECT SQL_CALC_FOUND_ROWS * FROM $wpdb->nggallery ORDER BY {$order_by} {$order_dir} {$limit_by}", OBJECT_K ); 
  117.  
  118. // Count the number of galleries and calculate the pagination 
  119. if ($limit > 0) { 
  120. $this->paged['total_objects'] = intval ( $wpdb->get_var( "SELECT FOUND_ROWS()" ) ); 
  121. $this->paged['objects_per_page'] = max ( count( $this->galleries ), $limit ); 
  122. $this->paged['max_objects_per_page'] = ( $limit > 0 ) ? ceil( $this->paged['total_objects'] / intval($limit)) : 1; 
  123.  
  124. if ( !$this->galleries ) 
  125. return array(); 
  126.  
  127. // get the galleries information 
  128. foreach ($this->galleries as $key => $value) { 
  129. $galleriesID[] = $key; 
  130. // init the counter values 
  131. $this->galleries[$key]->counter = 0; 
  132. $this->galleries[$key]->title = stripslashes($this->galleries[$key]->title); 
  133. $this->galleries[$key]->galdesc = stripslashes($this->galleries[$key]->galdesc); 
  134. $this->galleries[$key]->abspath = WINABSPATH . $this->galleries[$key]->path; 
  135. wp_cache_add($key, $this->galleries[$key], 'ngg_gallery'); 
  136.  
  137. // if we didn't need to count the images then stop here 
  138. if ( !$counter ) 
  139. return $this->galleries; 
  140.  
  141. // get the counter values 
  142. $picturesCounter = $wpdb->get_results('SELECT galleryid, COUNT(*) as counter FROM '.$wpdb->nggpictures.' WHERE galleryid IN (\''.implode('\', \'', $galleriesID).'\') ' . $exclude_clause . ' GROUP BY galleryid', OBJECT_K); 
  143.  
  144. if ( !$picturesCounter ) 
  145. return $this->galleries; 
  146.  
  147. // add the counter to the gallery objekt 
  148. foreach ($picturesCounter as $key => $value) { 
  149. $this->galleries[$value->galleryid]->counter = $value->counter; 
  150. wp_cache_set($value->galleryid, $this->galleries[$value->galleryid], 'ngg_gallery'); 
  151.  
  152. return $this->galleries; 
  153.  
  154. /** 
  155. * Get a gallery given its ID 
  156. * @param int|string $id or $slug 
  157. * @return A nggGallery object (null if not found) 
  158. */ 
  159. static function find_gallery( $id ) { 
  160. global $wpdb; 
  161.  
  162. if( is_numeric($id) ) { 
  163.  
  164. if ( $gallery = wp_cache_get($id, 'ngg_gallery') ) 
  165. return $gallery; 
  166.  
  167. $gallery = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $wpdb->nggallery WHERE gid = %d", $id ) ); 
  168.  
  169. } else 
  170. $gallery = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $wpdb->nggallery WHERE slug = %s", $id ) ); 
  171.  
  172. // Build the object from the query result 
  173. if ($gallery) { 
  174. // it was a bad idea to use a object, stripslashes_deep() could not used here, learn from it 
  175. $gallery->title = stripslashes($gallery->title); 
  176. $gallery->galdesc = stripslashes($gallery->galdesc); 
  177.  
  178. $gallery->abspath = WINABSPATH . $gallery->path; 
  179. //TODO:Possible failure , $id could be a number or name 
  180. wp_cache_add($id, $gallery, 'ngg_gallery'); 
  181.  
  182. return $gallery; 
  183. } else 
  184. return false; 
  185.  
  186. /** 
  187. * This function return all information about the gallery and the images inside 
  188. * @param int|string $id or $name 
  189. * @param string $order_by 
  190. * @param string $order_dir (ASC |DESC) 
  191. * @param bool $exclude 
  192. * @param int $limit number of paged galleries, 0 shows all galleries 
  193. * @param int $start the start index for paged galleries 
  194. * @param bool $json remove the key for associative array in json request 
  195. * @return An array containing the nggImage objects representing the images in the gallery. 
  196. */ 
  197. function get_gallery($id, $order_by = 'sortorder', $order_dir = 'ASC', $exclude = true, $limit = 0, $start = 0, $json = false) { 
  198.  
  199. global $wpdb; 
  200.  
  201. // init the gallery as empty array 
  202. $gallery = array(); 
  203. $i = 0; 
  204.  
  205. // Check for the exclude setting 
  206. $exclude_clause = ($exclude) ? ' AND tt.exclude<>1 ' : ''; 
  207.  
  208. // Say no to any other value 
  209. $order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC'; 
  210. $order_by = ( empty($order_by) ) ? 'sortorder' : $order_by; 
  211. $order_clause = "ABS(tt.{$order_by}) {$order_dir}, tt.{$order_by} {$order_dir}"; 
  212. // $order_clause = "LENGTH(tt.{$order_by}) {$order_dir}, tt.{$order_by} {$order_dir}"; 
  213.  
  214. // Should we limit this query ? 
  215. $limit_by = ( $limit > 0 ) ? 'LIMIT ' . intval($start) . ', ' . intval($limit) : ''; 
  216.  
  217. // Query database 
  218. if( is_numeric($id) ) 
  219. $result = $wpdb->get_results( $wpdb->prepare( "SELECT SQL_CALC_FOUND_ROWS tt.*, t.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE t.gid = %d {$exclude_clause} ORDER BY {$order_clause} {$limit_by}", $id ), OBJECT_K ); 
  220. else 
  221. $result = $wpdb->get_results( $wpdb->prepare( "SELECT SQL_CALC_FOUND_ROWS tt.*, t.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE t.slug = %s {$exclude_clause} ORDER BY {$order_clause} {$limit_by}", $id ), OBJECT_K ); 
  222.  
  223. // Count the number of images and calculate the pagination 
  224. if ($limit > 0) { 
  225. $this->paged['total_objects'] = intval ( $wpdb->get_var( "SELECT FOUND_ROWS()" ) ); 
  226. $this->paged['objects_per_page'] = max ( count( $result ), $limit ); 
  227. $this->paged['max_objects_per_page'] = ( $limit > 0 ) ? ceil( $this->paged['total_objects'] / intval($limit)) : 1; 
  228.  
  229. // Build the object 
  230. if ($result) { 
  231.  
  232. // Now added all image data 
  233. foreach ($result as $key => $value) { 
  234. // due to a browser bug we need to remove the key for associative array for json request 
  235. // (see http://code.google.com/p/chromium/issues/detail?id=883) 
  236. if ($json) $key = $i++; 
  237. $gallery[$key] = new nggImage( $value ); // keep in mind each request require 8-16 kb memory usage 
  238.  
  239.  
  240. // Could not add to cache, the structure is different to find_gallery() cache_add, need rework 
  241. //wp_cache_add($id, $gallery, 'ngg_gallery'); 
  242.  
  243. return $gallery; 
  244.  
  245. /** 
  246. * This function return all information about the gallery and the images inside 
  247. * @param int|string $id or $name 
  248. * @param string $orderby 
  249. * @param string $order (ASC |DESC) 
  250. * @param bool $exclude 
  251. * @return array An array containing the nggImage objects representing the images in the gallery. 
  252. */ 
  253. static function get_ids_from_gallery($id, $order_by = 'sortorder', $order_dir = 'ASC', $exclude = true) { 
  254.  
  255. global $wpdb; 
  256.  
  257. // Check for the exclude setting 
  258. $exclude_clause = ($exclude) ? ' AND tt.exclude<>1 ' : ''; 
  259.  
  260. // Say no to any other value 
  261. $order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC'; 
  262. $order_by = ( empty($order_by) ) ? 'sortorder' : $order_by; 
  263.  
  264. // Query database 
  265. if( is_numeric($id) ) 
  266. $result = $wpdb->get_col( $wpdb->prepare( "SELECT tt.pid FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE t.gid = %d $exclude_clause ORDER BY tt.{$order_by} $order_dir", $id ) ); 
  267. else 
  268. $result = $wpdb->get_col( $wpdb->prepare( "SELECT tt.pid FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE t.slug = %s $exclude_clause ORDER BY tt.{$order_by} $order_dir", $id ) ); 
  269.  
  270. return $result; 
  271.  
  272. /** 
  273. * Delete a gallery AND all the pictures associated to this gallery! 
  274. * @id The gallery ID 
  275. */ 
  276. static function delete_gallery( $id ) { 
  277. global $wpdb, $nggdb; 
  278.  
  279. $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->nggpictures WHERE galleryid = %d", $id) ); 
  280. $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->nggallery WHERE gid = %d", $id) ); 
  281.  
  282. wp_cache_delete($id, 'ngg_gallery'); 
  283.  
  284. //TODO:Remove all tag relationship 
  285.  
  286. //Update the galleries to remove the deleted ID's 
  287. $albums = $nggdb->find_all_album(); 
  288.  
  289. foreach ($albums as $album) { 
  290. $albumid = $album->id; 
  291. $galleries = $album->galleries; 
  292. $deleted = array_search($id, $galleries); 
  293.  
  294. unset($galleries[$deleted]); 
  295.  
  296. $new_galleries = serialize($galleries); 
  297.  
  298. nggdb::update_album($albumid, false, false, false, $new_galleries); 
  299.  
  300. return true; 
  301.  
  302. /** 
  303. * Get an album given its ID 
  304. * @id The album ID or name 
  305. * @return A nggGallery object (false if not found) 
  306. */ 
  307. static function find_album( $id ) { 
  308. global $wpdb; 
  309.  
  310. // Query database 
  311. if ( is_numeric($id) && $id != 0 ) { 
  312. if ( $album = wp_cache_get($id, 'ngg_album') ) 
  313. return $album; 
  314.  
  315. $album = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $wpdb->nggalbum WHERE id = %d", $id) ); 
  316. } elseif ( $id == 'all' || (is_numeric($id) && $id == 0) ) { 
  317. // init the object and fill it 
  318. $album = new stdClass(); 
  319. $album->id = 'all'; 
  320. $album->name = __('Album overview', 'nggallery'); 
  321. $album->albumdesc = __('Album overview', 'nggallery'); 
  322. $album->previewpic = 0; 
  323. $album->sortorder = serialize( $wpdb->get_col("SELECT gid FROM $wpdb->nggallery") ); 
  324. } else { 
  325. $album = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $wpdb->nggalbum WHERE slug = %s", $id) ); 
  326.  
  327. // Unserialize the galleries inside the album 
  328. if ( $album ) { 
  329. if ( !empty( $album->sortorder ) ) 
  330. $album->gallery_ids = unserialize( $album->sortorder ); 
  331.  
  332. // it was a bad idea to use a object, stripslashes_deep() could not used here, learn from it 
  333. $album->albumdesc = stripslashes($album->albumdesc); 
  334. $album->name = stripslashes($album->name); 
  335.  
  336. wp_cache_add($album->id, $album, 'ngg_album'); 
  337. return $album; 
  338.  
  339. return false; 
  340.  
  341. /** 
  342. * Delete an album 
  343. * @id The album ID 
  344. */ 
  345. static function delete_album( $id ) { 
  346. global $wpdb; 
  347.  
  348. $result = $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->nggalbum WHERE id = %d", $id) ); 
  349. wp_cache_delete($id, 'ngg_album'); 
  350.  
  351. return $result; 
  352.  
  353. /** 
  354. * Insert an image in the database 
  355. * @return the ID of the inserted image 
  356. */ 
  357. static function insert_image($gid, $filename, $alttext, $desc, $exclude) { 
  358. global $wpdb; 
  359.  
  360. $result = $wpdb->query( 
  361. "INSERT INTO $wpdb->nggpictures (galleryid, filename, description, alttext, exclude) VALUES " 
  362. . "('$gid', '$filename', '$desc', '$alttext', '$exclude');"); 
  363. $pid = (int) $wpdb->insert_id; 
  364. wp_cache_delete($gid, 'ngg_gallery'); 
  365.  
  366. return $pid; 
  367.  
  368. /** 
  369. * nggdb::update_image() - Update an image in the database 
  370. * @param int $pid id of the image 
  371. * @param (optional) string|int $galleryid 
  372. * @param (optional) string $filename 
  373. * @param (optional) string $description 
  374. * @param (optional) string $alttext 
  375. * @param (optional) int $exclude (0 or 1) 
  376. * @param (optional) int $sortorder 
  377. * @return bool result of update query 
  378. */ 
  379. static function update_image($pid, $galleryid = false, $filename = false, $description = false, $alttext = false, $exclude = false, $sortorder = false) { 
  380.  
  381. global $wpdb; 
  382.  
  383. $sql = array(); 
  384. $pid = (int) $pid; 
  385.  
  386. // slug must be unique, we use the alttext for that 
  387. $slug = nggdb::get_unique_slug( sanitize_title( $alttext ), 'image' ); 
  388.  
  389. $update = array( 
  390. 'image_slug' => $slug,  
  391. 'galleryid' => $galleryid,  
  392. 'filename' => $filename,  
  393. 'description' => $description,  
  394. 'alttext' => $alttext,  
  395. 'exclude' => $exclude,  
  396. 'sortorder' => $sortorder); 
  397.  
  398. // create the sql parameter "name = value" 
  399. foreach ($update as $key => $value) 
  400. if ($value !== false) 
  401. $sql[] = $key . " = '" . $value . "'"; 
  402.  
  403. // create the final string 
  404. $sql = implode(', ', $sql); 
  405.  
  406. if ( !empty($sql) && $pid != 0) 
  407. $result = $wpdb->query( "UPDATE $wpdb->nggpictures SET $sql WHERE pid = $pid" ); 
  408.  
  409. wp_cache_delete($pid, 'ngg_image'); 
  410.  
  411. return $result; 
  412.  
  413. /** 
  414. * nggdb::update_gallery() - Update an gallery in the database 
  415. * @since V1.7.0 
  416. * @param int $id id of the gallery 
  417. * @param (optional) string $title or name of the gallery 
  418. * @param (optional) string $path 
  419. * @param (optional) string $description 
  420. * @param (optional) int $pageid 
  421. * @param (optional) int $previewpic 
  422. * @param (optional) int $author 
  423. * @return bool result of update query 
  424. */ 
  425. static function update_gallery($id, $name = false, $path = false, $title = false, $description = false, $pageid = false, $previewpic = false, $author = false) { 
  426.  
  427. global $wpdb; 
  428.  
  429. $sql = array(); 
  430. $id = (int) $id; 
  431.  
  432. // slug must be unique, we use the title for that 
  433. $slug = nggdb::get_unique_slug( sanitize_title( $title ), 'gallery' ); 
  434.  
  435. $update = array( 
  436. 'name' => $name,  
  437. 'slug' => $slug,  
  438. 'path' => $path,  
  439. 'title' => $title,  
  440. 'galdesc' => $description,  
  441. 'pageid' => $pageid,  
  442. 'previewpic' => $previewpic,  
  443. 'author' => $author); 
  444.  
  445. // create the sql parameter "name = value" 
  446. foreach ($update as $key => $value) 
  447. if ($value !== false) 
  448. $sql[] = $key . " = '" . $value . "'"; 
  449.  
  450. // create the final string 
  451. $sql = implode(', ', $sql); 
  452.  
  453. if ( !empty($sql) && $id != 0) 
  454. $result = $wpdb->query( "UPDATE $wpdb->nggallery SET $sql WHERE gid = $id" ); 
  455.  
  456. wp_cache_delete($id, 'ngg_gallery'); 
  457.  
  458. return $result; 
  459.  
  460. /** 
  461. * nggdb::update_album() - Update an album in the database 
  462. * @since V1.7.0 
  463. * @param int $ id id of the album 
  464. * @param (optional) string $title 
  465. * @param (optional) int $previewpic 
  466. * @param (optional) string $description 
  467. * @param (optional) serialized array $sortorder 
  468. * @param (optional) int $pageid 
  469. * @return bool result of update query 
  470. */ 
  471. static function update_album($id, $name = false, $previewpic = false, $description = false, $sortorder = false, $pageid = false ) { 
  472.  
  473. global $wpdb; 
  474.  
  475. $sql = array(); 
  476. $id = (int) $id; 
  477.  
  478. // slug must be unique, we use the title for that 
  479. $slug = nggdb::get_unique_slug( sanitize_title( $name ), 'album' ); 
  480.  
  481. $update = array( 
  482. 'name' => $name,  
  483. 'slug' => $slug,  
  484. 'previewpic' => $previewpic,  
  485. 'albumdesc' => $description,  
  486. 'sortorder' => $sortorder,  
  487. 'pageid' => $pageid); 
  488.  
  489. // create the sql parameter "name = value" 
  490. foreach ($update as $key => $value) 
  491. if ($value !== false) 
  492. $sql[] = $key . " = '" . $value . "'"; 
  493.  
  494. // create the final string 
  495. $sql = implode(', ', $sql); 
  496.  
  497. if ( !empty($sql) && $id != 0) 
  498. $result = $wpdb->query( "UPDATE $wpdb->nggalbum SET $sql WHERE id = $id" ); 
  499.  
  500. wp_cache_delete($id, 'ngg_album'); 
  501.  
  502. return $result; 
  503.  
  504. /** 
  505. * Get an image given its ID 
  506. * @param int|string The image ID or Slug 
  507. * @return nggImage|bool The image, or false if it wasn't found. 
  508. */ 
  509. static function find_image( $id ) { 
  510. global $wpdb; 
  511.  
  512. if( is_numeric($id) ) { 
  513.  
  514. if ( $image = wp_cache_get($id, 'ngg_image') ) 
  515. return $image; 
  516.  
  517. $result = $wpdb->get_row( $wpdb->prepare( "SELECT tt.*, t.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE tt.pid = %d ", $id ) ); 
  518. } else 
  519. $result = $wpdb->get_row( $wpdb->prepare( "SELECT tt.*, t.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE tt.image_slug = %s ", $id ) ); 
  520.  
  521. // Build the object from the query result 
  522. if ($result) { 
  523. $image = new nggImage($result); 
  524. return $image; 
  525.  
  526. return false; 
  527.  
  528. /** 
  529. * Get images given a list of IDs 
  530. * @param $pids array of picture_ids 
  531. * @return An array of nggImage objects representing the images 
  532. */ 
  533. static function find_images_in_list( $pids, $exclude = false, $order = 'NOTSET') { 
  534. global $wpdb; 
  535.  
  536. $qry = nggdb::find_images_query( $pids, $exclude, $order); //Build query to get images 
  537. $images =$wpdb->get_results($qry, OBJECT_K); 
  538.  
  539. $result = array(); // Build the image objects from the query result 
  540. if ($images) { 
  541. foreach ($images as $key => $image) 
  542. $result[$key] = new nggImage( $image ); 
  543. return $result; 
  544.  
  545. /** 
  546. * Return sql query to get images 
  547. * 20150110: workarounded case if $pids is a string or numeric value. Makes function more flexible, yet there is no case where 
  548. * this functions is called using string/numeric... 
  549. * @param $pids array of Picture Ids | id value (string or integer) which is converted to array. 
  550. * @param $exclude Boolean true to exclude images with field exclude set 
  551. * @param $sort_dir String order direction. Options: ASC, DESC, RAND and NOTSET where natural sort is used. 
  552. */ 
  553. static function find_images_query( $pids, $exclude = false , $sort_dir = 'NOTSET') { 
  554. global $ngg, $wpdb; 
  555.  
  556. //Try to convert if there is one element. 
  557. //TODO: empty or invalid arrays should be avoided. (they will make an invalid query) 
  558. if (!is_array($pids)) { 
  559. $pids= array($pids); //create one-element array 
  560.  
  561. $exclude_clause = ($exclude) ? " AND t.exclude <> 1" : ""; //exclude images if required (notice one space before, none after) 
  562.  
  563. if ($sort_dir =="RAND") { 
  564. $order_clause = " ORDER BY rand()"; //notice one space before, none after 
  565. } else { 
  566. //get Sort field and sort direction to make order by sentence 
  567. $sort_dir = ($sort_dir == 'NOTSET') ? $ngg->options['galSortDir'] : $sort_dir; 
  568. $sort_field = $ngg->options['galSort']; 
  569. $order_clause = " ORDER BY t.$sort_field $sort_dir"; //notice one space before, none after 
  570. $id_list = "'" . implode("', '", $pids) . "'"; 
  571. //notice NO spaces between $exclude_clause and $order_clause on purpose 
  572. return "SELECT t.*, tt.* FROM $wpdb->nggpictures AS t INNER JOIN $wpdb->nggallery AS tt ON t.galleryid = tt.gid WHERE t.pid IN ($id_list)$exclude_clause$order_clause"; 
  573.  
  574.  
  575. /** 
  576. * Add an image to the database 
  577. * @since V1.4.0 
  578. * @param int $pid id of the gallery 
  579. * @param (optional) string|int $galleryid 
  580. * @param (optional) string $filename 
  581. * @param (optional) string $description 
  582. * @param (optional) string $alttext 
  583. * @param (optional) array $meta data 
  584. * @param (optional) int $post_id (required for sync with WP media lib) 
  585. * @param (optional) string $imagedate 
  586. * @param (optional) int $exclude (0 or 1) 
  587. * @param (optional) int $sortorder 
  588. * @return bool result of the ID of the inserted image 
  589. */ 
  590. static 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 ) { 
  591. global $wpdb; 
  592.  
  593. if ( is_array($meta_data) ) 
  594. $meta_data = serialize($meta_data); 
  595.  
  596. // slug must be unique, we use the alttext for that 
  597. $slug = nggdb::get_unique_slug( sanitize_title( $alttext ), 'image' ); 
  598.  
  599. // Add the image 
  600. if ( false === $wpdb->query( $wpdb->prepare("INSERT INTO $wpdb->nggpictures (image_slug, galleryid, filename, description, alttext, meta_data, post_id, imagedate, exclude, sortorder) 
  601. VALUES (%s, %d, %s, %s, %s, %s, %d, %s, %d, %d)", $slug, $id, $filename, $description, $alttext, $meta_data, $post_id, $imagedate, $exclude, $sortorder ) ) ) { 
  602. return false; 
  603.  
  604. $imageID = (int) $wpdb->insert_id; 
  605.  
  606. // Remove from cache the galley, needs to be rebuild now 
  607. wp_cache_delete( $id, 'ngg_gallery'); 
  608.  
  609. //and give me the new id 
  610. return $imageID; 
  611.  
  612. /** 
  613. * Add an album to the database 
  614. * @since V1.7.0 
  615. * @param (optional) string $title 
  616. * @param (optional) int $previewpic 
  617. * @param (optional) string $description 
  618. * @param (optional) serialized array $sortorder 
  619. * @param (optional) int $pageid 
  620. * @return bool result of the ID of the inserted album 
  621. */ 
  622. static function add_album( $name = false, $previewpic = 0, $description = '', $sortorder = 0, $pageid = 0 ) { 
  623. global $wpdb; 
  624.  
  625. // name must be unique, we use the title for that 
  626. $slug = nggdb::get_unique_slug( sanitize_title( $name ), 'album' ); 
  627.  
  628. // Add the album 
  629. if ( false === $wpdb->query( $wpdb->prepare("INSERT INTO $wpdb->nggalbum (name, slug, previewpic, albumdesc, sortorder, pageid) 
  630. VALUES (%s, %s, %d, %s, %s, %d)", $name, $slug, $previewpic, $description, $sortorder, $pageid ) ) ) { 
  631. return false; 
  632.  
  633. $albumID = (int) $wpdb->insert_id; 
  634.  
  635. //and give me the new id 
  636. return $albumID; 
  637.  
  638. /** 
  639. * Add an gallery to the database 
  640. * @since V1.7.0 
  641. * @param (optional) string $title or name of the gallery 
  642. * @param (optional) string $path 
  643. * @param (optional) string $description 
  644. * @param (optional) int $pageid 
  645. * @param (optional) int $previewpic 
  646. * @param (optional) int $author 
  647. * @return bool|int result of the ID of the inserted gallery 
  648. */ 
  649. static function add_gallery( $title = '', $path = '', $description = '', $pageid = 0, $previewpic = 0, $author = 0 ) { 
  650. global $wpdb; 
  651.  
  652. // slug must be unique, we use the title for that 
  653. $slug = nggdb::get_unique_slug( sanitize_title( $title ), 'gallery' ); 
  654.  
  655. // Note : The field 'name' is deprecated, it's currently kept only for compat reason with older shortcodes, we copy the slug into this field 
  656. if ( false === $wpdb->query( $wpdb->prepare("INSERT INTO $wpdb->nggallery (name, slug, path, title, galdesc, pageid, previewpic, author) 
  657. VALUES (%s, %s, %s, %s, %s, %d, %d, %d)", $slug, $slug, $path, $title, $description, $pageid, $previewpic, $author ) ) ) { 
  658. return false; 
  659.  
  660. $galleryID = (int) $wpdb->insert_id; 
  661.  
  662. //and give me the new id 
  663. return $galleryID; 
  664.  
  665. /** 
  666. * Delete an image entry from the database 
  667. * @param integer $id is the Image ID 
  668. */ 
  669. static function delete_image( $id ) { 
  670. global $wpdb; 
  671.  
  672. // Delete the image 
  673. $result = $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->nggpictures WHERE pid = %d", $id) ); 
  674.  
  675. // Delete tag references 
  676. wp_delete_object_term_relationships( $id, 'ngg_tag'); 
  677.  
  678. // Remove from cache 
  679. wp_cache_delete( $id, 'ngg_image'); 
  680.  
  681. return $result; 
  682.  
  683. /** 
  684. * Get the last images registered in the database with a maximum number of $limit results 
  685. * @param integer $page start offset as page number (0, 1, 2, 3, 4...) 
  686. * @param integer $limit the number of result 
  687. * @param bool $exclude do not show exluded images 
  688. * @param int $galleryId Only look for images with this gallery id, or in all galleries if id is 0 
  689. * @param string $orderby is one of "id" (default, order by pid), "date" (order by exif date), sort (order by user sort order) 
  690. * @return 
  691. */ 
  692. static function find_last_images($page = 0, $limit = 30, $exclude = true, $galleryId = 0, $orderby = "id") { 
  693. global $wpdb; 
  694.  
  695. // Check for the exclude setting 
  696. $exclude_clause = ($exclude) ? ' AND exclude<>1 ' : ''; 
  697.  
  698. // a limit of 0 makes no sense 
  699. $limit = ($limit == 0) ? 30 : $limit; 
  700. // calculate the offset based on the pagr number 
  701. $offset = (int) $page * $limit; 
  702.  
  703. $galleryId = (int) $galleryId; 
  704. $gallery_clause = ($galleryId === 0) ? '' : ' AND galleryid = ' . $galleryId . ' '; 
  705.  
  706. // default order by pid 
  707. $order = 'pid DESC'; 
  708. switch ($orderby) { 
  709. case 'date': 
  710. $order = 'imagedate DESC'; 
  711. break; 
  712. case 'sort': 
  713. $order = 'sortorder ASC'; 
  714. break; 
  715.  
  716. $result = array(); 
  717. $gallery_cache = array(); 
  718.  
  719. // Query database 
  720. $images = $wpdb->get_results("SELECT * FROM $wpdb->nggpictures WHERE 1=1 $exclude_clause $gallery_clause ORDER BY $order LIMIT $offset, $limit"); 
  721.  
  722. // Build the object from the query result 
  723. if ($images) { 
  724. foreach ($images as $key => $image) { 
  725.  
  726. // cache a gallery , so we didn't need to lookup twice 
  727. if (!array_key_exists($image->galleryid, $gallery_cache)) 
  728. $gallery_cache[$image->galleryid] = nggdb::find_gallery($image->galleryid); 
  729.  
  730. // Join gallery information with picture information 
  731. foreach ($gallery_cache[$image->galleryid] as $index => $value) 
  732. $image->$index = $value; 
  733.  
  734. // Now get the complete image data 
  735. $result[$key] = new nggImage( $image ); 
  736.  
  737. return $result; 
  738.  
  739. /** 
  740. * nggdb::get_random_images() - Get an random image from one ore more gally 
  741. * @param integer $number of images 
  742. * @param integer $galleryID optional a Gallery 
  743. * @return A nggImage object representing the image (null if not found) 
  744. */ 
  745. static function get_random_images($number = 1, $galleryID = 0) { 
  746. global $wpdb; 
  747.  
  748. $number = (int) $number; 
  749. $galleryID = (int) $galleryID; 
  750. $images = array(); 
  751.  
  752. // Query database 
  753. if ($galleryID == 0) 
  754. $result = $wpdb->get_results("SELECT t.*, tt.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE tt.exclude != 1 ORDER by rand() limit $number"); 
  755. else 
  756. $result = $wpdb->get_results("SELECT t.*, tt.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE t.gid = $galleryID AND tt.exclude != 1 ORDER by rand() limit {$number}"); 
  757.  
  758. // Return the object from the query result 
  759. if ($result) { 
  760. foreach ($result as $image) { 
  761. $images[] = new nggImage( $image ); 
  762. return $images; 
  763.  
  764. return null; 
  765.  
  766. /** 
  767. * Get all the images from a given album 
  768. * @param object|int $album The album object or the id 
  769. * @param string $order_by 
  770. * @param string $order_dir 
  771. * @param bool $exclude 
  772. * @return An array containing the nggImage objects representing the images in the album. 
  773. */ 
  774. static function find_images_in_album($album, $order_by = 'galleryid', $order_dir = 'ASC', $exclude = true) { 
  775. global $wpdb; 
  776.  
  777. if ( !is_object($album) ) 
  778. $album = nggdb::find_album( $album ); 
  779.  
  780. // Get gallery list 
  781. $gallery_list = implode(', ', $album->gallery_ids); 
  782. // Check for the exclude setting 
  783. $exclude_clause = ($exclude) ? ' AND tt.exclude<>1 ' : ''; 
  784.  
  785. // Say no to any other value 
  786. $order_dir = ( $order_dir == 'DESC') ? 'DESC' : 'ASC'; 
  787. $order_by = ( empty($order_by) ) ? 'galleryid' : $order_by; 
  788.  
  789. $result = $wpdb->get_results("SELECT t.*, tt.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE tt.galleryid IN ($gallery_list) $exclude_clause ORDER BY tt.$order_by $order_dir"); 
  790. // Return the object from the query result 
  791. if ($result) { 
  792. foreach ($result as $image) { 
  793. $images[] = new nggImage( $image ); 
  794. return $images; 
  795.  
  796. return null; 
  797.  
  798. /** 
  799. * search for images and return the result 
  800. * @since 1.3.0 
  801. * @param string $request 
  802. * @param int $limit number of results, 0 shows all results 
  803. * @return Array Result of the request 
  804. */ 
  805. function search_for_images( $request, $limit = 0 ) { 
  806. global $wpdb; 
  807.  
  808. // If a search pattern is specified, load the posts that match 
  809. if ( !empty($request) ) { 
  810. // added slashes screw with quote grouping when done early, so done later 
  811. $request = stripslashes($request); 
  812.  
  813. // split the words it a array if separated by a space or comma 
  814. preg_match_all('/".*?("|$)|((?<=[\\s", +])|^)[^\\s", +]+/', $request, $matches); 
  815. $search_terms = array_map(create_function('$a', 'return trim($a, "\\"\'\\n\\r ");'), $matches[0]); 
  816.  
  817. $n = '%'; 
  818. $searchand = ''; 
  819. $search = ''; 
  820.  
  821. foreach( (array) $search_terms as $term) { 
  822. $term = addslashes_gpc($term); 
  823. $search .= "{$searchand}((tt.description LIKE '{$n}{$term}{$n}') OR (tt.alttext LIKE '{$n}{$term}{$n}') OR (tt.filename LIKE '{$n}{$term}{$n}'))"; 
  824. $searchand = ' AND '; 
  825.  
  826. $term = esc_sql($request); 
  827. if (count($search_terms) > 1 && $search_terms[0] != $request ) 
  828. $search .= " OR (tt.description LIKE '{$n}{$term}{$n}') OR (tt.alttext LIKE '{$n}{$term}{$n}') OR (tt.filename LIKE '{$n}{$term}{$n}')"; 
  829.  
  830. if ( !empty($search) ) 
  831. $search = " AND ({$search}) "; 
  832.  
  833. $limit_by = ( $limit > 0 ) ? 'LIMIT ' . intval($limit) : ''; 
  834. } else 
  835. return false; 
  836.  
  837. // build the final query 
  838. $query = "SELECT t.*, tt.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE 1=1 $search ORDER BY tt.pid ASC $limit_by"; 
  839. $result = $wpdb->get_results($query); 
  840.  
  841. // TODO: Currently we didn't support a proper pagination 
  842. $this->paged['total_objects'] = $this->paged['objects_per_page'] = intval ( $wpdb->get_var( "SELECT FOUND_ROWS()" ) ); 
  843. $this->paged['max_objects_per_page'] = 1; 
  844.  
  845. // Return the object from the query result 
  846. if ($result) { 
  847. foreach ($result as $image) { 
  848. $images[] = new nggImage( $image ); 
  849. return $images; 
  850.  
  851. return null; 
  852.  
  853. /** 
  854. * search for galleries and return the result 
  855. * @since 1.7.0 
  856. * @param string $request 
  857. * @param int $limit number of results, 0 shows all results 
  858. * @return Array Result of the request 
  859. */ 
  860. function search_for_galleries( $request, $limit = 0 ) { 
  861. global $wpdb; 
  862.  
  863. // If a search pattern is specified, load the posts that match 
  864. if ( !empty($request) ) { 
  865. // added slashes screw with quote grouping when done early, so done later 
  866. $request = stripslashes($request); 
  867.  
  868. // split the words it a array if separated by a space or comma 
  869. preg_match_all('/".*?("|$)|((?<=[\\s", +])|^)[^\\s", +]+/', $request, $matches); 
  870. $search_terms = array_map(create_function('$a', 'return trim($a, "\\"\'\\n\\r ");'), $matches[0]); 
  871.  
  872. $n = '%'; 
  873. $searchand = ''; 
  874. $search = ''; 
  875.  
  876. foreach( (array) $search_terms as $term) { 
  877. $term = addslashes_gpc($term); 
  878. $search .= "{$searchand}((title LIKE '{$n}{$term}{$n}') OR (name LIKE '{$n}{$term}{$n}') )"; 
  879. $searchand = ' AND '; 
  880.  
  881. $term = esc_sql($request); 
  882. if (count($search_terms) > 1 && $search_terms[0] != $request ) 
  883. $search .= " OR (title LIKE '{$n}{$term}{$n}') OR (name LIKE '{$n}{$term}{$n}')"; 
  884.  
  885. if ( !empty($search) ) 
  886. $search = " AND ({$search}) "; 
  887.  
  888. $limit = ( $limit > 0 ) ? 'LIMIT ' . intval($limit) : ''; 
  889. } else 
  890. return false; 
  891.  
  892. // build the final query 
  893. $query = "SELECT * FROM $wpdb->nggallery WHERE 1=1 $search ORDER BY title ASC $limit"; 
  894. $result = $wpdb->get_results($query); 
  895.  
  896. return $result; 
  897.  
  898. /** 
  899. * search for albums and return the result 
  900. * @since 1.7.0 
  901. * @param string $request 
  902. * @param int $limit number of results, 0 shows all results 
  903. * @return Array Result of the request 
  904. */ 
  905. function search_for_albums( $request, $limit = 0 ) { 
  906. global $wpdb; 
  907.  
  908. // If a search pattern is specified, load the posts that match 
  909. if ( !empty($request) ) { 
  910. // added slashes screw with quote grouping when done early, so done later 
  911. $request = stripslashes($request); 
  912.  
  913. // split the words it a array if separated by a space or comma 
  914. preg_match_all('/".*?("|$)|((?<=[\\s", +])|^)[^\\s", +]+/', $request, $matches); 
  915. $search_terms = array_map(create_function('$a', 'return trim($a, "\\"\'\\n\\r ");'), $matches[0]); 
  916.  
  917. $n = '%'; 
  918. $searchand = ''; 
  919. $search = ''; 
  920.  
  921. foreach( (array) $search_terms as $term) { 
  922. $term = addslashes_gpc($term); 
  923. $search .= "{$searchand}(name LIKE '{$n}{$term}{$n}')"; 
  924. $searchand = ' AND '; 
  925.  
  926. $term = esc_sql($request); 
  927. if (count($search_terms) > 1 && $search_terms[0] != $request ) 
  928. $search .= " OR (name LIKE '{$n}{$term}{$n}')"; 
  929.  
  930. if ( !empty($search) ) 
  931. $search = " AND ({$search}) "; 
  932.  
  933. $limit = ( $limit > 0 ) ? 'LIMIT ' . intval($limit) : ''; 
  934. } else 
  935. return false; 
  936.  
  937. // build the final query 
  938. $query = "SELECT * FROM $wpdb->nggalbum WHERE 1=1 $search ORDER BY name ASC $limit"; 
  939. $result = $wpdb->get_results($query); 
  940.  
  941. return $result; 
  942.  
  943. /** 
  944. * search for a filename 
  945. * @since 1.4.0 
  946. * @param string $filename 
  947. * @param int (optional) $galleryID 
  948. * @return Array Result of the request 
  949. */ 
  950. function search_for_file( $filename, $galleryID = false ) { 
  951. global $wpdb; 
  952.  
  953. // If a search pattern is specified, load the posts that match 
  954. if ( !empty($filename) ) { 
  955. // added slashes screw with quote grouping when done early, so done later 
  956. $term = esc_sql($filename); 
  957.  
  958. $where_clause = ''; 
  959. if ( is_numeric($galleryID) ) { 
  960. $id = (int) $galleryID; 
  961. $where_clause = " AND tt.galleryid = {$id}"; 
  962.  
  963. // build the final query 
  964. $query = "SELECT t.*, tt.* FROM $wpdb->nggallery AS t INNER JOIN $wpdb->nggpictures AS tt ON t.gid = tt.galleryid WHERE tt.filename = '{$term}' {$where_clause} ORDER BY tt.pid ASC "; 
  965. $result = $wpdb->get_row($query); 
  966.  
  967. // Return the object from the query result 
  968. if ($result) { 
  969. $image = new nggImage( $result ); 
  970. return $image; 
  971.  
  972. return null; 
  973.  
  974.  
  975. /** 
  976. * Update or add meta data for an image 
  977. * @since 1.4.0 
  978. * @param int $id The image ID 
  979. * @param array $values An array with existing or new values 
  980. * @return bool result of query 
  981. */ 
  982. static function update_image_meta( $id, $new_values ) { 
  983. global $wpdb; 
  984.  
  985. // Query database for existing values 
  986. // Use cache object 
  987. $old_values = $wpdb->get_var( $wpdb->prepare( "SELECT meta_data FROM $wpdb->nggpictures WHERE pid = %d ", $id ) ); 
  988. $old_values = unserialize( $old_values ); 
  989.  
  990. $meta = array_merge( (array)$old_values, (array)$new_values ); 
  991.  
  992. $result = $wpdb->query( $wpdb->prepare("UPDATE $wpdb->nggpictures SET meta_data = %s WHERE pid = %d", serialize($meta), $id) ); 
  993.  
  994. wp_cache_delete($id, 'ngg_image'); 
  995.  
  996. return $result; 
  997.  
  998. /** 
  999. * Computes a unique slug for the gallery, album or image, when given the desired slug. 
  1000. * @since 1.7.0 
  1001. * @author taken from WP Core includes/post.php 
  1002. * @param string $slug the desired slug (post_name) 
  1003. * @param string $type ('image', 'album' or 'gallery') 
  1004. * @param int (optional) $id of the object, so that it's not checked against itself 
  1005. * @return string unique slug for the object, based on $slug (with a -1, -2, etc. suffix) 
  1006. */ 
  1007. static function get_unique_slug( $slug, $type, $id = 0 ) { 
  1008.  
  1009. global $wpdb; 
  1010.  
  1011. switch ($type) { 
  1012. case 'image': 
  1013. $check_sql = "SELECT image_slug FROM $wpdb->nggpictures WHERE image_slug = %s AND NOT pid = %d LIMIT 1"; 
  1014. break; 
  1015. case 'album': 
  1016. $check_sql = "SELECT slug FROM $wpdb->nggalbum WHERE slug = %s AND NOT id = %d LIMIT 1"; 
  1017. break; 
  1018. case 'gallery': 
  1019. $check_sql = "SELECT slug FROM $wpdb->nggallery WHERE slug = %s AND NOT gid = %d LIMIT 1"; 
  1020. break; 
  1021. default: 
  1022. return false; 
  1023.  
  1024. //if you didn't give us a name we take the type 
  1025. $slug = empty($slug) ? $type: $slug; 
  1026.  
  1027. // Slugs must be unique across all objects. 
  1028. $slug_check = $wpdb->get_var( $wpdb->prepare( $check_sql, $slug, $id ) ); 
  1029.  
  1030. if ( $slug_check ) { 
  1031. $suffix = 2; 
  1032. do { 
  1033. $alt_name = substr ($slug, 0, 200 - ( strlen( $suffix ) + 1 ) ) . "-$suffix"; 
  1034. $slug_check = $wpdb->get_var( $wpdb->prepare($check_sql, $alt_name, $id ) ); 
  1035. $suffix++; 
  1036. } while ( $slug_check ); 
  1037. $slug = $alt_name; 
  1038.  
  1039. return $slug; 
  1040.