Current Location: Home> Latest Articles> SQL Database Operations in PHP: Query, Insert, Update, and Delete

SQL Database Operations in PHP: Query, Insert, Update, and Delete

M66 2025-07-04

SQL Database Operations in PHP

SQL (Structured Query Language) is the standard language used to manage data in relational databases. In PHP development, SQL is commonly used to handle database operations. This article provides detailed examples of how to use SQL in PHP for common tasks such as database connection, data insertion, querying, updating, and deletion.

Database Connection

Before performing database operations, we first need to establish a connection to the database. PHP provides several database extensions, such as mysqli and PDO. Below is an example of connecting to a MySQL database using the mysqli extension:


<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

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

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

echo "Connection successful";
?>

Insert Data

Once the connection is successful, you can use the SQL INSERT statement to insert data into the database. Below is an example of inserting data into the "users" table:


<?php
$sql = "INSERT INTO users (username, password, email) VALUES ('john', '123456', 'john@example.com')";

if ($conn->query($sql) === true) {
    echo "Data insertion successful";
} else {
    echo "Data insertion failed: " . $conn->error;
}

$conn->close();
?>

Query Data

Querying data from the database is one of the most common operations. You can use the SELECT statement to retrieve specific data. Below is an example of querying all data from the "users" table:


<?php
$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"] . " - Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Update Data

When you need to modify data in the database, you can use the UPDATE statement. Below is an example of updating a user's password in the "users" table:


<?php
$sql = "UPDATE users SET password='654321' WHERE username='john'";

if ($conn->query($sql) === true) {
    echo "Data update successful";
} else {
    echo "Data update failed: " . $conn->error;
}

$conn->close();
?>

Delete Data

When you need to delete data from the database, you can use the DELETE statement. Below is an example of deleting data from the "users" table:


<?php
$sql = "DELETE FROM users WHERE username='john'";

if ($conn->query($sql) === true) {
    echo "Data deletion successful";
} else {
    echo "Data deletion failed: " . $conn->error;
}

$conn->close();
?>

Conclusion

This article provided an overview of how to use SQL in PHP to perform common database operations. With the help of code examples, you can now better understand how to connect to a database, insert, query, update, and delete data. Mastering these operations will help you interact with databases more effectively.

Related