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?

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?

 

 

 

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.php, 2-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.

 

 

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.

 

 

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().

 

 

 

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.

 

 

 

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