Connecting to MySQL using PHP
In PHP, the recommended ways to connect to a MySQL database are about using the MySQLi extension or PDO (PHP Data Objects). The older mysql_connect related functions are deprecated and have been removed from modern versions of PHP, so you should avoid them.
Object-Oriented and procedural ways
MySQLi, which stands for MySQL Improved, is an extension specifically for working with MySQL databases. It offers a procedural and an object-oriented API. The object-oriented approach is generally preferred for its clarity and consistency. In general, the mysql extension is enabled by default in any hosting provider, but you can check the php.ini file to be sure.
<?php
$host = 'localhost'
$username = 'root';
$password = 'mypassword';
$databaseName = 'test';
$dbConnection = new mysqli($host, $username, $password, $databaseName);
if ($dbConnection->connect_error) {
die("Connection failed: " . $dbConnection->connect_error);
}
$dbConnection->close();
?>You can also connect using a procedural method, even tho this is less common nowadays:
$dbConnection = mysqli_connect($host, $username, $password, $dbname);
if (!$dbConnection ) {
die("Connection failed: " . mysqli_connect_error());
}
mysqli_close($dbConnection);Basically the main difference between the two methods is that the last one will use default PHP functions instead of referring to the object used for the connection (in this case, the variable $dbConnection)
PDO
PDO is a database abstraction layer. This means you can use the same PDO methods to connect to various database systems (like PostgreSQL or SQLite) with minimal code changes. This makes your code more portable in case you plan to make your application work on different database systems.
Here is an example on how to connect to a MySQL database:
$host = 'localhost'
$username = 'root';
$password = 'mypassword';
$databaseName = 'test';
try {
$conn = new PDO("mysql:host=$host;dbname=$databaseName", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}In order to have a more robust code, we put our connection inside a try/catch block, but this is optional.
The DSN, Data Source Name, is a string used to specify the database type and connection details.
Which methos is better?
PDO is generally recommended for its flexibility and portability. It supports a wider range of database drivers and its use of exceptions for error handling is a modern best practice.
MySQLi is a solid choice if you know for certain that your project will only use a MySQL database. It can be slightly faster for certain operations because it's a dedicated extension.
Both methods are secure and support prepared statements, which are crucial for preventing SQL injection attacks.
Conclusion
In my personal experience, changing the DBMS type on an application rarely happens, for example switching from MySQL to Postgres is not that common, because in any way you would need to change many other little things, starting from CREATE TABLE statements and the column type definitions.
But one possible scenario is when you are moving an old application to a new system. Let's suppose our customer is using a 15 years old web application running on a Microsoft database, and we want to redesign everything using Symfony and Postgres. In this case, a PDO would be really handy, on the other side such an old codebase would probably be using a lot of deprecated stuff.
In the end it really depends on what you plan to do in the long term. I hope this helped you understand how to connect to a MySQL database and what is the best way for your case!
If this guide was useful, follow me on Facebook and subscribe on Youtube!
Also read: