Basic Usage of MySQL in a PHP Application

MySQL is one of the most popular open-source relational database management systems (RDBMS) used in web development. In this article, we’ll explore the fundamental steps to use MySQL in a PHP application. We’ll cover database connection, data manipulation, and error handling to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

MySQL: MySQL should be installed and running on your server or local development environment. You can download MySQL from the official MySQL website.
PHP: Ensure you have PHP installed. You can download PHP from the official PHP website.
Web Server: A web server like Apache or Nginx is required to run PHP scripts. If you don’t have one installed, consider using a prepackaged solution like XAMPP or WAMP.

Step 1: Connecting to MySQL

To connect to a MySQL database from a PHP application, you’ll need to use the MySQLi extension, which provides functions for interacting with the database. Create a connection to the MySQL server:

<?php
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database_name";

// Create a connection
$connection = new mysqli($servername, $username, $password, $database);

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

Replace "your_username", "your_password", and "your_database_name" with your MySQL credentials.

Step 2: Executing SQL Queries

Once connected, you can execute SQL queries to interact with the database. Let’s start with a simple example: inserting data into a table.

<?php
// SQL query to insert data into a "users" table
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')";

if ($connection->query($sql) === TRUE) {
    echo "Data inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}
?>

Step 3: Querying Data

You can retrieve data from MySQL tables using SQL queries. Here’s an example of selecting data from a table:

<?php
// SQL query to retrieve data from the "users" table
$sql = "SELECT id, username, email FROM users";
$result = $connection->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . ", Username: " . $row["username"] . ", Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No results found";
}
?>

Step 4: Updating and Deleting Data

Updating and deleting data in MySQL is straightforward. Here’s an example of updating a user’s email address:

<?php
// SQL query to update a user's email address
$sql = "UPDATE users SET email='[email protected]' WHERE username='john_doe'";

if ($connection->query($sql) === TRUE) {
    echo "Data updated successfully";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}
?>

And here’s an example of deleting a user:

<?php
// SQL query to delete a user
$sql = "DELETE FROM users WHERE username='john_doe'";

if ($connection->query($sql) === TRUE) {
    echo "User deleted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}
?>

Step 5: Error Handling and Closing the Connection

Always handle errors gracefully and close the MySQL connection when done.

<?php
// Closing the MySQL connection
$connection->close();
?>

Conclusion

In this article, we’ve covered the basic usage of MySQL in a PHP application. You’ve learned how to connect to a MySQL database, execute SQL queries for data manipulation, retrieve data, update records, and delete records. MySQL is a powerful RDBMS, and these fundamental skills are essential for building database-driven PHP web applications. As you continue your journey in web development, you’ll explore more complex database operations and best practices for secure and efficient data management.