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:
- PHP Database Object (PDO) –
$pdo = new PDO("mysql:host=HOST;dbname=NAME;charset=utf8", USER, PASSWORD);
- MYSQLI (MySQL Improved) –
$mysqli = new mysqli(HOST, USER, PASSWORD, NAME);
- 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
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
, orextension=pdo_mysql
is enabled inphp.ini
. - Create a test database and import
0-dummy.sql
if you want. 1-pdo.php
,2-mysqli.php
, and3-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
<?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
<?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
<?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

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
- PDO – PHP
- MySQLi – PHP
- MySQL – PHP
- Simple PHP Database Examples – Code Example
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!