Welcome to The Coding College, your ultimate destination for mastering coding concepts! In this guide, we’ll dive into how PHP interacts with MySQL, one of the most popular relational database management systems. By the end of this tutorial, you will understand how to connect to a MySQL database, perform CRUD (Create, Read, Update, Delete) operations, and manage your data using PHP.
Why Use PHP with MySQL?
PHP and MySQL are a powerful duo for building dynamic, database-driven web applications. Here’s why:
- Open Source: Both PHP and MySQL are free and widely supported.
- Ease of Use: PHP offers built-in functions to interact with MySQL databases efficiently.
- Cross-Platform: PHP and MySQL can run on almost any operating system.
- High Performance: Great for building scalable and high-performing web applications.
Prerequisites
To follow this tutorial, ensure you have the following:
- A server environment with PHP installed (e.g., XAMPP, WAMP, or LAMP).
- MySQL installed and running.
- Basic understanding of PHP and SQL syntax.
Connecting PHP to MySQL
The first step is to establish a connection to the MySQL database using PHP. This can be done using the MySQLi or PDO extensions.
Using MySQLi (Improved MySQL)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully!";
?>Using PDO (PHP Data Objects)
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully!";
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>Creating a MySQL Database
You can create a database using PHP by executing an SQL CREATE DATABASE query.
<?php
$servername = "localhost";
$username = "root";
$password = "";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Create database
$sql = "CREATE DATABASE my_database";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully!";
} else {
echo "Error creating database: " . $conn->error;
}
$conn->close();
?>Creating a Table
Once the database is created, you can create a table inside it.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "my_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL to create table
$sql = "CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(30) NOT NULL,
email VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
echo "Table Users created successfully!";
} else {
echo "Error creating table: " . $conn->error;
}
$conn->close();
?>CRUD Operations in PHP
1. Insert Data
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully!";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>2. Read Data
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
}
} else {
echo "No records found.";
}
$conn->close();
?>3. Update Data
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
$sql = "UPDATE Users SET email='newemail@example.com' WHERE username='JohnDoe'";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully!";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>4. Delete Data
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
$sql = "DELETE FROM Users WHERE username='JohnDoe'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully!";
} else {
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>Securing Your Database with Prepared Statements
To protect your database from SQL Injection, always use prepared statements.
Example: Using Prepared Statements
<?php
$conn = new mysqli("localhost", "root", "", "my_database");
$stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (?, ?)");
$stmt->bind_param("ss", $username, $email);
$username = "JaneDoe";
$email = "jane@example.com";
$stmt->execute();
echo "Record inserted successfully!";
$stmt->close();
$conn->close();
?>Connecting PHP to MySQL with PDO
The PDO extension provides more flexibility and supports multiple database types. Here’s how to insert data with prepared statements using PDO:
<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=my_database", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("INSERT INTO Users (username, email) VALUES (:username, :email)");
$stmt->bindParam(':username', $username);
$stmt->bindParam(':email', $email);
$username = "MikeSmith";
$email = "mike@example.com";
$stmt->execute();
echo "Record inserted successfully!";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>Best Practices for PHP and MySQL
- Use Prepared Statements: Prevent SQL injection by avoiding raw queries with user input.
- Validate User Input: Always sanitize and validate data before inserting it into the database.
- Use PDO: Provides better security and flexibility compared to MySQLi.
- Use Transactions: For operations that involve multiple queries, ensure data integrity by wrapping them in transactions.
- Handle Errors Gracefully: Use
try-catchblocks to handle database errors. - Keep Credentials Secure: Avoid hardcoding sensitive information in your files—use environment variables instead.
Conclusion
By combining PHP with MySQL, you can build dynamic, database-driven web applications with ease. In this tutorial, we covered the basics of connecting PHP to MySQL, creating databases and tables, and performing CRUD operations. For more advanced topics, including MySQL joins, transactions, and stored procedures, stay tuned to The Coding College.