Example #1
0
 /**
  * Return a set of ids based on a set of attribute criteria.
  *
  * @param array $criteria  The array of criteria. Example:
  *                         $criteria['OR'] = array(
  *                                      array('field' => 'name',
  *                                            'op'    => '=',
  *                                            'test'  => 'foo'),
  *                                      array('field' => 'name',
  *                                            'op'    => '=',
  *                                            'test'  => 'bar'));
  *                          This would return all ids for which the field
  *                          attribute_name is either 'foo' or 'bar'.
  */
 function getByAttributes($criteria)
 {
     if (!count($criteria)) {
         return array();
     }
     /* Build the query. */
     $this->_table_count = 1;
     $query = '';
     foreach ($criteria as $key => $vals) {
         if ($key == 'OR' || $key == 'AND') {
             if (!empty($query)) {
                 $query .= ' ' . $key . ' ';
             }
             $query .= '(' . $this->_buildAttributeQuery($key, $vals) . ')';
         }
     }
     /* Build the FROM/JOIN clauses. */
     $joins = array();
     $pairs = array();
     for ($i = 1; $i <= $this->_table_count; $i++) {
         $joins[] = sprintf('LEFT JOIN %1$s a%2$s ON a%2$s.%3$s = m.%3$s', $this->_params['attribute_table'], $i, $this->_params['id_column']);
         $pairs[] = 'AND a1.attribute_name = a' . $i . '.attribute_name';
     }
     $joins = implode(' ', $joins);
     $pairs = implode(' ', $pairs);
     $query = sprintf('SELECT DISTINCT a1.%s FROM %s m %s WHERE %s %s', $this->_params['id_column'], $this->_params['primary_table'], $joins, $query, $pairs);
     Horde::log('SQL Query by Hylax_SQL_Attributes::getByAttributes(): ' . $query, 'DEBUG');
     return $this->_db->getCol($query);
 }
 /**
  * Returns a numerically indexed 1D array of values from the first column
  * 
  * The returned value will be based upon the sql specified by $sql. This is 
  * basically a wrapper for the 
  * {@link http://pear.php.net/manual/en/package.database.db.db-common.getcol.php DB_common::getCol}
  * method of the Pear DB module.
  * 
  * @param string $sql The SQL code to be executed
  * @param array $data If $sql is a paramatised query, then the data for each parameter goes in this array
  * @return array
  * 
  */
 public function getColumn($sql, $col = 0, $data = array())
 {
     $result = $this->conn->getCol($sql, $col, $data);
     self::logQuery();
     if (DB::isError($result)) {
         throw new LoggedException($result->getMessage() . ". SQL: {$sql}", $result->getCode(), self::module);
     }
     return $result;
 }
Example #3
0
 /**
  * Delete a folders contents from the VFS in the SQL database,
  * recursively.
  *
  * @param string $path  The path of the folder.
  * @param string $name  The foldername to use.
  *
  * @throws Horde_Vfs_Exception
  */
 protected function _recursiveSQLDelete($path, $name)
 {
     $result = $this->_db->query(sprintf('DELETE FROM %s WHERE vfs_type = ? AND vfs_path = ?', $this->_params['table']), array(self::FILE, $this->_getSQLNativePath($path, $name)));
     if ($result instanceof PEAR_Error) {
         throw new Horde_Vfs_Exception($result->getMessage());
     }
     $folderList = $this->_db->getCol(sprintf('SELECT vfs_name FROM %s WHERE vfs_type = ? AND vfs_path = ?', $this->_params['table']), 0, array(self::FOLDER, $this->_getSQLNativePath($path, $name)));
     foreach ($folderList as $folder) {
         $this->_recursiveSQLDelete($this->_getSQLNativePath($path, $name), $folder);
     }
     $this->_db->query(sprintf('DELETE FROM %s WHERE vfs_type = ? AND vfs_name = ? AND vfs_path = ?', $this->_params['table']), array(self::FOLDER, $name, $path));
 }
Example #4
0
 public function getAccountCodes()
 {
     /* Make sure we have a valid database connection. */
     $this->_connect();
     $sql = 'SELECT DISTINCT(accountcode) FROM ' . $this->_params['table'] . ' ORDER BY accountcode';
     Horde::log(sprintf('Operator_Driver_asterisksql::getAccountCodes(): %s', $sql), 'DEBUG');
     $res = $this->_db->getCol($sql, 'accountcode');
     if (is_a($res, 'PEAR_Error')) {
         Horde::log($res, 'ERR');
         throw new Operator_Exception(_("Internal error.  Details have been logged for the administrator."));
     }
     return $res;
 }
Example #5
0
 /**
  * Return a set of tag names given the tag_ids.
  *
  * @param array $ids  An array of tag_ids to get names for.
  *
  * @return mixed  An array of tag names | PEAR_Error.
  */
 public function getTagNames($ids)
 {
     if (empty($ids)) {
         return array();
     }
     $sql = 'SELECT t.tag_name FROM jonah_tags as t WHERE t.tag_id IN (' . implode(',', array_map(function ($v) {
         return '?';
     }, $ids)) . ')';
     $tags = $this->_db->getCol($sql, 0, $ids);
     if ($tags instanceof PEAR_Error) {
         throw new Jonah_Exception($tags);
     }
     return $tags;
 }
Example #6
0
 public static function paginator($sql, $klvo = 10, $pnum = 9, $bind_params = [])
 {
     $page = HTTP::$page;
     $layout = $klvo * ($page - 1);
     $sql_num = preg_replace('|SELECT(.*?)FROM|s', "SELECT 1 FROM", $sql);
     $sql = str_replace(";", "", $sql);
     $sql = preg_replace('/LIMIT(.*?)$/Uis', "", $sql);
     $sql .= " LIMIT {$layout}, {$klvo};";
     $rows = DB::getCol($sql_num, $bind_params);
     $num_rows = sizeof($rows);
     unset($rows);
     $pages = ceil($num_rows / $klvo) + 1;
     $url_params = explode("?", $_SERVER['REQUEST_URI']);
     $url = preg_replace('/page-(.*?)$/Uis', "", $url_params[0]);
     if (strpos($url, '/', strlen($url) - 1)) {
         $url = substr($url, 0, -1);
     }
     $pos = floor($pnum / 2);
     //Get pages num before and after current page
     if ($page <= $pos) {
         //if curent page in the begining, crop last page by pages number
         $start_page = 1;
         $end_page = $pages < $pnum ? $pages : $pnum;
     } else {
         $start_page = $page - $pos;
         $end_page = $pages < $start_page + $pnum ? $pages : $start_page + $pnum;
     }
     $tpl = App::get('view');
     $tpl->assign('page_items', $num_rows);
     $tpl->assign('page_from', $layout + 1);
     $tpl->assign('page_to', $layout + $klvo >= $num_rows ? $num_rows : $layout + $klvo);
     if ($pages > 2) {
         $tpl->assign('page_url', $url);
         $tpl->assign('page_url_get', !empty($url_params[1]) ? "?" . $url_params[1] : "");
         $tpl->assign('start_page', $start_page);
         $tpl->assign('end_page', $end_page);
         $tpl->assign('page', $page);
         $tpl->assign('pages', 1);
         $tpl->assign('pages_num', $pages);
     }
     return $sql;
 }
Example #7
0
File: sql.php Project: horde/horde
 /**
  * Get saved search
  *
  * @return array saved searches
  */
 protected function _getSavedSearch()
 {
     $query = 'SELECT search_name FROM ' . $this->_params['search'] . ' WHERE user_uid = ?';
     return $this->_db->getCol($query, 'search_name', $GLOBALS['registry']->getAuth());
 }
Example #8
0
 /**
  *
  */
 private static function loadRights($group_id = false)
 {
     $rights = DB::getCol('SELECT rule FROM users_rules WHERE users_groups_id = ?', [$group_id]);
     return $rights;
 }
Example #9
0
 public function testGetCol()
 {
     $r = DB::getCol('select name from f**k');
     $this->assertTrue((bool) $r === true);
 }
Example #10
0
File: sql.php Project: horde/horde
 /**
  * Get users who have you on friendlist
  *
  * @return array users
  */
 public function friendOf()
 {
     $query = 'SELECT user_uid FROM ' . $this->_params['friends'] . ' WHERE friend_uid = ? AND friend_ask = ?' . ' ORDER BY friend_uid ASC';
     return $this->_db->getCol($query, 0, array($this->_user, 0));
 }