Latest web development tutorials

PHP MySQL connection

PHP 5 or higher and recommends the following ways to connect MySQL:

  • MySQLi extension ( "i" means improved)
  • PDO (PHP Data Objects)

In early versions of PHP, we use MySQL extension. However, the expansion in 2012 is not recommended.


I use the MySQLi, or PDO?

If you need a short answer, that is, "what you used to use which."

MySQLi and PDO have their own advantages:

PDO applications in 12 different databases, MySQLi only for the MySQL database.

So, if you need to switch the project in a variety of databases, it is recommended to use PDO, so you only need to modify the connection string and departments can query. Use MySQLi, if a different database, you need to re-write all the code, including queries.

Both are object-oriented, but also provides a MySQLi API interface.

Both support prepared statements. Prepared statements to prevent SQL injection, for the security of web projects is very important.


MySQLi and PDO connection MySQL instance

In this chapter and the next chapter, we will use the following three ways to demonstrate PHP operate MySQL:

  • MySQLi (object-oriented)
  • MySQLi (process-oriented)
  • PDO

MySQLi installation

Linux and Windows: When MySQLi extension php5 mysql package is installed in most cases is installed automatically.

Installation For more information, please see: http://php.net/manual/en/mysqli.installation.php

Through phpinfo () to see if the installation was successful:


PDO installation

For installation details, please see: http://php.net/manual/en/pdo.installation.php

Through phpinfo () to see if the installation was successful:


MySQL connection

Before we visit the MySQL database, we need to connect to the database server:

Examples (MySQLi - Object Oriented)

<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";

// Create connection
$ Conn = new mysqli ($ servername, $ username, $ password);

// Test connection
if ($ conn-> connect_error) {
die ( "Connection failed:" $ conn-> connect_error.);
}
echo "successfully connected";
?>

Note Note that in the above example object-oriented $ connect_error in PHP 5.2.9 and 5.3.0 are added. If you need compatibility with earlier versions, please use the following code to replace:

// Test connection
if (mysqli_connect_error ()) {
die ( "Database connection failed:". mysqli_connect_error ());
}

Examples (MySQLi - process-oriented)

<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";

// Create connection
$ Conn = mysqli_connect ($ servername, $ username, $ password);

// Test connection
if (! $ conn) {
die ( "Connection failed:" mysqli_connect_error ().);
}
echo "successfully connected";
?>


Examples of (PDO)

<? Php
$ Servername = "localhost";
$ Username = "username";
$ Password = "password";

try {
$ Conn = new PDO ( "mysql: host = $ servername; dbname = myDB", $ username, $ password);
echo "successfully connected";
}
catch (PDOException $ e)
{
echo $ e-> getMessage ();
}
?>

Note Note that in the above example we have specified the PDO database (myDB). PDO during the connection you need to set the database name. If not specified, an exception is thrown.


Close connection

Connection will automatically close after the script execution. You can also use the following code to close the connection:

Examples (MySQLi - Object Oriented)

$ Conn-> close ();


Examples (MySQLi - process-oriented)

mysqli_close ($ conn);


Examples of (PDO)

$ Conn = null;