MySQLi Object-Oriented PHP Database Connection

PHP - Mysql logoBefore we do anything with our project, we need to access the data stored in the MySQL database. For that, we need to be able to connect to the database server.

  • MySQL is the most popular database system used with PHP.
  • With PHP, you can connect to and manipulate databases(insert, edit, delete data).

You can create a connect.php file that contains this functionality so that you can include(“connect.php”); anywhere you need mysql along your project.

You can use a simple – MySQLi Procedural – type of connection or you can use a MySQLi Object-Oriented connection type or a PDO connection (PHP Data Objects).

Most of the people I know are using a MySQLi Object-Oriented connection like this:

<?php
$server_name = "localhost";
$username = "mysql_username"; // Your Mysql database username
$password = "mysql_password"; // Your Mysql database password

// Create a connection using OOP
$conn = new mysqli($server_name, $username, $password);

// Check connection for errors 
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
// This line is for testing only...
// echo "Connected successfully!";
?>

Also, the simple procedural method looks almost the same as the OOP method, with a small difference:

<?php
$server_name = "localhost";
$username = "username"; // Your Mysql username
$password = "password"; // Your Mysql password

// Create connection using non-OOP method
$conn = mysqli_connect($servername, $username, $password);

// Check connection for errors using the old die() call
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
// Only for testing echo "";
// echo "Connected successfully";
?>

Most of the projects can handle the database with the two methods from above, along this, there is another method for a connection this time using PDO (PDO is an acronym for PHP Data Objects. PDO is a lean, consistent way to access databases. This means developers can write portable code much easier. PDO is not an abstraction layer like PearDB. PDO is a more like a data access layer which uses a unified API)

<?php
$server_name = "localhost";
$username = "username"; // Your Mysql username
$password = "password"; // Your Mysql password

try {
    // dbname=mydatabase is your database name
    $conn = new PDO("mysql:host=$server_name;dbname=mydatabase", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>