PHP MySQL Connection
In this article, we will discuss how to establish a PHP MySQL Connection to create a database-driven website. There will be a brief explanation of how to establish a connection, execute queries, and handle errors with examples.
By the end of this article, you will have a good understanding of how PHP interacts with the MySQL database and what you can achieve with its help.
In order to create dynamic web pages and applications, PHP is a popular server-side programming language and MySQL is one of the most widely used open-source relational database management systems on the market today.
Using both these technologies together allows you to build powerful web applications that are capable of storing data, manipulating it and retrieving it at any time.
PHP MySQL Connection
A PHP MySQL Connection is a link established between PHP and MySQL that enables PHP scripts to interact with MySQL databases.
In simpler terms, it is a way for PHP to communicate with MySQL databases and perform operations such as inserting, updating, retrieving, and deleting data.
The connection between PHP and MySQL is established using PHP’s built-in functions, which allow you to connect to a MySQL database, execute SQL queries, and handle errors.
MySQLi or PDO?
The short answer is “Whatever you like“.
PDO and MySQL both have their own advantages.
A PDO database can work with up to 12 different database systems, but a MySQLi database can only work with MySQL databases.
Therefore, if you need to change your project’s database in the future, PDO makes it very easy to do so. Changing the connection string as well as a few queries is all that needs to be done. On the other hand, if you want to use MySQLi, then you will need to rewrite all your code, including the queries, in order to make this work.
The APIs of both are object-oriented but MySQLi does also support procedural API.
Both accept Prepared Statements. A prepared statement protects the application from SQL injection, and it is one of the most important security features in a web application.
MySQL Examples – MySQLi and PDO
We will demonstrate three ways of using PHP and MySQL in this chapter:
- MySQLi (object-oriented)
- MySQLi (procedural)
- PDO
MySQLi Installation
Linux and Windows users should see the MySQLi extension installed automatically as part of the php5 mysql package, which is installed by default in most cases.
For more information about installation, you can visit the following website:
http://php.net/manual/en/mysqli.installation.php
PDO Installation
Please refer to the following website for installation details:
http://php.net/manual/en/pdo.installation.php
Set up MySQL Database
The first step is to set up a MySQL database. You can create a MySQL database using phpMyAdmin or any other MySQL management tool.
Once you have created the database, note down the database name, username, and password as you will need them to establish the connection.
Open a Connection to MySQL
We need to connect to the MySQL server before we can access the database:
Below is an example using MySQLi’s Object-Oriented method:
Example:
You will need to use the following code if you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0.
// Checking connection
if (mysqli_connect_error()) {
die(“Database connection failed: ” . mysqli_connect_error());
}Example
Below is an example using MySQL PDO(PHP Data Objects) method:
Example:
Example Explanation
This PHP script connects to a MySQL database using PDO (PHP Data Objects). Here’s a breakdown of what each line of code does:
- $servername = “localhost”;: This sets the hostname of the MySQL server.
- $username = “my_user”;: This sets the MySQL username that the PHP script will use to connect to the database.
- $password = “my_pass”;: This sets the password for the MySQL user specified in the previous line.
- try { … }: This code block begins a try-catch block, which is used to handle errors that may occur during the database connection process.
- $conn = new PDO(“mysql:host=$servername;dbname=test_db”, $username, $password);: This creates a new PDO object that represents a connection to the MySQL server using the credentials specified in the previous lines of code. test_db is the name of the database that the script will connect to.
- $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);: This sets the error mode of the PDO object to ERRMODE_EXCEPTION, which means that PDO will throw an exception when an error occurs instead of returning a false value.
- echo “Connected successfully”;: If the database connection is successful, this line will be executed and the script will output “Connected successfully” to the browser.
- catch (PDOException $e) { … }: This code block catches any exceptions thrown by PDO during the database connection process and outputs an error message to the browser.
- echo “Connection failed: ” . $e->getMessage();: This line outputs an error message if the database connection fails.
PHP MySql Connection Terminate
The connection will be terminated automatically when the script ends. To close an existing connection before terminating automatically, use the following:
MySQLi Object-Oriented:
$conn->close();
MySQLi Procedural:
mysqli_close($conn);
PDO:
$conn = null;
