面向对象风格的 MySQLi
程序员文章站
2022-05-06 20:45:32
...
使用方法:
$database = new database_mysqli("localhost", "username", "password", "databasename"); //show full results - single query $sql = "SHOW TABLES"; $tables = $database->query($sql); //execute multiple queries $sql = array ("SELECT * FROM blogs WHERE userId = 2", "SELECT * FROM comments WHERE userId = 2"); $results = $database->query($sql, "", true, true); //execute query, dont care about results $sql = "DELETE FROM blogs WHERE userID = 2"; $database->justquery($sql); //show first row only $sql = "SHOW TABLES"; $firstTable = $database->loadFirstRow($sql); //show first element of first row only (useful for SELECT COUNT(*) statements) $sql = "SELECT COUNT(*) FROM users AS U"; $total = $database->loadResult($sql); $database->destroyConnection();
php代码:
<?php /** * This is the MySQLi OOP database interface class * @package Skyward_Landing_Page * @subpackage Database Interface * @filesource * @author Matt Ford * @version 2.0 */ class database_mysqli { /** * database server hostname/IP * @var string */ private $host = NULL; /** * SQL Server login username, encrypted for privacy * @var string */ private $username = NULL; /** * SQL Server login password, encrypted for privacy * @var string */ private $password = NULL; /** * name of database * @var string */ public $databaseName = NULL; /** * SQL Server connection resource * @var object */ public $link = NULL; /** * array of queries run against this object * @var array */ public $queries = NULL; /** * any errors resulting from queries * @var array */ public $errors = NULL; public function __construct($host, $username, $password, $database) { $this->host = $host; $this->username = sha1($username); $this->password = sha1($password); $this->databaseName = $database; $this->link = ""; $this->queries = array (); $this->errors = array (); $this->sqls = array (); $this->link = @new mysqli($this->host, $username, $password); if ($this->link->connect_error != null) { die("Connect Error: " . $this->link->connect_error); } else { if ($this->link->select_db($this->databaseName) === false) { die("Cannot Select Table: " . $this->link->error); } else {} } } /** * This method kills the MySQLi connection * @access public * @author Matt Ford */ public function destroyConnection() { $this->link->kill($this->link->thread_id); $this->link->close(); } /** * This method executes a query and returns the raw result resource * @access public * @author Matt Ford * @param string $sql string query * @return object raw SQL result resource */ public function justquery($sql) { $this->queries[] = $sql; return $this->link->query($sql); } /** * This method loads the first value of the first column of the first row of results * @access public * @author Matt Ford * @param string $sql string query * @return string result from first column of first row of query results */ public function loadResult($sql) { if (!($cur = $this->justquery($sql))) { return null; } $ret = null; if ($row = $cur->fetch_row()) { $ret = $row[0]; } $cur->free(); return $ret; } /** * This method returns the first row of results * @access public * @author Matt Ford * @param string $sql string query * @return object first row of results */ public function loadFirstRow($sql) { if (!($cur = $this->justquery($sql))) { return null; } $ret = null; if ($row = $cur->fetch_object()) { $ret = $row; } $cur->free(); return $ret; } /** * This method returns the auto-increment value from the last query run * @access public * @author Matt Ford * @return int auto-incremeted (primary key) value of last query */ public function insertid() { return $this->link->insert_id; } /** * This method returns the number of affected rows in the last insert/update/replace/delete query * @access public * @author Matt Ford * @return int number of affected rows */ public function numAffectedRows() { return $this->link->affected_rows; } /** * This method queries the database, logs data, and returns results * @access public * @author Matt Ford * @param string|array $sql depending on $batch flag, could be a single string query or an array of queries to run * @param string $key if supplied, each group of results will be indexed with its respective $key's column value as its object index/position * @param bool $returns determins if any results will be returned or not, merely for I/O * @param bool $batch flag denoting whether $sql is a string query or an array of queries to loop over * @return unset|object depending on $returns, could be nothing, or an object of query results */ public function query($sql, $key = "", $returns = true, $batch = false) { $sqls = $result = array (); switch ($batch) { default: case true: foreach ($sql as $index => $query) { $this->queries[] = $query; $answer = $this->link->query($query); if (!$answer) { $this->errors[] = $this->link->error; } else { if ($returns != false) { if ($answer->num_rows > 0){ while ($row = $answer->fetch_object()) { if ($key != ""){ $result[$index][$row->$key] = $row; } else { $result[$index][] = $row; } } $answer->free(); } else {} } else {} } } break; case false: $this->queries[] = $sql; $answer = $this->link->query($sql); if (!$answer) { $this->errors[] = $this->link->error; $result = false; } else { if ($returns != false) { if ($answer->num_rows > 0){ while ($row = $answer->fetch_object()) { if ($key != ""){ $result[$row->$key] = $row; } else { $result[] = $row; } } $answer->free(); } else {} } else { $result = true; } } break; } return $result; } /** * This method simply uses the database library's string escape utility * @access public * @author Matt Ford * @param string $string string needing escaping * @return string escaped string */ public function escapeString($string) { return $this->link->real_escape_string($string); } /** * This method builds INSERT/UPDATE queries to allow easy query generation/maintenance for long queries. * @access public * @author Matt Ford * @param array $params key/value pair array of parameters for query * @return string resulting Query string for MySQLi */ public function buildSQL($params) { /* Usage #INSERT Statements $params = array ( "type" => "insert", "table" => "eventCal_events", "doNotQuote" => array(), "data" => array ( "eventName" => $data->request["eventName"], "eventText" => $data->request["eventText"], "eventLocation" => $data->request["eventLocation"], "eventStartDate_month" => $start["month"], "eventStartDate_day" => $start["day"], "eventStartDate_year" => $start["year"], "eventStartDate_time" => $start["time"], "eventStartDate_timestamp" => $timestampStart, "eventEndDate_month" => $end["month"], "eventEndDate_day" => $end["day"], "eventEndDate_year" => $end["year"], "eventEndDate_time" => $end["time"], "eventEndDate_timestamp" => $timestampEnd, "occursMonthly" => $occursMonthly, "occursYearly" => $occursYearly, "dynamicEvent" => $dynamicEvent, "dynNthDay" => $data->request["dynOccurrence_freq"], "dynDayName" => $data->request["dynOccurrence_day"], "dynMonth" => $data->request["dynOccurrence_month"] ) ); $sql = $database->buildSQL($params); #UPDATE Statements $params = array ( "type" => "update", "table" => "eventCal_events", "doNotQuote" => array(), "data" => array ( "eventName" => $data->request["eventName"], "eventText" => $data->request["eventText"], "eventLocation" => $data->request["eventLocation"], "eventStartDate_month" => $start["month"], "eventStartDate_day" => $start["day"], "eventStartDate_year" => $start["year"], "eventStartDate_time" => $start["time"], "eventStartDate_timestamp" => $timestampStart, "eventEndDate_month" => $end["month"], "eventEndDate_day" => $end["day"], "eventEndDate_year" => $end["year"], "eventEndDate_time" => $end["time"], "eventEndDate_timestamp" => $timestampEnd, "occursMonthly" => $occursMonthly, "occursYearly" => $occursYearly, "dynamicEvent" => $dynamicEvent, "dynNthDay" => $data->request["dynOccurrence_freq"], "dynDayName" => $data->request["dynOccurrence_day"], "dynMonth" => $data->request["dynOccurrence_month"] ), "where" => array ( "eventID" => $data->request["eventID"], "eventCreator" => $my->userID ) ); $sql = $database->buildSQL($params); */ $sql = ""; $fieldQuantifier = "`"; $valueQuantifier = '"'; $params["type"] = strtolower($params["type"]); $params["doNotQuote"] = (is_array($params["doNotQuote"]) === true) ? $params["doNotQuote"] : array(); foreach ($params["data"] as $k => $v) { $value = stripslashes($v); $params["data"][$k] = $this->escapeString($value); } switch ($params["type"]) { case "insert": $sql .= "INSERT INTO " . $fieldQuantifier . $params["table"] . $fieldQuantifier . " "; $sql .= "(" . $fieldQuantifier . implode($fieldQuantifier . ", " . $fieldQuantifier, array_keys($params["data"])) . $fieldQuantifier . ") "; $sql .= "VALUES("; $vars = array(); foreach ($params["data"] as $k => $v) { $v = (in_array($k, $params["doNotQuote"])) ? $v : $valueQuantifier . $v . $valueQuantifier; $vars[] = $v; } $sql .= implode(", ", $vars); $sql .= ");"; break; case "update": $sql .= "UPDATE " . $fieldQuantifier . $params["table"] . $fieldQuantifier . " SET "; $vars = array(); foreach ($params["data"] as $k => $v) { $v = (in_array($k, $params["doNotQuote"])) ? $v : $valueQuantifier . $v . $valueQuantifier; $vars[] = $fieldQuantifier . $k . $fieldQuantifier . " = " . $v; } $sql .= implode(", ", $vars); $vars = array(); if ($params["where"]) { $sql .= " WHERE "; foreach ($params["where"] as $k => $v) { $vars[] = $fieldQuantifier . $k . $fieldQuantifier . " = " . $valueQuantifier . $v . $valueQuantifier; } $sql .= implode(" AND ", $vars); } else {} $sql .= ";"; break; } return $sql; } } ?>
上一篇: oracle的job学习
下一篇: MySQL 加密/压缩函数_MySQL