query.php

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· PHP 代码 · 共 1,899 行 · 第 1/5 页

        }
// FIXXME if $ids has no table.col syntax and we are using joins, the table better be put in front!!!
        $ids = $this->_quoteArray($ids);

        $query['where'] = $this->_buildWhere($col.' IN ('.implode(',', $ids).')');
        $queryString    = $this->_buildSelectQuery($query);

        return $this->returnResult($this->execute($queryString));
    }

    // }}}
    // {{{ getAll()

    /**
     * get all entries from the DB
     * for sorting use setOrder!!!, the last 2 parameters are deprecated
     *
     * @param   int     to start from
     * @param   int     the number of rows to show
     * @return  mixed   an array of the retreived data, or false in case of failure
     *                  when failing an error is set in $this->_error
     * @access  public
     */
    function getAll($from=0, $count=0, $method='getAll')
    {
        $query = array();
        if ($count) {
            $query = array('limit' => array($from, $count));
        }
        return $this->returnResult($this->execute($this->_buildSelectQuery($query), $method));
    }

    // }}}
    // {{{ getCol()

    /**
     * this method only returns one column, so the result will be a one dimensional array
     * this does also mean that using setSelect() should be set to *one* column, the one you want to
     * have returned a most common use case for this could be:
     *     $table->setSelect('id');
     *     $ids = $table->getCol();
     * OR
     *     $ids = $table->getCol('id');
     * so ids will be an array with all the id's
     *
     * @param   string  the column that shall be retreived
     * @param   int     to start from
     * @param   int     the number of rows to show
     * @return  mixed   an array of the retreived data, or false in case of failure
     *                  when failing an error is set in $this->_error
     * @access  public
     */
    function getCol($column=null, $from=0, $count=0)
    {
        $query = array();
        if ($column != null) {
            // by using _buildSelect() I can be sure that the table name will not be ambiguous
            // i.e. in a join, where all the joined tables have a col 'id'
            // _buildSelect() will put the proper table name in front in case there is none
            $query['select'] = $this->_buildSelect(trim($column));
        }
        if ($count) {
            $query['limit'] = array($from, $count);
        }
        $res = $this->returnResult($this->execute($this->_buildSelectQuery($query), 'getCol'));
        return ($res === false) ? array() : $res;
    }

    // }}}
    // {{{ getCount()

    /**
     * get the number of entries
     *
     * @return  mixed   an array of the retreived data, or false in case of failure
     *                  when failing an error is set in $this->_error
     * @access  public
     */
    function getCount()
    {
/* the following query works on mysql
SELECT count(DISTINCT image.id) FROM image2tree
RIGHT JOIN image ON image.id = image2tree.image_id
the reason why this is needed - i just wanted to get the number of rows that do exist if the result is grouped by image.id
the following query is what i tried first, but that returns the number of rows that have been grouped together
for each image.id
SELECT count(*) FROM image2tree
RIGHT JOIN image ON image.id = image2tree.image_id GROUP BY image.id

so that's why we do the following, i am not sure if that is standard SQL and absolutley correct!!!
*/

//FIXXME see comment above if this is absolutely correct!!!
        if ($group = $this->_buildGroup()) {
            $query['select'] = 'COUNT(DISTINCT '.$group.')';
            $query['group']  = '';
        } else {
            $query['select'] = 'COUNT(*)';
        }

        $query['order'] = '';   // order is not of importance and might freak up the special group-handling up there, since the order-col is not be known
/*# FIXXME use the following line, but watch out, then it has to be used in every method, or this
# value will be used always, simply try calling getCount and getAll afterwards, getAll will return the count :-)
# if getAll doesn't use setSelect!!!
*/
        //$this->setSelect('count(*)');
        $queryString = $this->_buildSelectQuery($query, true);
        return($res=$this->execute($queryString, 'getOne')) ? $res : 0;
    }

    // }}}
    // {{{ getDefaultValues()

    /**
     * return an empty element where all the array elements do already exist
     * corresponding to the columns in the DB
     *
     * @return  array   an empty, or pre-initialized element
     * @access  public
     */
    function getDefaultValues()
    {
        $ret = array();
        // here we read all the columns from the DB and initialize them
        // with '' to prevent PHP-warnings in case we use error_reporting=E_ALL
        foreach ($this->metadata() as $aCol => $x) {
            $ret[$aCol] = '';
        }
        return $ret;
    }

    // }}}
    // {{{ getEmptyElement()

    /**
     * this is just for BC
     * @deprecated
     */
    function getEmptyElement()
    {
        $this->getDefaultValues();
    }

    // }}}
    // {{{ getQueryString()

    /**
     * Render the current query and return it as a string.
     *
     * @return string the current query
     */
    function getQueryString()
    {
        $ret = $this->_buildSelectQuery();
        if (is_string($ret)) {
            $ret = trim($ret);
        }
        return $ret;
    }

    // }}}
    // {{{ save()

    /**
     * save data, calls either update or add
     * if the primaryCol is given in the data this method knows that the
     * data passed to it are meant to be updated (call 'update'), otherwise it will
     * call the method 'add'.
     * If you dont like this behaviour simply stick with the methods 'add'
     * and 'update' and ignore this one here.
     * This method is very useful when you have validation checks that have to
     * be done for both adding and updating, then you can simply overwrite this
     * method and do the checks in here, and both cases will be validated first.
     *
     * @param   array   contains the new data that shall be saved in the DB
     * @return  mixed   the data returned by either add or update-method
     * @access  public
     */
    function save($data)
    {
        if (isset($data[$this->primaryCol]) && $data[$this->primaryCol]) {
            return $this->update($data);
        }
        return $this->add($data);
    }

    // }}}
    // {{{ update()

    /**
     * update the member data of a data set
     *
     * @param   array   contains the new data that shall be saved in the DB
     *                  the id has to be given in the field with the key 'ID'
     * @return  mixed   true on success, or false otherwise
     * @access  public
     */
    function update($newData)
    {
        $query = array();
        // do only set the 'where' part in $query, if a primary column is given
        // if not the default 'where' clause is used
        if (isset($newData[$this->primaryCol])) {
            //$this->_errorSet('Error updating the new member.');
            //return false;
            $query['where'] = $this->primaryCol.'='.$this->_quote($newData[$this->primaryCol]);
        }

        $newData = $this->_checkColumns($newData, 'update');
        $values = array();
        foreach ($newData as $key => $aData) {   // quote the data
            //$values[] = "{$this->table}.$key=" . $this->_quote($aData);
            $values[] = "$key=" . $this->_quote($aData);
        }

        $query['set'] = implode(',', $values);
        $updateString = $this->_buildUpdateQuery($query);
        return $this->execute($updateString, 'query') ? true : false;
    }

    // }}}
    // {{{ add()

    /**
     * add a new member in the DB
     *
     * @param   array   contains the new data that shall be saved in the DB
     * @return  mixed   the inserted id on success, or false otherwise
     * @access  public
     */
    function add($newData)
    {
        // if no primary col is given, get next sequence value
        if (empty($newData[$this->primaryCol])) {
            if ($this->primaryCol) {
                // do only use the sequence if a primary column is given
                // otherwise the data are written as given
                $id = (int)$this->db->nextId($this->sequenceName);
                $newData[$this->primaryCol] = $id;
            } else {
                // if no primary col is given return true on success
                $id = true;
            }
        } else {
            $id = $newData[$this->primaryCol];
        }

        //unset($newData[$this->primaryCol]);

        $newData = $this->_checkColumns($newData, 'add');
        $newData = $this->_quoteArray($newData);

        $query = sprintf('INSERT INTO %s (%s) VALUES (%s)',
            $this->table,
            implode(', ', array_keys($newData)),
            implode(', ', $newData)
        );
        return $this->execute($query, 'query') ? $id : false;
    }

    // }}}
    // {{{ addMultiple()

    /**
     * adds multiple new members in the DB
     *
     * @param   array   contains an array of new data that shall be saved in the DB
     *                  the key-value pairs have to be the same for all the data!!!
     * @return  mixed   the inserted ids on success, or false otherwise
     * @access  public
     */
    function addMultiple($data)
    {
        if (!sizeof($data)) {
            return false;
        }
        // the inserted ids which will be returned or if no primaryCol is given
        // we return true by default
        $retIds = $this->primaryCol ? array() : true;
        $allData = array();                     // each row that will be inserted
        foreach ($data as $key => $aData) {
            $aData = $this->_checkColumns($aData,'add');
            $aData = $this->_quoteArray($aData);

            if (empty($aData[$this->primaryCol])) {
                if ($this->primaryCol) {
                    // do only use the sequence if a primary column is given
                    // otherwise the data are written as given
                    $retIds[] = $id = (int)$this->db->nextId($this->sequenceName);
                    $aData[$this->primaryCol] = $id;
                }
            } else {
                $retIds[] = $aData[$this->primaryCol];
            }
            $allData[] = '('.implode(', ', $aData).')';
        }

        $query = sprintf( 'INSERT INTO %s (%s) VALUES %s',
                          $this->table ,
                          implode(', ', array_keys($aData)) ,
                          implode(', ', $allData)
                        );
        return $this->execute($query, 'query') ? $retIds : false;
    }

    // }}}
    // {{{ remove()

    /**
     * removes a member from the DB
     *
     * @param   mixed   integer/string - the value of the column that shall be removed
     *                  array   - multiple columns that shall be matched, the second parameter will be ignored
     * @param   string  the column to match the data against, only if $data is not an array
     * @return  boolean
     * @access  public
     */
    function remove($data, $whereCol='')
    {
        //$raw = $this->getOption('raw');

        if (is_array($data)) {
//FIXXME check $data if it only contains columns that really exist in the table
            $wheres = array();
            foreach ($data as $key => $val) {
                $wheres[] = $key .'='. $this->_quote($val);
            }
            $whereClause = implode(' AND ', $wheres);
        } else {
            if ($whereCol=='') {
                $whereCol = $this->primaryCol;
            }
            $whereClause = $whereCol.'='. $this->_quote($data);
        }

        $query = sprintf( 'DELETE FROM %s WHERE %s',
                          $this->table,
                          $whereClause
                          );
        return $this->execute($query, 'query') ? true : false;
// i think this method should return the ID's that it removed, this way we could simply use the result
// for further actions that depend on those id ... or? make stuff easier, see ignaz::imail::remove
    }

    // }}}
    // {{{ removeAll()

    /**
     * empty a table
     *
     * @return resultSet or false on error [execute() result]
     * @access public
     */
    function removeAll()
    {
        $query = 'DELETE FROM ' . $this->table;
        return $this->execute($query, 'query') ? true : false;
    }

    // }}}
    // {{{ removeMultiple()

    /**
     * remove the datasets with the given ids
     *
     * @param  array   the ids to remove
     * @return resultSet or false on error [execute() result]
     * @access public
     */
    function removeMultiple($ids, $colName='')
    {
        if ($colName == '') {
            $colName = $this->primaryCol;
        }
        $ids = $this->_quoteArray($ids);

        $query = sprintf(
            'DELETE FROM %s WHERE %s IN (%s)',
            $this->table,
            $colName,