Use PDO in php to add, delete, modify, and query Databases

Use PDO in php to add, delete, modify, and query Databases

PDO is a public class for mysql database operations. We can directly use pdo to operate databases without having to customize the class, but in the default php configuration, pdo is not enabled, so we must first in php. enable it in ini.

You need to enable pdo support for php and php5.1 and later versions.

Database Connection Singleton is implemented, including three elements: static variables, static instantiation methods, and private constructor DPDO. php.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

Class DPDO { Private $ DSN; Private $ DBUser; Private $ DBPwd; Private $ longLink; Private $ pdo; // Private constructor prevents direct instantiation Private function _ construct ($ dsn, $ DBUser, $ DBPwd, $ longLink = false ){ $ This-> DSN = $ dsn; $ This-> DBUser = $ DBUser; $ This-> DBPwd = $ DBPwd; $ This-> longLink = $ longLink; $ This-> connect (); } // Private empty clone function to prevent cloning Private function _ clone (){} // The static instantiation function returns a pdo object. Static public function instance ($ dsn, $ DBUser, $ DBPwd, $ longLink = false ){ Static $ singleton = array (); // static functions are used to store instantiated objects. $ SingIndex = md5 ($ dsn. $ DBUser. $ DBPwd. $ longLink ); If (empty ($ singleton [$ singIndex]) { $ Singleton [$ singIndex] = new self ($ dsn, $ DBUser, $ DBPwd, $ longLink = false ); } Return $ singleton [$ singIndex]-> pdo; }   Private function connect (){ Try { If ($ this-> longLink ){ $ This-> pdo = new PDO ($ this-> DSN, $ this-> DBUser, $ this-> DBPwd, array (PDO: ATTR_PERSISTENT => true )); } Else { $ This-> pdo = new PDO ($ this-> DSN, $ this-> DBUser, $ this-> DBPwd ); } $ This-> pdo-> query ('set names' UTF-8 '); } Catch (PDOException $ e ){ Die ('error: '. $ e-> getMessage ().' <br/> '); } } }

It is used to process field ing. Using pdo field ing can effectively avoid SQL injection.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

// Field Association Array Processing, mainly used to write and update data, query conditions with and or, and generate an array of SQL statements and ing Fields Public function FDFields ($ data, $ link = ',', $ judge = array (), $ aliasTable = ''){ $ SQL = ''; $ MapData = array (); Foreach ($ data as $ key => $ value ){ $ MapIndex = ':'. ($ link! = ','? 'C': ''). $ aliasTable. $ key; $ SQL. = ''. ($ aliasTable? $ AliasTable. '.': ''). '''. $ key. '''. ($ judge [$ key]? $ Judge [$ key]: '='). ''. $ mapIndex.''. $ link; $ MapData [$ mapIndex] = $ value; } $ SQL = trim ($ SQL, $ link ); Return array ($ SQL, $ mapData ); } // Process a single Field Public function FDField ($ field, $ value, $ judge = ', $ preMap = 'cn', $ aliasTable = ''){ $ MapIndex = ':'. $ preMap. $ aliasTable. $ field; $ SQL = ''. ($ aliasTable? $ AliasTable. '.': ''). '''. $ field. '''. $ judge. $ mapIndex; $ MapData [$ mapIndex] = $ value; Return array ($ SQL, $ mapData ); } // You can use the preceding method to conveniently generate query conditions and corresponding data arrays. Public function FDCondition ($ condition, $ mapData ){ If (is_string ($ condition )){ $ Where = $ condition; } Else if (is_array ($ condition )){ If ($ condition ['str']) { If (is_string ($ condition ['str']) { $ Where = $ condition ['str']; } Else { Return false; } } If (is_array ($ condition ['data']) { $ Link = $ condition ['link']? $ Condition ['link']: 'and '; List ($ conSql, $ mapConData) = $ this-> FDFields ($ condition ['data'], $ link, $ condition ['judge ']); If ($ conSql ){ $ Where. = ($ where? ''. $ Link:''). $ conSql; $ MapData = array_merge ($ mapData, $ mapConData ); } } } Return array ($ where, $ mapData ); }

Detailed Implementation of addition, deletion, modification, and query in DB. php

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61

Public function fetch ($ SQL, $ searchData = array (), $ dataMode = PDO: FETCH_ASSOC, $ preType = array (PDO: ATTR_CURSOR => PDO: CURSOR_FWDONLY )){ If ($ SQL ){ $ SQL. = 'limit 1 '; $ PdoStatement = $ this-> pdo-> prepare ($ SQL, $ preType ); $ PdoStatement-> execute ($ searchData ); Return $ data = $ pdoStatement-> fetch ($ dataMode ); } Else { Return false; } }   Public function fetchAll ($ SQL, $ searchData = array (), $ limit = array (0, 10), $ dataMode = PDO: FETCH_ASSOC, $ preType = array (PDO :: ATTR_CURSOR => PDO: CURSOR_FWDONLY )){ If ($ SQL ){ $ SQL. = 'limit'. (int) $ limit [0]. ','. (intval ($ limit [1])> 0? Intval ($ limit [1]): 10 ); $ PdoStatement = $ this-> pdo-> prepare ($ SQL, $ preType ); $ PdoStatement-> execute ($ searchData ); Return $ data = $ pdoStatement-> fetchAll ($ dataMode ); } Else { Return false; } }   Public function insert ($ tableName, $ data, $ returnInsertId = false, $ replace = false ){ If (! Empty ($ tableName) & count ($ data)> 0 ){ $ SQL = $ replace? 'Replace into': 'insert '; List ($ setSql, $ mapData) = $ this-> FDFields ($ data ); $ SQL. = $ tableName. 'set'. $ setSql; $ PdoStatement = $ this-> pdo-> prepare ($ SQL, array (PDO: ATTR_CURSOR => PDO: CURSOR_FWDONLY )); $ ExecRet = $ pdoStatement-> execute ($ mapData ); Return $ execRet? ($ ReturnInsertId? $ This-> pdo-> lastInsertId (): $ execRet): false; } Else { Return false; } }   Public function update ($ tableName, $ data, $ condition, $ mapData = array (), $ returnRowCount = true ){ If (! Empty ($ tableName) & count ($ data)> 0 ){ $ SQL = 'update'. $ tableName. 'set '; List ($ setSql, $ mapSetData) = $ this-> FDFields ($ data ); $ SQL. = $ setSql; $ MapData = array_merge ($ mapData, $ mapSetData ); List ($ where, $ mapData) = $ this-> FDCondition ($ condition, $ mapData ); $ SQL. = $ where? 'Where'. $ WHERE :''; $ PdoStatement = $ this-> pdo-> prepare ($ SQL, array (PDO: ATTR_CURSOR => PDO: CURSOR_FWDONLY )); $ ExecRet = $ pdoStatement-> execute ($ mapData ); Return $ execRet? ($ ReturnRowCount? $ PdoStatement-> rowCount (): $ execRet): false; } Else { Return false; } }   Public function delete ($ tableName, $ condition, $ mapData = array ()){ If (! Empty ($ tableName) & $ condition ){ $ SQL = 'delete from'. $ tableName; List ($ where, $ mapData) = $ this-> FDCondition ($ condition, $ mapData ); $ SQL. = $ where? 'Where'. $ WHERE :''; $ PdoStatement = $ this-> pdo-> prepare ($ SQL, array (PDO: ATTR_CURSOR => PDO: CURSOR_FWDONLY )); $ ExecRet = $ pdoStatement-> execute ($ mapData ); Return $ execRet; } }

Test. php

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

Header ("Content-type: text/html; charset = UTF-8 "); Define ('app _ dir', dirname (_ FILE __));   If (function_exists ('spl _ autoload_register ')){ Spl_autoload_register ('autoclass '); } Else { Function _ auto_load ($ className ){ AutoClass ($ className ); } }   Function autoClass ($ className ){ Try { Require_once APP_DIR. '/class/'. $ className. '. php '; } Catch (Exception $ e ){ Die ('error: '. $ e-> getMessage ().' <br/> '); } } $ DB = new DB (); // Insert $ InData ['a'] = rand (1,100 ); $ InData ['B'] = rand (1, 1000 ); $ InData ['C'] = rand (1,200). '.'. rand (1,100 ); $ Ret = $ DB-> insert ('A', $ inData ); Echo 'insert'. ($ ret? 'Success': 'failed'). '<br/> '; // Update $ UpConData ['A '] = 100; $ UpConJudge ['a'] = '<'; $ UpConData ['B'] = 30; $ UpConJudge ['B'] = '> '; List ($ upConStr, $ mapUpConData) = $ DB-> FDField ('B', 200, '<', 'gt '); $ Condition = array ( 'Str' => $ upConStr, 'Data' => $ upConData, 'Judge '=> $ upConJudge, 'Link' => 'and' ); $ UpData ['a'] = rand (1, 10 ); $ UpData ['B'] = 1; $ UpData ['C'] = 1.00; $ ChangeRows = $ DB-> update ('A', $ upData, $ condition, $ mapUpConData ); Echo 'number of update rows: '. (int) $ changeRows.' <br/> '; // Delete $ DelVal = rand (1, 10 ); List ($ delCon, $ mapDelCon) = $ DB-> FDField ('A', $ delVal ); $ DelRet = $ DB-> delete ('A', $ delCon, $ mapDelCon ); Echo 'delete a = '. $ delVal. ($ delRet? 'Success': 'failed'). '<br/> ';   // Query $ Data ['a'] = '10 '; $ Judge ['a'] = '> '; $ Data ['B'] = '123 '; $ Judge ['B'] = '<'; List ($ conSql, $ mapConData) = $ DB-> FDFields ($ data, 'and', $ judge ); $ MData = $ DB-> fetch ('select * from a where'. $ conSql. 'order by 'A' desc', $ mapConData );   Var_dump ($ mData );

The above is all the content of this article. I hope you will like it.