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

1. 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.
2. 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.
3. 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 users with 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.

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

1. Use Prepared Statements: To prevent SQL injection, always use prepared statements instead of directly embedding variables in queries.
2. Error Handling: Add detailed error handling for better debugging.
3. 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.

The following two tabs change content below.

• Bio
• Latest Posts

Kushagra Kumar Mishra

Kushagra Mishra is a recent BCA graduate with a strong foundation in web development technologies. He possesses a comprehensive skillset encompassing HTML, CSS, JavaScript, Python, PHP, and React, honed through over two years of practical experience. With a keen interest in Python, AI, and PHP, Kushagra is driven to share his knowledge and contribute to the growth of the tech community. His academic record, with an overall score of 76% across six semesters, further solidifies his dedication and proficiency. Kushagra is eager to engage with fellow learners and contribute to the evolving landscape of technology.

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