PHP 5 and later recommends connecting to MySQL using the following methods:
- mysqli Extension ("I" means improved)
- PDO (PHP Data Objects)
We used the MySQL extension in early PHP versions. However, this extension is not recommended for use in 2012.
Should I use mysqli, or PDO?
If you need a short answer, "which one you are used to".
Mysqli and PDO have their own advantages:
PDO is used in 12 different databases, mysqli only for MySQL databases.
So, if your project needs to switch between multiple databases, it is recommended to use PDO, so you only need to modify the connection string and some query statements. With Mysqli, if you have a different database, you need to rewrite all the code, including the query.
Both are object-oriented, but Mysqli also provides API interfaces.
Both support preprocessing statements. Preprocessing statements can prevent SQL injection, which is important for Web project security.
Mysqli and PDO Connect to MySQL instance
In this section and in the following chapters, we will demonstrate PHP operation with MySQL in three ways:
- Mysqli (object-oriented)
- MYSQLI (Process oriented)
- PDOMYSQLI Installation
Linux and Windows: mysqli extensions are installed automatically in most cases when the PHP5 MySQL package is installed.
For more information on installation, please view: http://php.net/manual/en/mysqli.installation.php
You can see if the installation was successful by Phpinfo ():
PDO Installation
For installation details, please view: http://php.net/manual/en/pdo.installation.php
You can see if the installation was successful by Phpinfo ():
Connect to MySQL
Before we access the MySQL database, we need to connect to the database server first:
Example (mysqli-process-oriented)
1 /*Place Host*/2 $db _host= ' 127.0.0.1 ';3 /*Place username*/4 $db _username= ' Root ';5 /*Place Password*/6 $db _passwd= ' 123123123 ';7 /*Place dbname*/8 $db _name= ' DB1 '; 9 /*Place Port Default 3306*/ Ten //$db _port = ' 3306 '; One /*Place socket or named pipe*/ A //$db _socket = "; - - /*process-oriented MySQL connection*/ the $conn=Mysqli_connect($db _host,$db _username,$db _passwd,$db _name) or die(' Connect_error ('.Mysqli_connect_errno().‘)‘.Mysqli_connect_error());
Example (Mysqli-object-oriented)
1*place host*/2 $db _host= ' 127.0.0.1 ';3 /*Place username*/4 $db _username= ' Root ';5 /*Place Password*/6 $db _passwd= ' 123123123 ';7 /*Place dbname*/8 $db _name= ' DB1 '; 9 /*Place Port Default 3306*/ Ten //$db _port = ' 3306 '; One /*Place socket or named pipe*/ A //$db _socket = "; - - /*object-oriented connection MySQL*/ the $conn=NewMysqli ($db _host,$db _username,$db _passwd,$db _name) or die(' Connect_error ('.Mysqli_connect_errno().‘)‘.Mysqli_connect_error());
Instance (PDO)
1*pdo*/2 Try{3 $conn=NewPDO ("mysql:host=$db _host;d Bname=mysql ",$db _username,$db _passwd);4 Echo"Connection succeeded";5}Catch(pdoexception$e){6 Echo $e-getMessage ();7}
|
Note that we have specified the database (MyDB) in the above PDO instance. PDO needs to set the database name during the connection process. If not specified, an exception is thrown. |
Close connection
The connection closes automatically when the script finishes executing. You can also use the following code to close the connection:
Example (MYSQLI- oriented process)$conn->close ();
Example (Mysqli- Object-oriented)mysqli_close($conn);
instance (PDO) $conn null;
PHP connection MySQL