19
\$\begingroup\$

It's a little bit more code but I wanted to show the full class. I highlight the points I'd like input after the source.

I've cut comments since they where not in English and translated the important ones.

The class is inspired by PearDb (too old) and Zend_DB (to cluttered / unfinished at the time) and is used in a inhouse application. I know it's not ideal to write your own database handler (I'd go so far to say it's pretty pointless).

<?php

class DbStatement {

    private $oStatement;
    private $aFieldnames = array();
    private $aResultRow = array();
    private $aResultSet = array();
    private $bMetadata = true;
    private $bPreparedFetch = false;
    private $iNumRows = false;
    private $sQuery;
    private $aArgs;
    private $fQuerytime;

    /**
     * @throws DbException
     * @param mysqli $oDb Datenbankverbindung
     * @param string $sQuery
     */
    public function __construct(mysqli $oDb, $sQuery) {
        $this->oStatement = $oDb->prepare($sQuery);
        $this->sQuery = $sQuery;
        if($this->oStatement === false) {
            switch($oDb->errno) {
                case 1054:
                    throw new DbNoSuchFieldException($oDb->error, $oDb->errno);
                case 1146:
                    throw new DbNoSuchTableException($oDb->error, $oDb->errno);
                default:
                    throw new DbException(
                        "Prepared Statement could not be created: ".
                        $oDb->error." (".$oDb->errno."). Query was: '$sQuery'",
                        $oDb->errno
                    );
            }
        }
    }

    /**#@+
     *
     * @param mixed $mParams,...
     */

    public function execute() {
        $this->_execute(func_get_args());
        $this->_done();
    }

    /**
     * @return null|bool|int|string|float
     */
    public function getOne() {
        $this->_execute(func_get_args());
        $this->_fetchRow();
        if(isset($this->aResultSet[0][$this->aFieldnames[0]])) {
            return $this->aResultSet[0][$this->aFieldnames[0]];
        }
        return null;
    }

    /**
     * @return array
     */
    public function getCol() {
        $this->_execute(func_get_args());
        $this->_fetchAll();
        $sIndex = $this->aFieldnames[0];
        $aReturn = array();
        foreach($this->aResultSet as $aResultRow) {
            $aReturn[] = $aResultRow[$sIndex];
        }
        return $aReturn;
    }

    /**
     * @return array
     */
    public function getRow() {
        $this->_execute(func_get_args());
        $this->_fetchRow();
        if(isset($this->aResultSet[0])) {
            return $this->aResultSet[0];
        }
        return array();
    }

    /**
     * @return array
     */
    public function getAssoc() {
        $this->_execute(func_get_args());
        $this->_fetchAll();
        if(isset($this->aFieldnames[0]) && isset($this->aFieldnames[1])) {
            $sIndexKey = $this->aFieldnames[0];
            $sIndexValue = $this->aFieldnames[1];
            $aReturn = array();
            foreach($this->aResultSet as $aResultRow) {
                $aReturn[$aResultRow[$sIndexKey]] = $aResultRow[$sIndexValue];
            }
            return $aReturn;
        }
        return array();
    }

    /**
     * @return array
     */
    public function getAll() {
        $this->_execute(func_get_args());
        $this->_fetchAll();
        return $this->aResultSet;
    }
    /**#@-*/

    /**
     * @return false|int
     */
    public function numRows() {
        return $this->iNumRows;
    }

    /**
     * @return int
     */
    public function affectedRows() {
        return $this->oStatement->affected_rows;
    }

    /**
     * @return int
     */
    public function lastInsertId() {
        return $this->oStatement->insert_id;
    }

    public function getLastExecutedQuery() {
        $sReturn = $this->sQuery;
        if($this->aArgs) {
            $sReturn .= "; -- Argumente: ~".implode("~,~", $this->aArgs)."~";
        }
        return $sReturn;
    }

    /**
     * @throws DbException
     *
     * @param array $aArgs
     */
    private function _execute($aArgs) {
        $aArgs = $this->_parseFuncArgs($aArgs);
        $this->aArgs = $aArgs;
        $iArgs = count($aArgs);
        if($iArgs) {
            if($this->oStatement->param_count !== $iArgs ) {
                throw new DbException(
                    "Inserting parameters failed: ".$this->oStatement->param_count.
                    " Parameters expected but ".$iArgs." passed."
                );
            }
            $aRefArgs = array();
            foreach(array_keys($aArgs) as $mIndex) {
                $aRefArgs[$mIndex] = &$aArgs[$mIndex];
            }
            array_unshift($aRefArgs, str_repeat("s", $iArgs));
            // Needs References
            call_user_func_array(array($this->oStatement, "bind_param"), $aRefArgs);
        }
        $bWorked = $this->oStatement->execute();
        if($bWorked === false) {
            $sError = sprintf(
                "Query failed: %s (%s) Query was: '%s'",
                $this->oStatement->error,
                $this->oStatement->errno,
                $this->sQuery
            );
            switch($this->oStatement->errno) {
                case 1062:
                    throw new DbKeyViolationException($sError, $this->oStatement->errno);
                default:
                    throw new DbException($sError, $this->oStatement->errno);
            }

        }

        $this->_prepareFetch();
    }

    private function _prepareFetch() {
        if($this->bMetadata && !$this->bPreparedFetch) {
            $oMeta = $this->oStatement->result_metadata();
            if($oMeta === false) {
                $this->bMetadata = false;
            } else {
                $this->_prepareMetadata($oMeta);

                $this->aResultRow = array_fill(0, count($this->aFieldnames), null);
                // Ugly but 'bind_result' forces you to pass references
                $aRefs = array();
                foreach ($this->aResultRow as $iIndex => &$rmValue) {
                    $aRefs[$iIndex] = &$rmValue;
                }

                call_user_func_array(array($this->oStatement, "bind_result"), $this->aResultRow);
                $this->bPreparedFetch = true;
            }
        }
    }

