1. Introduction
PHP Database connection is at the heart of modern web applications, providing a structured and persistent storage solution for managing data. Whether you are developing a simple blog or a complex e-commerce platform, understanding how to interact with databases is crucial. This lesson aims to equip you with the foundational knowledge and skills to work with databases using PHP 8, focusing on the MySQL database system.
From establishing a connection to a database, performing basic CRUD (Create, Read, Update, Delete) operations, to ensuring secure interactions, this lesson covers a comprehensive overview to get you started. If you need a refresher on previous topics, feel free to navigate through the PHP 8 Free Course index.
PHP database connection
2. Introduction to MySQLi and PDO
MySQLi and PDO are both PHP libraries for interacting with MySQL databases. However, there are some key differences between the two.
Database support
- PDO: PDO supports a variety of different database systems, including MySQL, PostgreSQL, Oracle, and Microsoft SQL Server. This makes it a more versatile choice for projects that may need to switch database systems in the future.
- MySQLi: MySQLi is only specifically designed to work with MySQL databases. This can be limiting if you need to use a different database system.
Data Access Abstraction Layer (DAL)
- PDO: PDO is a data access abstraction layer (DAL), which means that it provides a consistent interface for interacting with different database systems. This makes it easier to write code that is portable and reusable.
- MySQLi: MySQLi is a specific implementation for MySQL databases. This means that the code you write will only work with MySQL databases.
API support
- PDO: PDO provides an object-oriented API, which can make it more readable and maintainable.
- MySQLi: MySQLi provides both an object-oriented and procedural API. The procedural API is similar to the old-school PHP style of coding.
Prepared statements
- PDO: PDO supports prepared statements, which are a way of protecting against SQL injection attacks. Prepared statements allow you to bind values to a SQL query at runtime, rather than hardcoding them into the query itself. This makes it more difficult for malicious users to inject SQL code into your application.
- MySQLi: MySQLi also supports prepared statements, but the syntax for using them is slightly different from PDO.
Performance
In general, PDO and MySQLi have similar performance characteristics. However, some benchmarks have shown that PDO can be slightly faster for certain types of queries.
Security
Both PDO and MySQLi can be used to write secure database applications. However, PDO is generally considered to be more secure because it supports prepared statements, which can help to prevent SQL injection attacks.
Recommendation
In general, PDO is a more versatile and secure choice for PHP developers than MySQLi. It is recommended to use PDO for new projects, and to migrate existing projects to PDO if possible.
3. PHP Database connection
Connecting to a database is the first step in database interaction. Below is how you can connect to a MySQL database using PDO.
// Using PDO
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
$username = 'username';
$password = 'password';
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
// Explanation:
// 1. Set up a DSN (Data Source Name) with the database host, name, and charset.
// 2. Provide the username and password for database access.
// 3. Define options for the PDO instance.
// 4. Attempt to create a new PDO instance to connect to the database.
// 5. Catch any connection errors and display an error message.4. Creating Data (INSERT)
Inserting new data into the database is a common task. Here’s how you can do it using a prepared statement in PDO to prevent SQL injection.
$query = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$query->execute(['name' => 'John Doe', 'email' => 'john.doe@example.com']);
// Explanation:
// 1. Prepare an SQL INSERT statement with placeholders for the values to be inserted.
// 2. Execute the prepared statement with the actual values, securely inserting the data.5. Reading Data (SELECT)
Retrieve data from the database using an SQL SELECT statement.
$query = $pdo->query("SELECT * FROM users");
while ($row = $query->fetch()) {
echo $row['name'] . ' - ' . $row['email'] . '<br>';
}6. Updating Data (UPDATE)
Modify existing data in the database using an SQL UPDATE statement.
$query = $pdo->prepare("UPDATE users SET email = :email WHERE name = :name");
$query->execute(['email' => 'new.email@example.com', 'name' => 'John Doe']);7. Deleting Data (DELETE)
Remove data from the database using an SQL DELETE statement.
$query = $pdo->prepare("DELETE FROM users WHERE name = :name");
$query->execute(['name' => 'John Doe']);8. Prepared Statements
Use prepared statements to prevent SQL injection, a common web application security vulnerability.
$query = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$query->execute(['email' => $email]);9. Closing the Database Connection
It’s a good practice to close the database connection once done.
$pdo = null;10. Exercises
- Database Interaction:
- Set up a local PHP database connection and create a simple PHP script to insert, read, update, and delete data.
- Secure Database Interaction:
- Modify your script to use prepared statements for secure database interaction.
11. Further Reading and Resources
Proceed with Lesson 10: Security to delve into ensuring the security of your PHP applications. For a broader range of programming topics, explore the programming section on our blog.
