How to Connect MySQL Database Using PHP – Safe to PHP 7

In order to be able to interact with a database, especially MySQL database, the first thing we need to do is connecting to the database, there are many ways to do it, so this time, we’ll discuss how to connect MySQL Database using PHP.

PHP is growing, its built-in functions are continuously developed and maintained. For some reasons, some of them were removed including functions for connecting to MySQL database.

Note: Functions in this tutorial also works with MariaDB database (The “Duplicate” of MySQL).

I. MySQLi and PDO

Usually, when we want to connect to MySQL Database Using PHP, we use the following functions:

mysql_connect()
mysql_select_db()
mysql_close()

Today, the above method become an old school way.

So, if you still use those functions you should stop it right now, because, PHP 7 no longer support those functions, it only works until PHP 5.6. Otherwise, we should now use MySQLi or PDO.

So, what functions should be used?

Currently, PHP provides two ways to connect PHP to MySQL sever, using MySQLi (MySQL Improvement) and PDO (PHP Data Object).

MySQLi

Procedural Programming

• Yes it support procedural programming.
• If you usually use mysql_xxx function, then this type will suits to you, as MySQLi provide functions similar to mysql extension, we only need to add an i suffix, for example: mysql_connect() become mysli_connect().

Object Oriented Programming (OOP Way):

• Yes it support Object Oriented Programming
• MySQLi only support MySQL database, so if you work with other databases of planning to use other database, you need to change to PDO
• Doesn’t support named parameters.
• Support prepare statement (prevent SQL Injection)
• A bit faster than PHP PDO

PDO

Support procedural way.

• Doesn’t support procedural way.
• If you usually use mysql_xxx functions, then it is necessary to learn from scratch to use this extension.

Support OOP Way

• PHP PDO Support up to 12 database. List of supported database can be found disini
• Support named parameters, make it easier to bind value to the query
• Support prepare statement (prevent SQL Injection)
• A bit slower than MySQLi

II. Connect MySQL Database Using PHP

There are two ways to connect MySQL database using PHP, both by using MySQLi and PDO. we’ll discuss both.

Using MySQLi to connect to MySQL Database

For procedural programming, use mysql_query() function with some parameters: mysqli_connect('db host', 'db username', 'db password', 'db name'), for example:

$con = @mysqli_connect('localhost', 'root', '', 'wordpress');

if (!$con) {
    echo "Error: " . mysqli_connect_error();
 exit();
}
echo 'Connected to MySQL';

For OOP way, the format is similar, we only need to add a new keyword: new mysqli ('db host', 'db username', 'db password', 'db name'), for example:

$con = @new mysqli('localhost', 'root', '', 'wordpress');

if ($con->connect_error) {
    echo "Error: " . $con->connect_error;
 exit();
}
echo 'Connected to MySQL';

Some possible errors that may occur while running the above code:

• Error: Access denied for user ‘@’ localhost ‘to database’ wordpress’ means that MySQLi can’t login into MySQL database using the provided user name and password.
• Error: Unknown database ‘wordpress’ means that the login was successful but MySQLi can’t find wordpress database.

More complete example:

// Open Connection
$con = @mysqli_connect('localhost', 'root', '', 'product');

if (!$con) {
    echo "Error: " . mysqli_connect_error();
 exit();
}

// Some Query
$sql  = 'SELECT * FROM product';
$query  = mysqli_query($con, $sql);
while ($row = mysqli_fetch_array($query))
{
 echo $row['id'];
}

// Close connection
mysqli_close ($con);

To know more about MySQLi function both using OOP and procedural Interface, please visit: PHP: The MySQLi Extension Function Summary – Manual

On that page, there are various functions that we familiar with, such as: mysqli_num_fields(), mysqli_fetch_row(), and mysqli_fetch_array().

Using PDO to connect to MySQL Database

Unlike MySQLi, to connect PHP to MySQL server using PHP PDO, we have to use try{} and catch{} block.

The point is try{} means that we try to run a PHP script, if an error occurs, than the error will be captured in the catch{} block so, that the output of the error will be isolated in the block.

Example:

try 
{
 $pdo = new PDO('mysql:host=localhost;dbname=product', 'root', '');

}
catch (PDOException $e) 
{
    echo 'Error: ' . $e->getMessage();
    exit();
}
echo 'Connected to MySQL';

More complete example:

// Open connection
try 
{
 $pdo = new PDO('mysql:host=localhost;dbname=produk', 'root', '');

}
catch (PDOException $e) 
{
    echo 'Error: ' . $e->getMessage();
    exit();
}

// Run Query
$sql  = 'SELECT * FROM product';
$stmt  = $pdo->prepare($sql); // Prevent MySQl injection. $stmt means statement
$stmt->execute();
while ($row = $stmt->fetch())
{
 echo $row['id'];
}

// Close connection
$pdo = null;

Attributes in PDO

PDO has many attributes to define various rule, including:

• PDO :: ATTR_ERRMODE to adjust the displayed error
• PDO :: ATTR_DEFAULT_FETCH_MODE to set the default fetch mode

Example of using multiple attributes at once:

try 
{
 $driver_options = array(
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
  PDO::ATTR_ERRMODE => PDO::ATTR_ERRMODE_EXCEPTION,
  PDO::MYSQL_ATTR_LOCAL_INFILE => 1
 );
 
 $pdo = new PDO('mysql:host=localhost;dbname=product', 'root', '', $driver_options);
}

Example of using individual attribute:

try 
{
 $pdo = new PDO('mysql:host=localhost;dbname=product', 'root', '');
 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ATTR_ERRMODE_EXCEPTION);
}

For a complete list of available attributes, please visit: PHP: PDO :: setAtribute – Manual.

III. Closing Connection

If we look at the above scripts, we always close the mysql connection. Should we do this? the answer could be yes and no.

For the yes answer

Yes, for best practice, as in other programming languages, if we not close the connection, it will still open and stored in memory.

For the no answer

PHP is an interpreted language, means that it not directly related to the physical computer memory (RAM), there is an engine which translated the PHP code into a computer language that is Zend Engine, a big company behind PHP

Well, to keep the program to always run well, then at the end of each script execution, the engine will automatically shut down all connections to the database, so we don’t need to do it manually.