3 Ways to Connect to MySQL in PHP – PDO MYSQLI MYSQL

Welcome to a tutorial on how to connect to MySQL in PHP. So you are done with the basics of PHP and looking to dabble in the dark arts of the database?

When it comes to connecting to the MySQL database in PHP, there are 3 extensions that we can use:

  1. PHP Database Object (PDO)$pdo = new PDO("mysql:host=HOST;dbname=NAME;charset=utf8", USER, PASSWORD);
  2. MYSQLI (MySQL Improved)$mysqli = new mysqli(HOST, USER, PASSWORD, NAME);
  3. MYSQL (Deprecated)$mysql = new mysql(HOST, USER, PASSWORD);

It is recommended to use PDO as it also works with other databases such as Firebird, SQLite, Postgre, and more.

That covers the basics, but how do we run SQL statements? Let us run through a few more examples – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

REAL QUICK TUTORIAL

 

TABLE OF CONTENTS

Download & Notes PDO Extension MySQLi Extension
MySQL Extension Useful Bits & Links What’s Next?

 

 

DOWNLOAD & NOTES

First, here is the download link to the example source code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Please make sure that the respective extension=mysql, extension=mysqli, or extension=pdo_mysql is enabled in php.ini.
  • Create a test database and import 0-dummy.sql if you want.
  • 1-pdo.php2-mysqli.php, and 3-mysql.php will show the respective methods to connect to the database – Just remember to change the database settings in the scripts.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

PHP PDO EXTENSION

All right, let us now start with the PHP Database Object (PDO) extension, and this is also the one that I will highly recommend adopting.

 

MYSQL DATABASE CONNECTION WITH PHP PDO

1-pdo.php
<?php
// (A) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (B) ATTEMPT DATABASE CONNECTION
try {
  $pdo = new PDO(
    "mysql:host=". DB_HOST .";dbname=". DB_NAME .";charset=". DB_CHARSET,
    DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

// (C) CONNECTION OK
// Fetch some entries, do some SQL yoga
$stmt = $pdo->prepare("SELECT * FROM `test` WHERE `id`<=?"); $stmt->execute([3]);
$results = $stmt->fetchAll();
print_r($results);

// (D) CLOSE THE DATABASE CONNECTION
// Optional. GC should do these automatically, but good to make sure anyway.
if ($stmt !== null) { $stmt = null; }
if ($pdo !== null) { $pdo = null; }

 

 

THE QUICK EXPLANATION

  • Use $pdo = new PDO(HOST-DATABASE-CHARSET, USER, PASSWORD, OPTIONS) to connect to the database.
  • $stmt = $pdo->prepare(SQL) will prime the SQL query.
  • $stmt->execute([PARAMETERS]) will plug in the parameters and run the SQL query.
  • For select queries only, use $stmt->fetch() or $stmt->fetchAll() to get the results.

 

MYSQLI EXTENSION

This next method is a common one that you have probably seen everywhere – The MySQLi extension.

 

CONNECTING TO MYSQL USING MYSQLI

2-mysqli.php
<?php
// (A) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (B) ATTEMPT DATABASE CONNECTION
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if ($mysqli->connect_error) { exit($mysqli->connect_error); }
$mysqli->set_charset(DB_CHARSET);

// (C) CONNECTION OK
// Fetch some entries, do some SQL yoga
$result = $mysqli->query("SELECT * FROM `test` WHERE `id`<=3");
print_r($result); 
while ($row = $result->fetch_assoc()) { print_r($row); }
$result->close();

// (D) CLOSE THE DATABASE CONNECTION
$mysqli->close();

 

THE QUICK EXPLANATION

  • Use $mysqli = new mysqli(HOST, USER, PASSWORD, NAME) to connect to the database.
  • It is optional to set the charset $mysqli->set_charset(CHARSET), but recommended.
  • $result = $mysqli->query(SQL) will run a SQL query.
  • while ($row = $result->fetch_assoc()) will loop through the results (for select queries).
  • Remember to manually release the results $result->close(), or they will hog the system resources.
  • Finally, close the database connection $mysqli->close().

 

 

CONNECTING WITH MYSQL

This final method is outdated and defunct. Use it only if you need to support legacy systems.

 

LEGACY PHP MYSQL CONNECTION

3-mysql.php
<?php
// (A) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (B) ATTEMPT DATABASE CONNECTION
$mysql = new mysql(DB_HOST, DB_USER, DB_PASSWORD);
if (!$mysql) { exit(mysql_error()); }
mysql_select_db(DB_NAME, $mysql);
mysql_set_charset(DB_CHARSET, $mysql);

// (C) CONNECTION OK
// Fetch some entries, do some SQL yoga
$result = mysql_query("SELECT * FROM `test` WHERE `id`<=3");
print_r($result);
while ($row = mysql_fetch_array($result, MYSQL_NUM)) { print_r($row); }
mysql_free_result($result);

// (D) CLOSE THE DATABASE CONNECTION
mysql_close($mysql);

 

THE QUICK EXPLANATION

Well, don’t think this one needs a lot of explanation. It is pretty much the same as MySQLi, except that we use new mysql() instead.

 

 

USEFUL BITS & LINKS

That’s all for this guide, and here is a small section on some extras and links that may be useful to you.

 

PDO VS MYSQLI VS MYSQL

Why are there so many different extensions to deal with the database? Long story short:

  • MySQL – Historically, PHP has always been “bundled” with MySQL, and this is the exclusive PHP extension to support MySQL only. But it has since been deprecated and replaced by MySQLi. Do not use this unless you have to support the really old systems. It is also totally removed in PHP 7.
  • MySQLi – The MySQL improved extension. Better in terms of performance and stuff, but still not recommended.
  • PDO (PHP Data Objects) – As more databases (other than MySQL) start to become popular, the developers of PHP came up with PDO. This extension will support MySQL and also other databases; PDO is the way to go if you want to future proof your projects.

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

How to Connect to MySQL in PHP (Click to enlarge)

 

 

DATABASE MANAGEMENT TOOLS

There are quite a number of tools out there, but I will personally recommend 2 popular ones:

  • phpMyAdmin – A free PHP database manager. If you installed the XAMPP package, it is already installed at http://localhost/phpmyadmin.
  • MySQL Workbench – The so-called official MySQL database manager by Oracle themselves.

 

REFERENCES & LINKS

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *