PHP Connect to MySQL

PHP Connect to MySQL


PHP 5 and later can work with a MySQL database using :

  • MySQLi extension (the "i" stands for improved)
  • PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.





Open a Connection to MySQL

We must be able to log on to the server before we can view data in the MySQL database :

Note:- Here we have created three types of connection code using mysqli (object oriented), mysqli (procedural) and PDO. You can use any one of the type or method to make a connection with mysql database.

Note:- Don't try to mix or use more than one method to create a mysql connection.

Example (MySQLi Object-Oriented)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Up to PHP 5.2.9 and 5.3.0 $connect error had been broken. If you need to make sure your PHP version is compatible before 5.2.9 and 5.3.0, please use the code :

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

Example (MySQLi Procedural)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

Example (PDO)

<?php
$servername = "localhost";
$username = "username";
$password = "password";
try {
  $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
  echo "Connected successfully";
} catch(PDOException $e) {
  echo "Connection failed: " . $e->getMessage();
}
?>

Note :-

We also specified a database in the previous PDO example (test). PDO requires a proper database connection to function. An error is thrown if no database is given.

Tip:- A significant advantage of PDO is that it offers an exceptional class for any issues that may arise from our database queries. The script will stop executing and move immediately to the first catch(){{~} block if an exception is thrown within the test{} block. The SQL command and the produced error message are echoed in the above catch block.


Close the Connection

When the script is finished, the connection will be immediately disconnected. Use the following to disconnect the connection before proceeding :

MySQLi Object-Oriented :

$conn->close();

MySQLi Procedural:

mysqli_close($conn);

PDO :

$conn = null;


You can also search for these topics, php mysql close the connection, php close the connection mysqli object-oriented, php close the connection mysqli procedural, php close the connection mysqli pdo, how to close the php mysqli connection.