    /**
     * @param mysqli_result $oMeta
     */
    private function _prepareMetadata(mysqli_result $oMeta) {
        $aFields = $oMeta->fetch_fields();
        foreach($aFields as $oField) {
            $this->aFieldnames[] = $oField->name;
        }
    }

    private function _fetchRow() {
        $this->_fetch(true);
    }

    private function _fetchAll() {
        $this->_fetch(false);
    }

    /*
     * @param bool $bOne One line ?
     */
    private function _fetch($bOne) {
        $this->aResultSet = array();
        if($bOne !== true) {
            $this->oStatement->store_result();
        }
        while($this->oStatement->fetch()) {

            // Deref
            $aTmp = array();
            foreach($this->aResultRow as $mValue) {
                $aTmp[] = $mValue;
            }
            $this->aResultSet[] = array_combine($this->aFieldnames, $aTmp);

            if($bOne === true) {
                break;
            }
        }

        $this->iNumRows = $this->oStatement->num_rows;
        $this->_done();
    }

    private function _done() {
        $this->oStatement->free_result();
    }

    /**
     * @param array $aArgs
     * @return array
     */
    private function _parseFuncArgs($aArgs) {
        if(isset($aArgs[0]) && is_array($aArgs[0])) {
            return $aArgs[0];
        }
        return $aArgs;
    }

}

The ugly bits (I think, that is why we are here I guess), are in _execute and _prepareFetch as the MySQLi API forced us to use references.

The class takes for granted that the passed in MySQLi object is already connected to a database.

If I missed something please let me know.

\$\endgroup\$
  • 3
    \$\begingroup\$ This is one of the most readable bits of PHP I've ever seen, should be even better with the original comments. Maybe you could extract the common massaging before call_user_func_array in _execute and _prepareFetch into a helper? Hmm, not worth it, good enough as-is. \$\endgroup\$ – TryPyPy Jan 21 '11 at 21:14
  • \$\begingroup\$ This is an awesome piece of code. I cant really see much where you can optimize it. One possible addition you could add is the ability to use the class to build your query as well. Something like the CodeIgniter methods of $db->select('id, name')->from(... \$\endgroup\$ – Hailwood Jan 22 '11 at 4:46
  • \$\begingroup\$ One tip to make any code better is to write comments directly in English. This will save a lot of problems in the future. \$\endgroup\$ – rightfold Jan 24 '11 at 23:08
  • \$\begingroup\$ @Time Machine that doesn't work out that well if not everyone on your team is able to understand English (strange, i know, but we are working on it) Thanks :) \$\endgroup\$ – edorian Jan 25 '11 at 8:28
15
\$\begingroup\$

Maybe you could extract the common massaging before call_user_func_array in _execute and _prepareFetch into a helper?

private function _execute($aArgs) {
/// [...]
        $aRefArgs = array();
        foreach(array_keys($aArgs) as $mIndex) {
            $aRefArgs[$mIndex] = &$aArgs[$mIndex];
        }
        array_unshift($aRefArgs, str_repeat("s", $iArgs));
        // Needs References
        call_user_func_array(array($this->oStatement, "bind_param"), $aRefArgs);
///-^ This part...
/// [...]

private function _prepareFetch() {
/// [...]
            $this->aResultRow = array_fill(0, count($this->aFieldnames), null);
            // Ugly but 'bind_result' forces you to pass references
            $aRefs = array();
            foreach ($this->aResultRow as $iIndex => &$rmValue) {
                $aRefs[$iIndex] = &$rmValue;
            }

            call_user_func_array(array($this->oStatement, "bind_result"), $this->aResultRow);
///-^ ... matches this one loosely. 
/// [...]

Probably not worth it, good enough as-is.

This is one of the most readable bits of PHP I've ever seen, should be even better with the original comments.

\$\endgroup\$
8
\$\begingroup\$

While I have not reviewed the private methods closely, I wanted to provide some feedback on several of the public methods.

$dbStatement = new dbStatement($mysqliDb, 'SELECT * FROM users');
echo $dbStatement->numRows(); // false
$users = $dbStatement->getAll(); // Users array
echo $dbStatement->numRows(); // Some int
  1. There seems to be some inconsistency how some methods work. Some methods will trigger the execution of the statement while others will not. As a result, I believe this will result in some unexpected behavior. See above for code example.

  2. You may have good reason for this however I am also curious why numRows() is defaulted to false, while affectedRows() and lastInsertId() are not.

  3. I also noticed that getLastExecutedQuery() returns the query that was passed in to the class regardless of whether or not it was executed. It is prepared in the constructor, but is not executed until the private _execute() method.

The class takes for granted that the passed in mysqli object is already connected to a database.

Lastly, I would recommend ensuring that the class throws an exception or gracefully handles this another way if the mysqli object is not connected to a database.

\$\endgroup\$
  • \$\begingroup\$ It's been some month since I asked the question but nevertheless: Thanks a lot for the feedback! Point 1) and 2) touch upon a point that i never ever considered or tested. Also it seems that i was never used the wrong way. Really great that you caught that, it should at least report this in a way I guess :) - 3) was named wrong and was removed some time ago. ---- The "not connected" problem is handled somewhat gracefully by the execute method as it throws a "not connected" exception when trying to execute the query. Thanks a buch for the feeback! \$\endgroup\$ – edorian Apr 6 '11 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.