<?phpclassDB{# @object, The PDO objectprivate$pdo;# @object, PDO statement objectprivate$sQuery;# @array, The database settingsprivate$settings;# @bool , Connected to the databaseprivate$bConnected=false;# @array, The parameters of the SQL queryprivate$parameters;/**
* Default Constructor
*
* 1. Instantiate Log class.
* 2. Connect to database.
* 3. Creates the parameter array.
*/publicfunction__construct(){$this->Connect();$this->parameters=array();}/**
* This method makes connection to the database.
*
* 1. Reads the database settings from a ini file.
* 2. Puts the ini content into the settings array.
* 3. Tries to connect to the database.
* 4. If connection failed, exception is displayed and a log file gets created.
*/privatefunctionConnect(){$this->settings=parse_ini_file("settings.ini.php");$dsn='mysql:dbname='.$this->settings["dbname"].';host='.$this->settings["host"].'';try{# Read settings from INI file, set UTF8$this->pdo=newPDO($dsn,$this->settings["user"],$this->settings["password"],array(PDO::MYSQL_ATTR_INIT_COMMAND=>"SET NAMES utf8"));# We can now log any exceptions on Fatal error.$this->pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);# Disable emulation of prepared statements, use REAL prepared statements instead.$this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);# Connection succeeded, set the boolean to true.$this->bConnected=true;}catch(PDOException$e){# Write into logecho$this->ExceptionLog($e->getMessage());die();}}/*
* You can use this little method if you want to close the PDO connection
*
*/publicfunctionCloseConnection(){# Set the PDO object to null to close the connection# http://www.php.net/manual/en/pdo.connections.php$this->pdo=null;}/**
* Every method which needs to execute a SQL query uses this method.
*
* 1. If not connected, connect to the database.
* 2. Prepare Query.
* 3. Parameterize Query.
* 4. Execute Query.
* 5. On exception : Write Exception into the log + SQL query.
* 6. Reset the Parameters.
*/privatefunctionInit($query,$parameters=""){# Connect to databaseif(!$this->bConnected){$this->Connect();}try{# Prepare query$this->sQuery=$this->pdo->prepare($query);# Add parameters to the parameter array$this->bindMore($parameters);# Bind parametersif(!empty($this->parameters)){foreach($this->parametersas$param=>$value){if(is_int($value[1])){$type=PDO::PARAM_INT;}elseif(is_bool($value[1])){$type=PDO::PARAM_BOOL;}elseif(is_null($value[1])){$type=PDO::PARAM_NULL;}else{$type=PDO::PARAM_STR;}// Add type when binding the values to the column$this->sQuery->bindValue($value[0],$value[1],$type);}}# Execute SQL$this->sQuery->execute();}catch(PDOException$e){# Write into log and display Exceptionecho$this->ExceptionLog($e->getMessage(),$query);die();}# Reset the parameters$this->parameters=array();}/**
* @void
*
* Add the parameter to the parameter array
* @param string $para
* @param string $value
*/publicfunctionbind($para,$value){$this->parameters[sizeof($this->parameters)]=[":".$para,$value];}/**
* @void
*
* Add more parameters to the parameter array
* @param array $parray
*/publicfunctionbindMore($parray){if(empty($this->parameters)&&is_array($parray)){$columns=array_keys($parray);foreach($columnsas$i=>&$column){$this->bind($column,$parray[$column]);}}}/**
* If the SQL query contains a SELECT or SHOW statement it returns an array containing all of the result set row
* If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return mixed
*/publicfunctionquery($query,$params=null,$fetchmode=PDO::FETCH_ASSOC){$query=trim(str_replace("\r"," ",$query));$this->Init($query,$params);$rawStatement=explode(" ",preg_replace("/\s+|\t+|\n+/"," ",$query));# Which SQL statement is used$statement=strtolower($rawStatement[0]);if($statement==='select'||$statement==='show'){return$this->sQuery->fetchAll($fetchmode);}elseif($statement==='insert'||$statement==='update'||$statement==='delete'){return$this->sQuery->rowCount();}else{returnNULL;}}/**
* Returns the last inserted id.
* @return string
*/publicfunctionlastInsertId(){return$this->pdo->lastInsertId();}/**
* Starts the transaction
* @return boolean, true on success or false on failure
*/publicfunctionbeginTransaction(){return$this->pdo->beginTransaction();}/**
* Execute Transaction
* @return boolean, true on success or false on failure
*/publicfunctionexecuteTransaction(){return$this->pdo->commit();}/**
* Rollback of Transaction
* @return boolean, true on success or false on failure
*/publicfunctionrollBack(){return$this->pdo->rollBack();}/**
* Returns an array which represents a column from the result set
*
* @param string $query
* @param array $params
* @return array
*/publicfunctioncolumn($query,$params=null){$this->Init($query,$params);$Columns=$this->sQuery->fetchAll(PDO::FETCH_NUM);$column=null;foreach($Columnsas$cells){$column[]=$cells[0];}return$column;}/**
* Returns an array which represents a row from the result set
*
* @param string $query
* @param array $params
* @param int $fetchmode
* @return array
*/publicfunctionrow($query,$params=null,$fetchmode=PDO::FETCH_ASSOC){$this->Init($query,$params);$result=$this->sQuery->fetch($fetchmode);$this->sQuery->closeCursor();// Frees up the connection to the server so that other SQL statements may be issued,return$result;}/**
* Returns the value of one single field/column
*
* @param string $query
* @param array $params
* @return string
*/publicfunctionsingle($query,$params=null){$this->Init($query,$params);$result=$this->sQuery->fetchColumn();$this->sQuery->closeCursor();// Frees up the connection to the server so that other SQL statements may be issuedreturn$result;}/**
* Writes the log and returns the exception
*
* @param string $message
* @param string $sql
* @return string
*/privatefunctionExceptionLog($message,$sql=""){$exception='Unhandled Exception. <br />';if(!empty($sql)){# Add the Raw SQL to the Log$message.="\r\nRaw SQL : ".$sql;}$exception.=$message;$exception.="<br /> You can find the error back in the log.";return$exception;}}?>
<?php// Fetch whole table$persons=$db->query("SELECT * FROM persons");
条件查询 (免疫SQL注入):
有3中不同的方式查询
<?php// 1. 方式一 $db->bind("id","1");$db->bind("firstname","John");$person=$db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id");// 2. 方式二 $db->bindMore(array("firstname"=>"John","id"=>"1"));$person=$db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id"));// 3. 方式三$person=$db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id",array("firstname"=>"John","id"=>"1"));
查询行:
通常返回单行
<?php$ages=$db->row("SELECT * FROM Persons WHERE id = :id",array("id"=>"1"));
Result
id
firstname
lastname
sex
age
1
John
Doe
M
19
查询单个值:
<?php// Fetch one single value$db->bind("id","3");$firstname=$db->single("SELECT firstname FROM Persons WHERE id = :id");
Result
firstname
Zoe
模糊查询
<?php// 注意必须以通配符结尾$like=$db->query("SELECT * FROM Persons WHERE Firstname LIKE :firstname ",array("firstname"=>"sekit%"));
Result
id
firstname
lastname
sex
age
4
Sekito
Khan
M
19
查询列:
<?php// Fetch a column$names=$db->column("SELECT Firstname FROM Persons");
Result
firstname
John
Bob
Zoe
Kona
Kader
删除/插入/更新
<?php// Delete$delete=$db->query("DELETE FROM Persons WHERE Id = :id",array("id"=>"1"));// Update$update=$db->query("UPDATE Persons SET firstname = :f WHERE Id = :id",array("f"=>"Jan","id"=>"32"));// Insert$insert=$db->query("INSERT INTO Persons(Firstname,Age) VALUES(:f,:age)",array("f"=>"Vivek","age"=>"20"));// Do something with the data if($insert>0){return'Succesfully created a new person !';}