Phppdo class library usage example, phppdo Module Entry example-php Tutorial

Phppdo class library usage example, phppdo Module Entry example

$ Dbh = new PDO ('MySQL: host = localhost; dbname = access_control ', 'root ',''); $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> exec ('set names utf8 '); /* Add */ // $ SQL = "INSERT INTO 'user' SET 'login' =: login AND 'password' =: password "; $ SQL = "INSERT INTO 'user' ('login', 'password') VALUES (: login,: password )"; $ stmt = $ dbh-> prepare ($ SQL); $ stmt-> execute (array (': login' => 'kevin2 ',': password '=> '')); Echo $ dbh-> lastinsertid (); /* Modify */ $ SQL = "UPDATE 'user' SET 'password' =: password WHERE 'user _ id' =: userId "; $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> execute (array (': userid' => '7',': password' => '4607e782c4d86fd5364d7e4248bb10d9 ')); Echo $ stmt-> rowCount (); /* Delete */ $ SQL = "DELETE FROM 'user' WHERE 'login' LIKE 'Kevin _ '"; // kevin % $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> execute (); Echo $ stmt-> rowCount (); /* Query */ $ Login = 'Kevin % '; $ SQL = "SELECT * FROM 'user' WHERE 'login' LIKE: login "; $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> execute (array (': login' => $ login )); While ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC )){ Print_r ($ row ); } Print_r ($ stmt-> fetchAll (PDO: FETCH_ASSOC )); ?> 1. establish a connection $ Dbh = newPDO ('MySQL: host = localhost; port = 3306; dbname = test', $ user, $ pass, array ( PDO: ATTR_PERSISTENT => true )); ?> Persistent link PDO: ATTR_PERSISTENT => true 2. capture errors Try { $ Dbh = newPDO ('MySQL: host = localhost; dbname = test', $ user, $ pass ); $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> exec ("set character set utf8 "); $ Dbh = null; // disconnect } Catch (PDOException $ e ){ Print "Error! : ". $ E-> getMessage ()." "; Die (); } ?> 3. Transactions Try { $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> beginTransaction (); // start the transaction $ Dbh-> exec ("insertintostaff (id, first, last) values (23, 'job', 'bloggs ')"); $ Dbh-> exec ("insertintosalarychange (id, amount, changedate) Values (, NOW ())"); $ Dbh-> commit (); // submit the transaction } Catch (Exception $ e ){ $ Dbh-> rollBack (); // error rollBack Echo "Failed:". $ e-> getMessage (); } ?> 4. Error handling a. silent mode (default mode) Try { $ Dbh = new PDO ($ dsn, $ user, $ password ); $ SQL = 'select * from city where CountryCode =: country '; $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_WARNING ); $ Stmt = $ dbh-> prepare ($ SQL ); $ Stmt-> bindParam (': country', $ country, PDO: PARAM_STR ); $ Stmt-> execute (); While ($ row = $ stmt-> fetch (PDO: FETCH_ASSOC )){ Print $ row ['name']. "/t "; } } // If there is a problem we can handle it here Catch (PDOException $ e ){ Echo 'pdo Exception Caught .'; Echo 'error with the database: '; Echo 'SQL Query:', $ SQL; Echo 'Error: '. $ e-> getMessage (); } ?> 1. use query () $ Dbh-> query ($ SQL); when $ SQL contains variables, you can use $ dbh-> quote ($ params); // escape string data $ SQL = 'select * from city where CountryCode = '. $ dbh-> quote ($ country ); Foreach ($ dbh-> query ($ SQL) as $ row ){ Print $ row ['name']. "/t "; Print $ row ['countrycode']. "/t "; Print $ row ['population']. "/n "; } ?> 2. use prepare, bindParam, and execute [recommended, and can be added, modified, or deleted] $ Dbh-> prepare ($ SQL); generates a PDOStatement object. PDOStatement-> bindParam () PDOStatement-> execute (); // you can put the bound variable here. ?> 3. Transactions Try { $ Dbh = new PDO ('MySQL: host = localhost; dbname = test', 'root ',''); $ Dbh-> query ('set names utf8 ;'); $ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION ); $ Dbh-> beginTransaction (); $ Dbh-> exec ("Insert INTO 'test'. 'Table' ('name', 'age') VALUES ('Mick', 22 );"); $ Dbh-> exec ("Insert INTO 'test'. 'Table' ('name', 'age') VALUES ('Lily', 29 );"); $ Dbh-> exec ("Insert INTO 'test'. 'Table' ('name', 'age') VALUES ('Susan ', 21 );"); $ Dbh-> commit (); } Catch (Exception $ e ){ $ Dbh-> rollBack (); Echo "Failed:". $ e-> getMessage (); } ?> Common PDO method: PDO: query () is mainly used for PDOStatement operations with record results, especially select operations. PDO: exec () is mainly for operations that are not returned by the result set. Such as insert and update. Returns the number of affected rows. PDO: lastInsertId () returns the last ID of the last insert operation, but note: If you use insert into tb (col1, col2) values (v1, v2), (v11, v22 ).. lastinsertid () returns only the ID of the first (v1, v2) inserted, rather than the ID of the last inserted record. PDOStatement: fetch () is used to obtain a record. Traverse with while. PDOStatement: fetchAll () is to obtain all record sets to one. PDOStatement: fetchcolumn ([int column_indexnum]) is used to directly access a column. the parameter column_indexnum is the value of the column starting from 0 in the row. However, this method can only retrieve one column of the same row at a time. if it is executed once, it will jump to the next row. Therefore, it is easier to directly access a column, but it cannot be used to traverse multiple columns. PDOStatement: rowcount () is used to obtain the number of records when the query ("select...") method is used. It can also be used in preprocessing. $ Stmt-> rowcount (); PDOStatement: columncount () is used to obtain the number of columns of records when the query ("select...") method is used. Note: 1. choose fetch or fetchall? When using a small record set, fetchall is highly efficient, reducing the number of retrieval times from the database. However, for a large result set, using fetchall brings a great burden to the system. The database needs to transmit too much data to the web front-end, but the efficiency is low. 2. there are several parameters for fetch () or fetchall: Mixed pdostatement: fetch ([int fetch_style [, int cursor_orientation [, int cursor_offset]) array pdostatement: fetchAll (int fetch_style) Fetch_style parameters: $ Row = $ rs-> fetchAll (PDO: FETCH_BOTH); FETCH_BOTH is the default value, which can be saved and returns associations and indexes. $ Row = $ rs-> fetchAll (PDO: FETCH_ASSOC); the FETCH_ASSOC parameter determines that only the associated array is returned. $ Row = $ rs-> fetchAll (PDO: FETCH_NUM); returns the index array $ row = $ rs-> fetchAll (PDO: FETCH_OBJ); if fetch () returns the object. for fetchall (), returns a two-dimensional array composed of objects.