INSTALLING MYSQL

The easiest way to get your own web server is to download and install XAMPP – Apache, MySQL, PHP, and Perl all-in-one installer package. If not, you can also choose to manually install MySQL… Linux users, you can easily do a command line fetch:

sudo apt-get install mysql-server

Windows users, you will have to manually download and run the MySQL installer.

PHP.INI CONFIGURATION

Once you have installed MySQL, please do make sure that the respective PHP MySQL Extension is enabled in the php.ini file.

extension=mysql
extension=mysqli
extension=pdo_mysql

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 improved MySQL 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 a ton of other databases; PDO is the way to go if you want to future proof your project.

So yep, if you check under your php.ini file, you will realize that there are other PDO database extensions as well – Firebird, SQLite, ODBC. It will be wise to use PDO, and this makes switching the database a lot easier. For example, we may want to upgrade from MySQL to the more professional Oracle in the future. All we need to do is to change the PDO extension, with no changes required on the PHP code.

VERIFICATION

To verify if MySQL is properly installed, we can write a simple PHP file with a single line:

Access this script in your web browser, and see if the extensions are enabled.

DATABASE MANAGEMENT TOOL

If you have installed the XAMPP package, it comes with a very useful database management tool that you can easily access from http://localhost/phpmyadmin. If not, you can also manually download it from the phpMyAdmin official website.

DUMMY TABLE & DATA

For the purpose of demonstration, here is a small dummy table that we shall use as an example in this guide – Import this (using phpMyAdmin) into your own database if you like.

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`id`, `name`) VALUES
(1, 'John Doe'),
(2, 'Jane Doe'),
(3, 'Jay Doe'),
(4, 'Joy Doe'),
(5, 'Jordan Doe');

ALTER TABLE `test`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `test`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

THE SCRIPT

 PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false
    ]
  );
}

// ERROR - THROW ERROR MESSAGE OR SOMETHING
catch (Exception $ex) {
  print_r($ex);
  die();
}

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

// (4) CLOSE THE DATABASE CONNECTION
// Optional. PHP should do this automatically, but good to manually make sure anyway.
if ($stmt !== null) { $stmt = null; }
if ($pdo !== null) { $pdo = null; }
?>

THE QUICK EXPLANATION

• Use $pdo = new PDO(DATA-SOURCE-NAME, 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.

THE SCRIPT

connect_error) {
  echo $mysqli->connect_error;
  die();
}

// (3) SET CHARSET
$mysqli->set_charset(DB_CHARSET);

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

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

THE SCRIPT

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.

REFERENCES

• PDO
• MySQLi
• MySQL

MORE DATABASE YOGA

What’s next after the connection? Here are more examples:

PHP Database – Comprehensive Beginner’s Guide (Examples Included)

QUICK START

Skipped the entire tutorial? Here are a few quick steps to set up the example:

• Make sure that you have properly set up your own copy of MySQL first.
• Download and unzip into your project folder.
• 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.

SOURCE 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.
 

The post 3 Ways to Connect to MySQL in PHP (Examples Included) appeared first on Code Boxx.