Connecting to MySQL and Performing CRUD Operations
Working with MySQL databases is a core aspect of web development, and PHP makes it seamless. With XAMPP providing a local development environment, you can easily connect PHP to MySQL and perform CRUD (Create, Read, Update, Delete) operations.
In this blog, we’ll walk through setting up the connection and performing CRUD operations using PHP and MySQL, with an emphasis on understanding the theory behind each step.
Prerequisites
- Install XAMPP: Download and install XAMPP from apachefriends.org.
- XAMPP is an open-source platform that bundles Apache, MySQL, PHP, and Perl, making it easy to set up a local server environment.
- Start Apache and MySQL: Launch the XAMPP Control Panel and start the Apache and MySQL services.
- Apache acts as the web server, while MySQL is the database server.
- Create a Database:
- Open your browser and go to
http://localhost/phpmyadmin. - This is the MySQL administration tool where you can manage databases visually.
- Create a database named
testdb. - Add a table named
userswith the following fields:id(INT, AUTO_INCREMENT, PRIMARY KEY): A unique identifier for each user.name(VARCHAR(100)): The user’s name.email(VARCHAR(100)): The user’s email address.age(INT): The user’s age.
- Open your browser and go to
Step 1: Connect PHP to MySQL
What is Database Connection?
A database connection allows PHP to communicate with the MySQL database server to store, retrieve, and manipulate data. Using the mysqli extension, we can establish a secure connection.
Database Configuration File (db.php):
<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";
// Create connection
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
- Theory:
localhost: Refers to the local machine where XAMPP is running.root: The default username for MySQL in XAMPP."": The default password is empty.mysqli: A PHP extension used for interacting with MySQL databases.
Step 2: Perform CRUD Operations
CRUD operations are the backbone of database management. Let’s explore each operation in detail.
Create Operation (Insert Data)
Theory
The Create operation inserts new records into the database. This is achieved using the INSERT INTO SQL statement.
Example:
<?php
include 'db.php';
$name = "John Doe";
$email = "[email protected]";
$age = 25;
$sql = "INSERT INTO users (name, email, age) VALUES ('$name', '$email', $age)";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
?>
- Theory:
INSERT INTO: Adds a new row to the specified table.VALUES: Defines the data for each column.
Read Operation (Retrieve Data)
Theory
The Read operation fetches data from the database using the SELECT SQL statement.
Example:
<?php
include 'db.php';
$sql = "SELECT id, name, email, age FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "0 results";
}
?>
- Theory:
SELECT: Retrieves specified columns from the table.fetch_assoc(): Fetches each row as an associative array.
Update Operation (Modify Data)
Theory
The Update operation modifies existing records in the database. This is done using the UPDATE SQL statement.
Example:
<?php
include 'db.php';
$id = 1;
$newEmail = "[email protected]";
$sql = "UPDATE users SET email='$newEmail' WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
?>
- Theory:
UPDATE: Modifies existing rows.WHERE: Specifies the rows to be updated.
Delete Operation (Remove Data)
Theory
The Delete operation removes records from the database using the DELETE SQL statement.
Example:
<?php
include 'db.php';
$id = 1;
$sql = "DELETE FROM users WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
?>
- Theory:
DELETE FROM: Removes rows from a table.WHERE: Specifies which rows to delete.
Best Practices
- Use Prepared Statements: To prevent SQL injection, always use prepared statements instead of directly embedding variables in queries.
- Error Handling: Add detailed error handling for better debugging.
- Close Connections: Always close the database connection when done.
Closing the Connection:
$conn->close();
Conclusion
This guide provided a theoretical and practical approach to connecting PHP to MySQL and performing basic CRUD operations using XAMPP. By mastering these fundamentals, you’re equipped to build dynamic, data-driven applications.
Kushagra Kumar Mishra
Latest posts by Kushagra Kumar Mishra (see all)
- Understanding Encapsulation in PHP: Protect Your Data - January 31, 2025
- Connecting to MySQL and Performing CRUD Operations - January 31, 2025
- Tables in PHP: A Comprehensive Guide - January 31, 2025
Recent Comments