Use of mysql connection methods in php

This article mainly introduces in detail the various use methods of mysql connection methods in PHP, which is a personal summary. if you have any omissions, please let me know. if you need them, please refer to them. Common PDO methods:

PDO: query () is mainly used for operations (PDOStatement) that have returned 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. select 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 () is returned, the object is returned. if fetchall () is returned, a two-dimensional array composed of objects is returned.

The code is as follows:

<? Php
$ 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

The code is as follows:

<? Php
$ 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

The code is as follows:

<? Php
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. Transaction

The code is as follows:

<? Php
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. handle errors

A. silent mode (default mode)

The code is as follows:

$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_SILENT); // no error is displayed.
$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_WARNING); // displays a warning error and continues execution
$ Dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // generates a fatal error, PDOException

The code is as follows:

<? Php
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 ()

The code is as follows:

<? Php
$ 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]

The code is as follows:

<? Php
$ Dbh-> prepare ($ SQL); generates a PDOStatement object.

PDOStatement-> bindParam ()

PDOStatement-> execute (); // you can put the bound variable here.
?>

3. Transactions

The code is as follows:

<? Php
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 ();
}
?>

The above is all about the usage of pdo in PHP. I hope this article will help you and I hope you will like it.