How to Perform DML Database Operations ? See Example

Once your PHP script is connected to a MySQL database, the next step is performing DML (Data Manipulation Language) operations.
DML includes the following actions:

• INSERT – Add new records
• SELECT – Retrieve records
• UPDATE – Modify existing records
• DELETE – Remove records

PHP performs these operations using either MySQLi or PDO.
Below are examples using MySQLi (Procedural) since it’s simple and widely used.

---

1. INSERT Operation (Adding Data)

Example: Insert a new user

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

$name = "Sagar";
$email = "sagar@example.com";

$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

if (mysqli_query($conn, $sql)) {
    echo "Record inserted successfully!";
} else {
    echo "Error: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

---

2. SELECT Operation (Fetching Data)

Example: Fetch all users

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . 
             " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "No records found!";
}

mysqli_close($conn);
?>

---

3. UPDATE Operation (Modifying Data)

Example: Update a user’s email

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

$sql = "UPDATE users SET email='newemail@example.com' WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully!";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

---

4. DELETE Operation (Removing Data)

Example: Delete a user

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

$sql = "DELETE FROM users WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully!";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

---

Best Practices for DML in PHP

✔ Always validate user input to avoid SQL injection
✔ Use Prepared Statements (MySQLi or PDO) for secure queries
✔ Handle errors using mysqli_error()
✔ Close the connection using mysqli_close()
✔ Use LIMIT when selecting large datasets for performance

---

Citations

🔗 View other articles about PHP:
http://savanka.com/category/learn/php/

🔗 External PHP Documentation:
https://www.php.net/manual/en/