Understanding PHP and MySQL Integration

When developing dynamic web applications, one of the most essential skills you'll need is the ability to effectively connect PHP with MySQL. This integration allows your application to pull information from a database, modify it, and display it to the user in real-time. In this article, we’ll explore how to connect PHP to MySQL and develop a simple web application that showcases this interaction.

Setting Up the Environment

Before diving into the coding aspects, ensure that you have a suitable development environment set up. You will need:

PHP: The latest version of PHP installed on your local server (e.g., XAMPP, MAMP, or WAMP).
MySQL: Typically included with XAMPP/WAMP/MAMP installations.
A code editor: Visual Studio Code, Sublime Text, or any text editor of your choice.

Once your environment is ready, you can create a new directory for your project in the htdocs folder if you're using XAMPP or your designated web directory.

Creating the Database and Table

To start, we need to create a MySQL database and a table to store our data. You can use phpMyAdmin to do this, which is usually accessible via http://localhost/phpmyadmin.

Step 1: Create a Database

Open phpMyAdmin and click on the "Databases" tab.
Enter a name for your new database (for example, test_db) and click "Create".

Step 2: Create a Table

Click on the newly created database in the left sidebar.
Click on the “SQL” tab to enter a SQL query.
Run the following SQL statement to create a users table:

CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This SQL command creates a simple users table with id, name, email, and created_at columns.

Connecting PHP to MySQL

To work with the database in PHP, you will need to establish a connection. PHP provides several ways to connect to MySQL, namely using the mysqli extension or PDO (PHP Data Objects). For simplicity, we’ll use mysqli in this example.

Step 1: Create the Connection

Create a new file named db.php in your project directory. This file will handle the connection to the MySQL database.

<?php
$servername = "localhost";
$username = "root"; // default username for XAMPP/WAMP
$password = ""; // default password is empty for XAMPP/WAMP
$dbname = "test_db"; // database name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Step 2: Testing the Connection

You can test the connection by creating a simple PHP file called test_connection.php in your project directory.

<?php
include 'db.php';

if ($conn) {
    echo "Successfully connected to the database!";
} else {
    echo "Failed to connect to the database.";
}
?>

Visit http://localhost/your_project_directory/test_connection.php, and if everything is set up correctly, you should see a success message.

Performing CRUD Operations

Now that we've established a connection, it’s time to create a simple CRUD (Create, Read, Update, Delete) application for managing users.

Create User

We'll create a form to insert new users into the users table.

Create a file named create_user.php:

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name = $_POST['name'];
    $email = $_POST['email'];
    
    $sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";
    
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

?>

<form method="post">
    Name: <input type="text" name="name" required>
    Email: <input type="email" name="email" required>
    <input type="submit" value="Create User">
</form>

Read Users

To display users from the users table, create another file named read_users.php:

<?php
include 'db.php';

$sql = "SELECT id, name, email, created_at FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><tr><th>ID</th><th>Name</th><th>Email</th><th>Created At</th></tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["id"] . "</td><td>" . $row["name"] . "</td><td>" . $row["email"] . "</td><td>" . $row["created_at"]. "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

Update User

Updating user information can be achieved by creating a form similar to our create user form:

Create a file named update_user.php:

<?php
include 'db.php';

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $id = $_POST['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    
    $sql = "UPDATE users SET name='$name', email='$email' WHERE id='$id'";
    
    if ($conn->query($sql) === TRUE) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . $conn->error;
    }
}

$id = $_GET['id'];
$user_sql = "SELECT * FROM users WHERE id='$id'";
$user_result = $conn->query($user_sql);
$user = $user_result->fetch_assoc();
?>

<form method="post">
    <input type="hidden" name="id" value="<?php echo $user['id']; ?>">
    Name: <input type="text" name="name" value="<?php echo $user['name']; ?>" required>
    Email: <input type="email" name="email" value="<?php echo $user['email']; ?>" required>
    <input type="submit" value="Update User">
</form>

Delete User

Finally, to delete a user, you can add the following code to either the read_users.php file or create a new delete_user.php:

<?php
include 'db.php';

$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id='$id'";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Conclusion

Integrating PHP with MySQL paves the way for creating dynamic and interactive web applications. In this article, we've established a basic connection and conducted CRUD operations to manage users. As you grow more comfortable with PHP and MySQL, you can expand upon this foundation by incorporating features like user authentication, input validation, and more complex queries.

By practicing these skills and building upon them, you’ll be well on your way to mastering PHP and MySQL integration in no time!

Programming - PHP