Skip to content

Instantly share code, notes, and snippets.

@bastman

bastman/MySQLIDB Example

Created November 6, 2011 20:06
Show Gist options
  • Save bastman/1343392 to your computer and use it in GitHub Desktop.
Save bastman/1343392 to your computer and use it in GitHub Desktop.
Download ZIP
MySQLi simple dbclient standalone php
Raw
MySQLIDB Example
//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"=>0
);
$rows = $db->fetchAll($sql, $params, false);
var_dump($rows);
// (2) INSERT
// ========
$rowInsert = array(
"id" => null,
"firstname" => "seb",
);
$id=(int)$db->insert("User", $row, true);
var_dump($id);
// (3) UPDATE
// =========
$rowUpdate = array(
"firstname" => "john",
);
$where = "id=:id";
$params = array(
"id"=>1,
);
$affectedRows=(int)$db->update("User", $row, $where, $params);
// (4) DELETE
// =========
$where = "id=:id";
$params = array(
"id"=>20,
);
$affectedRows=(int)$db->delete("User", $row, $where, $params);
var_dump($affectedRows);
// (5) INSERT OR UPDATE
// ==================
// NOTICE: primaryKey for Table Event is (id,userId)
$rowInsert = array(
"userId"=>1,
"eventId" => 1,
"location" => "Berlin",
);
$rowUpdate = array(
"location" => "Berlin",
);
$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);
Raw
MySQLiDB.php
<?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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment