Connecting to MySQL Database in PHP

Welcome to this quick guide. Connecting to MySQL database in PHP is a very initial step for developing any web applications or website. I am going to mention the most common and efficient way for connecting to MySQL database in PHP.

Amazingly, PHP provides three ways (API) to connect and interact with database (MySQL).

• MYSQL
• MySQLi
• PDO (PHP Data Objects)

Since, simple MySQL is deprecated so, we are going to use MySQLi and PDO only. PHP encourages everyone to use mysqli_* instead of old mysql_* extension. You can also read MySQL vs MySQLi guide to learn more about MySQLi.

Let’s create a database connection file as connection.php. It will store our database name, username, password and database connection.

MySQLi (Procedural)

define('DB_HOST','localhost');
define('DB_USER','your_username');
define('DB_PASS','your_password');
define('DB_NAME','database_name');

$con = mysqli_connect(DB_HOST, DB_USER, DB_PASS,DB_NAME);
if (!$con) die("Database connection failed! " . mysqli_connect_error());

echo "Connected Successfully";

MySQLi (Object-Oriented)

define('DB_HOST','localhost');
define('DB_USER','your_username');
define('DB_PASS','your_password');
define('DB_NAME','database_name');

$con = new mysqli(DB_HOST, DB_USER, DB_PASS,DB_NAME);
if (!$con) die("Database connection failed! " . $mysqli->connect_error);

echo "Connected Successfully";

PDO (PHP Data Objects)

define('DB_HOST','localhost');
define('DB_USER','your_username');
define('DB_PASS','your_password');
define('DB_NAME','database_name');

try {
    
    $con = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USER, DB_PASS);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
    
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

If you noticed, I used constants to store our values instead of variables because we don’t want these values to be changed anywhere in the script, so it’s better to use constants in that case. You can read more about Constants in PHP.

Don’t forget to replace ‘your_username’, ‘your_password’ and ‘database_name’ with your actual login info.

You must include or require this file in the beginning of your script. I prefer using require because we don’t want the script to load at all if this file is missing.

require_once 'connection.php';

// your code goes here

In case you are wondering, that is the main difference between using include or require in PHP. Using “include” function will still load the page no matter if the file is missing or not but using “require” would immediately stop the page from loading any further if the file is not found.

This is a very basic tutorial but important as well. It might help you get better at the initial stage of web development. You must also try to keep the project organized as well by keeping the files in their respected folders. I will share a new tutorial about organizing PHP projects as well.

Thank you for reading. I Will keep updating this page or any tutorial which might needs update in the future.