Skip to content

Instantly share code, notes, and snippets.

@bastman

bastman/MySQLIDB Example

Created November 6, 2011 20:06
Download ZIP

Revisions

  1. bastman revised this gist Nov 11, 2011. 1 changed file with 24 additions and 26 deletions.
    50 changes: 24 additions & 26 deletions MySQLIDB Example
    View file
    Original file line number Diff line number Diff line change
    @@ -17,29 +17,7 @@ $params = array(
    $rows = $db->fetchAll($sql, $params, false);
    var_dump($rows);

    //(2) SELECT WITH QUALIFIED COLUMN NAMES
    //====================================
    // NOTICE: primaryKey for Table Event is (id,userId)
    $sql = "
    SELECT
    User.*,
    Event.*
    FROM User
    LEFT JOIN Event
    ON (User.id=Event.userId)

    WHERE
    User.id>:id
    AND Event.id>:eventId
    ";
    $params = array(
    "id"=>0,
    "eventId" => 0,
    );
    $rows = $db->fetchAll($sql, $params, true);
    var_dump($rows);

    // (3) INSERT
    // (2) INSERT
    // ========

    $rowInsert = array(
    @@ -50,7 +28,7 @@ $id=(int)$db->insert("User", $row, true);
    var_dump($id);


    // (4) UPDATE
    // (3) UPDATE
    // =========
    $rowUpdate = array(
    "firstname" => "john",
    @@ -61,7 +39,7 @@ $params = array(
    );
    $affectedRows=(int)$db->update("User", $row, $where, $params);

    // (5) DELETE
    // (4) DELETE
    // =========
    $where = "id=:id";
    $params = array(
    @@ -70,7 +48,7 @@ $params = array(
    $affectedRows=(int)$db->delete("User", $row, $where, $params);
    var_dump($affectedRows);

    // (6) INSERT OR UPDATE
    // (5) INSERT OR UPDATE
    // ==================
    // NOTICE: primaryKey for Table Event is (id,userId)
    $rowInsert = array(
    @@ -85,5 +63,25 @@ $rowUpdate = array(
    $affectedRows=(int)$db->insertOnDuplicateKeyUpdateRow("Event", $rowInsert, $rowUpdate);
    var_dump($affectedRows);

    //(6) SELECT WITH QUALIFIED COLUMN NAMES
    //======================================
    // NOTICE: primaryKey for Table Event is (id,userId)
    $sql = "
    SELECT
    User.*,
    Event.*
    FROM User
    LEFT JOIN Event
    ON (User.id=Event.userId)

    WHERE
    User.id>:id
    AND Event.id>:eventId
    ";
    $params = array(
    "id"=>0,
    "eventId" => 0,
    );
    $rows = $db->fetchAll($sql, $params, true);
    var_dump($rows);

  2. bastman revised this gist Nov 11, 2011. 1 changed file with 6 additions and 4 deletions.
    10 changes: 6 additions & 4 deletions MySQLIDB Example
    View file
    Original file line number Diff line number Diff line change
    @@ -12,14 +12,14 @@ $db = MySQLiDB::getInstance();
    //=================
    $sql = "SELECT * FROM User WHERE id>:id";
    $params = array(
    "id"=>10
    "id"=>0
    );
    $rows = $db->fetchAll($sql, $params, false);
    var_dump($rows);

    //(2) SELECT WITH QUALIFIED COLUMN NAMES
    //====================================

    // NOTICE: primaryKey for Table Event is (id,userId)
    $sql = "
    SELECT
    User.*,
    @@ -33,7 +33,7 @@ User.id>:id
    AND Event.id>:eventId
    ";
    $params = array(
    "id"=>10,
    "id"=>0,
    "eventId" => 0,
    );
    $rows = $db->fetchAll($sql, $params, true);
    @@ -68,12 +68,14 @@ $params = array(
    "id"=>20,
    );
    $affectedRows=(int)$db->delete("User", $row, $where, $params);
    var_dump($affectedRows);

    // (6) INSERT OR UPDATE
    // ==================

    // NOTICE: primaryKey for Table Event is (id,userId)
    $rowInsert = array(
    "userId"=>1,
    "eventId" => 1,
    "location" => "Berlin",
    );
    $rowUpdate = array(
  3. bastman revised this gist Nov 11, 2011. 1 changed file with 87 additions and 0 deletions.
    87 changes: 87 additions & 0 deletions MySQLIDB Example
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,87 @@
    //Example
    //=======
    ini_set("display_errors", true);
    error_reporting(E_ALL|E_STRICT & ~E_NOTICE);
    set_error_handler(function($errno, $errstr, $errfile, $errline){
    throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
    });

    $db = MySQLiDB::getInstance();

    // (1) SELECT SIMPLE
    //=================
    $sql = "SELECT * FROM User WHERE id>:id";
    $params = array(
    "id"=>10
    );
    $rows = $db->fetchAll($sql, $params, false);
    var_dump($rows);

    //(2) SELECT WITH QUALIFIED COLUMN NAMES
    //====================================

    $sql = "
    SELECT
    User.*,
    Event.*
    FROM User
    LEFT JOIN Event
    ON (User.id=Event.userId)

    WHERE
    User.id>:id
    AND Event.id>:eventId
    ";
    $params = array(
    "id"=>10,
    "eventId" => 0,
    );
    $rows = $db->fetchAll($sql, $params, true);
    var_dump($rows);

    // (3) INSERT
    // ========

    $rowInsert = array(
    "id" => null,
    "firstname" => "seb",
    );
    $id=(int)$db->insert("User", $row, true);
    var_dump($id);


    // (4) UPDATE
    // =========
    $rowUpdate = array(
    "firstname" => "john",
    );
    $where = "id=:id";
    $params = array(
    "id"=>1,
    );
    $affectedRows=(int)$db->update("User", $row, $where, $params);

    // (5) DELETE
    // =========
    $where = "id=:id";
    $params = array(
    "id"=>20,
    );
    $affectedRows=(int)$db->delete("User", $row, $where, $params);

    // (6) INSERT OR UPDATE
    // ==================

    $rowInsert = array(
    "userId"=>1,
    "location" => "Berlin",
    );
    $rowUpdate = array(
    "location" => "Berlin",
    );

    $affectedRows=(int)$db->insertOnDuplicateKeyUpdateRow("Event", $rowInsert, $rowUpdate);
    var_dump($affectedRows);



  4. bastman created this gist Nov 6, 2011.
    1,108 changes: 1,108 additions & 0 deletions MySQLiDB.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,1108 @@
    <?php
    /**
    * Created by JetBrains PhpStorm.
    * User: seb
    * Date: 06.11.11
    * Time: 09:41
    * To change this template use File | Settings | File Templates.
    */
    class MySQLiDB
    {



    /*
    RECOMMENDED SETUP
    =================
    ini_set("display_errors", true);
    error_reporting(E_ALL|E_STRICT & ~E_NOTICE);
    set_error_handler(function($errno, $errstr, $errfile, $errline){
    throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
    });
    */

    /**
    * @var array
    */
    protected $_config = array(
    "host" => "localhost",
    "port" => "3306",
    "username" => "root",
    "password" => "",
    "database" => "foo",
    "socket" => null,
    "charset" => "utf8",
    );


    /**
    * @var MySQLi
    */
    protected $_mysqli;


    protected $_namedParametersEnabled = true;

    /**
    * @var MySQLiDB
    */
    private static $_instance;

    /**
    * @static
    * @return MySQLiDB
    */
    public static function getInstance()
    {
    if(!(self::$_instance instanceof self)) {
    self::$_instance = new self();
    }
    return self::$_instance;
    }

    /**
    * @return MySQLi
    * @throws Exception
    */
    public function getMySqli()
    {
    $config = $this->_config;
    if (!($this->_mysqli instanceof MySQLi)) {

    $mysqli = new MySQLi(
    $config["host"],
    $config["username"],
    $config["password"],
    $config["database"],
    $config["port"],
    $config["socket"]
    );
    $this->_mysqli = $mysqli;

    // report all errors as exception (ignore the query index warnings)
    mysqli_report(MYSQLI_REPORT_ALL ^ MYSQLI_REPORT_INDEX);

    $mysqli->select_db($config["database"]);
    $mysqli->set_charset($config["charset"]);
    if ($mysqli->character_set_name() !== $config["charset"]) {
    throw new Exception("set charset failed");
    // you may need real_connect instead of connect (?)
    }


    }

    $mysqli = $this->_mysqli;
    $mysqli->query("SET NAMES '".$config["charset"]."'");
    return $this->_mysqli;
    }




    /**
    * @var Mysqli_Stmt|null
    */
    protected $_lastStatement;
    /**
    * @return Mysqli_Stmt|null
    */
    public function getLastStatement() {
    return $this->_lastStatement;
    }
    /**
    * @return bool
    */
    public function hasLastStatement()
    {
    return ($this->_lastStatement instanceof MySqli_Stmt);
    }


    public function fetchAll($sql, $params, $qualifiedColumnNames)
    {
    $this->_lastStatement = null;


    $mysqli = $this->getMySqli();

    $sqlQuery = $sql;
    $sqlParams = $params;
    if($this->_namedParametersEnabled===true) {
    $sqlMeta = $this->parseSqlNamedToPositionalParameters($sql, $params);
    $sqlQuery = $sqlMeta["sqlParsed"];
    $sqlParams = $sqlMeta["paramsPositional"];

    }
    $stmt = $mysqli->prepare($sqlQuery);
    if(!($stmt instanceof MySqli_Stmt)) {
    throw new Exception("Prepare statement failed");
    }
    $this->_lastStatement = $stmt;

    $this->_bindParamsAsList($stmt, $sqlParams);

    $stmt->execute();
    $rows = $this->_fetchRows($stmt, null, $qualifiedColumnNames);
    $stmt->free_result();
    return $rows;
    }

    /**
    * @param $sql
    * @param $params
    * @param $qualifiedColumnNames
    * @return array|null
    * @throws Exception
    */
    public function fetchOne($sql, $params, $qualifiedColumnNames)
    {
    $this->_lastStatement = null;

    $row = null;
    $mysqli = $this->getMySqli();

    $sqlQuery = $sql;
    $sqlParams = $params;
    if($this->_namedParametersEnabled===true) {
    $sqlMeta = $this->parseSqlNamedToPositionalParameters($sql, $params);
    $sqlQuery = $sqlMeta["sqlParsed"];
    $sqlParams = $sqlMeta["paramsPositional"];
    }
    $stmt = $mysqli->prepare($sqlQuery);
    if(!($stmt instanceof MySqli_Stmt)) {
    throw new Exception("Prepare statement failed");
    }
    $this->_lastStatement = $stmt;

    $this->_bindParamsAsList($stmt, $sqlParams);

    $stmt->execute();
    $rows = $this->_fetchRows($stmt, 1, $qualifiedColumnNames);
    $stmt->free_result();

    if(array_key_exists(0, $rows)) {
    $row = $rows[0];
    }
    if(!is_array($row)) {
    $row = null;
    }
    return $row;
    }


    /**
    * Inserts a table row with specified data.
    *
    * @param mixed $table The table to insert data into.
    * @param array $bind Column-value pairs.
    * @return int The number of affected rows.
    * @throws Zend_Db_Adapter_Exception
    */
    public function insert($table, array $rowInsert)
    {
    $type = "";
    $result = $this->_insert($type, $table, $rowInsert);
    return $result;
    }

    public function update($table, array $rowUpdate, $where, $params)
    {
    if($params===null) {
    $params = array();
    }


    $bind = $rowUpdate;
    /**
    * Build "col = ?" pairs for the statement,
    * except for Zend_Db_Expr which is treated literally.
    */
    $set = array();

    foreach ($bind as $col => $val) {
    $val = '?';
    $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
    }

    $where = "".$where;

    /**
    * Build the UPDATE statement
    */
    $sql = "UPDATE "
    . $this->quoteIdentifier($table, true)
    . ' SET ' . implode(', ', $set)
    . (($where) ? " WHERE $where" : '');



    $mysqli = $this->getMySqli();

    $sqlQuery = $sql;
    $sqlParams = array_values($rowUpdate);
    $sqlWhereParams = array();
    if($this->_namedParametersEnabled===true) {
    $sqlMeta = $this->parseSqlNamedToPositionalParameters($sql, $params);
    $sqlQuery = $sqlMeta["sqlParsed"];
    $sqlWhereParams = $sqlMeta["paramsPositional"];

    }else {
    $sqlWhereParams = $params;
    }
    foreach($sqlWhereParams as $sqlWhereParamValue) {
    $sqlParams[] = $sqlWhereParamValue;
    }


    $stmt = $mysqli->prepare($sqlQuery);
    if(!($stmt instanceof MySqli_Stmt)) {
    throw new Exception("Prepare statement failed");
    }
    $this->_lastStatement = $stmt;

    $this->_bindParamsAsList($stmt, $sqlParams);

    $stmt->execute();


    $result = $stmt->affected_rows;
    return $result;



    }


    /**
    * Deletes table rows based on a WHERE clause.
    *
    * @param mixed $table The table to update.
    * @param mixed $where DELETE WHERE clause(s).
    * @return int The number of affected rows.
    */
    public function delete($table, $where , $params)
    {
    $where = "".$where;

    if($params===null) {
    $params = array();
    }
    /**
    * Build the DELETE statement
    */
    $sql = "DELETE FROM "
    . $this->quoteIdentifier($table, true)
    . (($where) ? " WHERE $where" : '');

    $mysqli = $this->getMySqli();

    $sqlQuery = $sql;
    $sqlParams = $params;

    if($this->_namedParametersEnabled===true) {
    $sqlMeta = $this->parseSqlNamedToPositionalParameters($sql, $params);
    $sqlQuery = $sqlMeta["sqlParsed"];
    $sqlParams = $sqlMeta["paramsPositional"];

    }


    $stmt = $mysqli->prepare($sqlQuery);
    if(!($stmt instanceof MySqli_Stmt)) {
    throw new Exception("Prepare statement failed");
    }
    $this->_lastStatement = $stmt;

    $this->_bindParamsAsList($stmt, $sqlParams);

    $stmt->execute();


    $result = $stmt->affected_rows;
    return $result;


    }


    /**
    * @param string $type
    * @param string $table
    * @param array $rowInsert
    * @return int
    * @throws Exception
    */
    public function insertOnDuplicateKeyUpdateRow(
    $table, array $rowInsert, array $rowUpdate
    )
    {

    // extract and quote col names from the array keys
    $cols = array();
    $vals = array();
    $i = 0;
    foreach ($rowInsert as $col => $val) {
    $cols[] = $this->quoteIdentifier($col, true);

    $vals[] = '?';

    }


    $set = array();

    foreach ($rowUpdate as $col => $val) {
    $val = '?';
    $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
    }

    // build the statement
    $sql = "INSERT INTO "
    . $this->quoteIdentifier($table, true)
    . ' (' . implode(', ', $cols) . ') '
    . 'VALUES (' . implode(', ', $vals) . ')';
    $sql .= " ON DUPLICATE KEY UPDATE "
    . ' ' . implode(', ', $set);

    // execute the statement and return the number of affected rows

    $bind = array_values($rowInsert);
    $bindUpdate = array_values($rowUpdate);
    foreach($bindUpdate as $value) {
    $bind[] = $value;
    }

    $mysqli = $this->getMySqli();

    $params = $bind;

    $sqlQuery = $sql;
    $sqlParams = $params;


    $stmt = $mysqli->prepare($sqlQuery);
    if(!($stmt instanceof MySqli_Stmt)) {
    throw new Exception("Prepare statement failed");
    }
    $this->_lastStatement = $stmt;

    $this->_bindParamsAsList($stmt, $sqlParams);

    $stmt->execute();

    $result = $stmt->affected_rows;
    return $result;
    }










    /**
    * @param string $type
    * @param string $table
    * @param array $rowInsert
    * @return int
    * @throws Exception
    */
    protected function _insert($type, $table, array $rowInsert)
    {

    $type = "".$type;
    $type = strtoupper($type);
    $types = array("", "REPLACE", "IGNORE");
    if(!in_array($type, $types, true)) {
    throw new Exception("Invalid type");
    }


    $bind = $rowInsert;
    // extract and quote col names from the array keys
    $cols = array();
    $vals = array();
    $i = 0;
    foreach ($bind as $col => $val) {
    $cols[] = $this->quoteIdentifier($col, true);

    $vals[] = '?';

    }

    // build the statement
    $sql = "INSERT ".$type." INTO "
    . $this->quoteIdentifier($table, true)
    . ' (' . implode(', ', $cols) . ') '
    . 'VALUES (' . implode(', ', $vals) . ')';

    // execute the statement and return the number of affected rows

    $bind = array_values($bind);

    $mysqli = $this->getMySqli();

    $params = $bind;

    $sqlQuery = $sql;
    $sqlParams = $params;


    $stmt = $mysqli->prepare($sqlQuery);
    if(!($stmt instanceof MySqli_Stmt)) {
    throw new Exception("Prepare statement failed");
    }
    $this->_lastStatement = $stmt;

    $this->_bindParamsAsList($stmt, $sqlParams);

    $stmt->execute();

    $result = $stmt->affected_rows;
    return $result;
    }




    // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    protected function _bindParamsAsList(
    MySQLi_Stmt $stmt,
    array $params
    )
    {
    if(count($params)<1) {
    return;
    }
    $values = array();
    $types = array();
    foreach($params as $paramValue) {
    if($paramValue === null) {
    $values[] = $paramValue;
    $types[] = "i";
    continue;
    }
    if (is_bool($paramValue)) {
    $values[] = $paramValue;
    $types[] = "i";
    continue;
    }
    if (is_float($paramValue)) {
    $values[] = $paramValue;
    $types[] = "d";
    continue;
    }
    if (is_double($paramValue)) {
    $values[] = $paramValue;
    $types[] = "d";
    continue;
    }
    if (is_int($paramValue)) {
    $values[] = $paramValue;
    $types[] = "i";
    continue;
    }
    if (is_string($paramValue)) {
    $values[] = $paramValue;
    $types[] = "s";
    continue;
    }
    throw new Exception("Invalid param value type");
    }

    $typeString = implode("", $types);


    $args = array($typeString);
    foreach($values as $value) {
    if($value === null) {
    $args[]=null;
    continue;
    }
    $args[] = $value;
    }

    $reflectionClass = new ReflectionClass($stmt);
    $reflectionMethod = $reflectionClass->getMethod("bind_param");
    $methodArgs = array();
    foreach($args as $index => $value) {
    if($index===0) {
    $methodArgs[] = $value;
    continue;
    }
    $methodArgs[]=&$args[$index];
    }
    $reflectionMethod->invokeArgs($stmt, $methodArgs);


    }


    /**
    * @param MySQLi_Stmt $stmt
    * @param int|null $maxRowsCount
    * @param bool $qualifiedColumnNames
    * @return array
    * @throws Exception
    */
    protected function _fetchRows(
    MySQLi_Stmt $stmt,
    $maxRowsCount,
    $qualifiedColumnNames
    ) {

    if (!is_bool($qualifiedColumnNames)) {
    throw new Exception("Invalid parameter 'qualifiedColumnNames'");
    }
    if ($maxRowsCount !== null) {
    if (!(is_int($maxRowsCount) && ($maxRowsCount>0))) {
    throw new Exception("Invalid parameter 'maxRowsCount'");
    }
    }

    $useFetchMethodAll = true;

    $useFetchMethodAll = ($maxRowsCount>10)
    || ($maxRowsCount!==null && $maxRowsCount<10000);

    if ($qualifiedColumnNames) {
    $rows = $this->_fetchRowsQualified($stmt, $maxRowsCount, $useFetchMethodAll);
    return $rows;
    }

    $rows = $this->_fetchRowsUnqualified($stmt, $maxRowsCount, $useFetchMethodAll);
    return $rows;

    }

    /**
    * @param MySQLi_Stmt $stmt
    * @param int|null $maxRowsCount
    * @return array
    * @throws Exception
    */
    protected function _fetchRowsUnqualified(
    MySQLi_Stmt $stmt,
    $maxRowsCount,
    $useFetchMethodAll
    ) {

    if ($maxRowsCount !== null) {
    if (!(is_int($maxRowsCount) && ($maxRowsCount>0))) {
    throw new Exception("Invalid parameter 'maxRowsCount'");
    }
    }
    $useFetchMethodAll = ($useFetchMethodAll===true);

    $result = array();


    $resultMetaData = $stmt->result_metadata();
    if(!($resultMetaData instanceof MySqli_Result)) {
    return $result;
    }
    /**
    * @var mysqli_result $stmtResult
    */
    $stmtResult = $stmt->get_result();
    if(!($resultMetaData instanceof MySqli_Result)) {
    return $result;
    }


    if ($useFetchMethodAll) {
    $allRows = $stmtResult->fetch_all(MYSQLI_ASSOC);
    if(!is_array($allRows)) {
    return $result;
    }
    if($maxRowsCount === null) {
    $result = $allRows;
    return $result;
    }

    $rowsCount = 0;
    foreach($allRows as $row) {
    if (($maxRowsCount!==null) && ($rowsCount >= $maxRowsCount)) {
    break;
    }
    $result[] = $row;
    $rowsCount++;
    }
    return $result;
    }

    // method: fetchOne
    $rowsCount = 0;
    while(is_array( ($row = $stmtResult->fetch_assoc()) ))
    {
    if (($maxRowsCount!==null) && ($rowsCount >= $maxRowsCount)) {
    break;
    }
    $result[] = $row;
    $rowsCount++;
    }
    return $result;
    }

    /**
    * @param MySQLi_Stmt $stmt
    * @param int|null $maxRowsCount
    * @return array
    * @throws Exception
    */
    protected function _fetchRowsQualified(
    MySQLi_Stmt $stmt,
    $maxRowsCount,
    $useFetchMethodAll
    ) {


    if ($maxRowsCount !== null) {
    if (!(is_int($maxRowsCount) && ($maxRowsCount>0))) {
    throw new Exception("Invalid parameter 'maxRowsCount'");
    }
    }
    $result = array();
    $useFetchMethodAll = ($useFetchMethodAll===true);

    $resultMetaData = $stmt->result_metadata();
    if(!($resultMetaData instanceof MySqli_Result)) {
    return $result;
    }
    /**
    * @var mysqli_result $stmtResult
    */
    $stmtResult = $stmt->get_result();
    if(!($resultMetaData instanceof MySqli_Result)) {
    return $result;
    }


    $fields = $resultMetaData->fetch_fields();
    foreach($fields as $fieldIndex => $fieldInfo) {
    /**
    * @var stdClass $fieldInfo
    */
    $qualifiedColumnNameParts = array();
    if(!property_exists($fieldInfo, "table")) {
    throw new Exception("Invalid mysql version for qualified column names");
    }
    if (strlen($fieldInfo->table)>0) {
    $qualifiedColumnNameParts = array($fieldInfo->table, $fieldInfo->name);
    } else {
    $qualifiedColumnNameParts = array($fieldInfo->name);
    }
    $fieldInfo->qname = implode(".", $qualifiedColumnNameParts);
    $fields[$fieldIndex] = $fieldInfo;
    }

    if ($useFetchMethodAll) {
    $allRows = $stmtResult->fetch_all(MYSQLI_NUM);
    if(!is_array($allRows)) {
    return $result;
    }


    $rowsCount = 0;
    foreach($allRows as $row) {
    if (($maxRowsCount!==null) && ($rowsCount >= $maxRowsCount)) {
    break;
    }
    $item = array();
    foreach($row as $columnIndex => $value) {
    $columnName = $fields[$columnIndex]->qname;
    $item[$columnName] = $value;
    }
    $result[] = $item;
    $rowsCount++;

    }
    return $result;
    }

    // method: fetchOne
    $rowsCount = 0;
    while(( is_array(
    $row = $stmtResult->fetch_array(MYSQLI_NUM)

    ) ))
    {
    //var_dump($row);continue;
    if (($maxRowsCount!==null) && ($rowsCount >= $maxRowsCount)) {
    break;
    }
    $item = array();
    foreach($row as $columnIndex => $value) {
    $columnName = $fields[$columnIndex]->qname;
    $item[$columnName] = $value;
    }
    $result[] = $item;
    $rowsCount++;
    }
    return $result;
    }








    /**
    * @param $sql
    * @param array $params
    * @return array
    * @throws Exception
    */
    public function parseSqlNamedToPositionalParameters($sql, array $params)
    {

    $result = array(
    "sql" => $sql,
    "params" => $params,
    "sqlParam" => array(),
    "sqlSplit" => array(),
    "bindParam" => array(),
    "sqlParsed" => null,
    "paramsPositional" => null,
    );
    $sql = $this->stripQuoted($sql);
    $paramPositional=array();
    // split into text and params
    $sqlSplit = preg_split('/(\?|\:[a-zA-Z0-9_]+)/',
    $sql, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    // map params
    $sqlParam = array();
    foreach ($sqlSplit as $key => $val) {
    if ($val == '?') {
    /*
    if ($this->_adapter->supportsParameters('positional') === false) {
    throw new Zend_Db_Statement_Exception("Invalid bind-variable position '$val'");
    }
    */
    } else if ($val[0] == ':') {
    /*
    if ($this->_adapter->supportsParameters('named') === false) {
    throw new Zend_Db_Statement_Exception("Invalid bind-variable name '$val'");
    }
    */

    $paramName = substr($val,1);

    if(!array_key_exists($paramName, $params)) {
    throw new Exception("Bound param '$val' does not ex");
    }
    $paramPositional[] = $params[$paramName];

    }
    $sqlParam[] = $val;
    }

    // set up for binding
    $bindParam = array();

    $sqlParsed = preg_replace('/(\?|\:[a-zA-Z0-9_]+)/',
    '?',
    $sql);//, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    $result = array(
    "sql" => $sql,
    "params" => $params,
    "sqlParam" => $sqlParam,
    "sqlSplit" => $sqlSplit,
    "bindParam" => $bindParam,
    "sqlParsed" => $sqlParsed,
    "paramsPositional" => $paramPositional,
    );
    return $result;
    }





    // +++++++++++++++++++++++++++++ zend +++++++++++++++++++++++++++++++++++





    public function parseParameters($sql)
    {

    $result = array(
    "sql" => $sql,
    "sqlParam" => array(),
    "sqlSplit" => array(),
    "bindParam" => array(),
    );
    $sql = $this->stripQuoted($sql);

    // split into text and params
    $sqlSplit = preg_split('/(\?|\:[a-zA-Z0-9_]+)/',
    $sql, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    // map params
    $sqlParam = array();
    foreach ($sqlSplit as $key => $val) {
    if ($val == '?') {
    /*
    if ($this->_adapter->supportsParameters('positional') === false) {
    throw new Zend_Db_Statement_Exception("Invalid bind-variable position '$val'");
    }
    */
    } else if ($val[0] == ':') {
    /*
    if ($this->_adapter->supportsParameters('named') === false) {
    throw new Zend_Db_Statement_Exception("Invalid bind-variable name '$val'");
    }
    */
    }
    $sqlParam[] = $val;
    }

    // set up for binding
    $bindParam = array();


    $result = array(
    "sql" => $sql,
    "sqlParam" => $sqlParam,
    "sqlSplit" => $sqlSplit,
    "bindParam" => $bindParam,
    );
    return $result;
    }



    /**
    * Remove parts of a SQL string that contain quoted strings
    * of values or identifiers.
    *
    * @param string $sql
    * @return string
    */
    public function stripQuoted($sql)
    {
    // get the character for delimited id quotes,
    // this is usually " but in MySQL is `
    $d = $this->quoteIdentifier('a');
    $d = $d[0];

    // get the value used as an escaped delimited id quote,
    // e.g. \" or "" or \`
    $de = $this->quoteIdentifier($d);
    $de = substr($de, 1, 2);
    $de = str_replace('\\', '\\\\', $de);

    // get the character for value quoting
    // this should be '
    $q = $this->quote('a');
    $q = $q[0];

    // get the value used as an escaped quote,
    // e.g. \' or ''
    $qe = $this->quote($q);
    $qe = substr($qe, 1, 2);
    $qe = str_replace('\\', '\\\\', $qe);

    // get a version of the SQL statement with all quoted
    // values and delimited identifiers stripped out
    // remove "foo\"bar"
    $sql = preg_replace("/$q($qe|\\\\{2}|[^$q])*$q/", '', $sql);
    // remove 'foo\'bar'
    if (!empty($q)) {
    $sql = preg_replace("/$q($qe|[^$q])*$q/", '', $sql);
    }

    return $sql;
    }


    /**
    * Quote a raw string.
    *
    * @param mixed $value Raw string
    *
    * @return string Quoted string
    */
    public function quote($value)
    {
    if (is_int($value) || is_float($value)) {
    return $value;
    }
    //$this->_connect();
    return "'" . $this->getMySqli()->real_escape_string($value) . "'";
    }


    /**
    * Returns the symbol the adapter uses for delimiting identifiers.
    *
    * @return string
    */
    public function getQuoteIdentifierSymbol()
    {
    return "`";
    }



    /**
    * Quotes a value and places into a piece of text at a placeholder.
    *
    * The placeholder is a question-mark; all placeholders will be replaced
    * with the quoted value. For example:
    *
    * <code>
    * $text = "WHERE date < ?";
    * $date = "2005-01-02";
    * $safe = $sql->quoteInto($text, $date);
    * // $safe = "WHERE date < '2005-01-02'"
    * </code>
    *
    * @param string $text The text with a placeholder.
    * @param mixed $value The value to quote.
    * @param string $type OPTIONAL SQL datatype
    * @param integer $count OPTIONAL count of placeholders to replace
    * @return string An SQL-safe quoted value placed into the original text.
    */
    public function quoteInto($text, $value, $type = null, $count = null)
    {
    if ($count === null) {
    return str_replace('?', $this->quote($value, $type), $text);
    } else {
    while ($count > 0) {
    if (strpos($text, '?') !== false) {
    $text = substr_replace($text, $this->quote($value, $type), strpos($text, '?'), 1);
    }
    --$count;
    }
    return $text;
    }
    }

    /**
    * Quotes an identifier.
    *
    * Accepts a string representing a qualified indentifier. For Example:
    * <code>
    * $adapter->quoteIdentifier('myschema.mytable')
    * </code>
    * Returns: "myschema"."mytable"
    *
    * Or, an array of one or more identifiers that may form a qualified identifier:
    * <code>
    * $adapter->quoteIdentifier(array('myschema','my.table'))
    * </code>
    * Returns: "myschema"."my.table"
    *
    * The actual quote character surrounding the identifiers may vary depending on
    * the adapter.
    *
    * @param string|array|Zend_Db_Expr $ident The identifier.
    * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
    * @return string The quoted identifier.
    */
    public function quoteIdentifier($ident, $auto=false)
    {
    return $this->_quoteIdentifierAs($ident, null, $auto);
    }



    /**
    * Quote a column identifier and alias.
    *
    * @param string|array|Zend_Db_Expr $ident The identifier or expression.
    * @param string $alias An alias for the column.
    * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
    * @return string The quoted identifier and alias.
    */
    public function quoteColumnAs($ident, $alias, $auto=false)
    {
    return $this->_quoteIdentifierAs($ident, $alias, $auto);
    }

    /**
    * Quote a table identifier and alias.
    *
    * @param string|array|Zend_Db_Expr $ident The identifier or expression.
    * @param string $alias An alias for the table.
    * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
    * @return string The quoted identifier and alias.
    */
    public function quoteTableAs($ident, $alias = null, $auto = false)
    {
    return $this->_quoteIdentifierAs($ident, $alias, $auto);
    }

    /**
    * Quote an identifier and an optional alias.
    *
    * @param string|array|Zend_Db_Expr $ident The identifier or expression.
    * @param string $alias An optional alias.
    * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
    * @param string $as The string to add between the identifier/expression and the alias.
    * @return string The quoted identifier and alias.
    */

    protected function _quoteIdentifierAs($ident, $alias = null, $auto = false, $as = ' AS ')
    {
    //return "`".$ident."`";
    if (is_string($ident)) {
    $ident = explode('.', $ident);
    }
    if (is_array($ident)) {
    $segments = array();
    foreach ($ident as $segment) {

    $segments[] = $this->_quoteIdentifier($segment, $auto);

    }
    if ($alias !== null && end($ident) == $alias) {
    $alias = null;
    }
    $quoted = implode('.', $segments);
    } else {
    $quoted = $this->_quoteIdentifier($ident, $auto);
    }

    if ($alias !== null) {
    $quoted .= $as . $this->_quoteIdentifier($alias, $auto);
    }
    return $quoted;
    }

    /**
    * Quote an identifier.
    *
    * @param string $value The identifier or expression.
    * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
    * @return string The quoted identifier and alias.
    */

    protected function _quoteIdentifier($value, $auto=false)
    {
    $autoQuoteIdentifiers=true;
    if ($auto === false || $autoQuoteIdentifiers === true) {
    $q = $this->getQuoteIdentifierSymbol();
    return ($q . str_replace("$q", "$q$q", $value) . $q);
    }
    return $value;
    }




    }