PHP CRUD (Create, Read, Update, Delete) operations with MySQL are achieved using mysqli or PDO to interact with a database. The process involves a database connection file, an HTML form for input, and PHP scripts to execute INSERT, SELECT, UPDATE, and DELETE SQL queries, usually with prepared statements for security.
1. Database Table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
2. Database Connection (db.php)
<?php
$conn = new mysqli("localhost", "root", "", "test_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
?>
3. CREATE (Insert Data)
<?php
include 'db.php';
$name = "Nilesh";
$email = "nilesh@gmail.com";
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
if ($stmt->execute()) {
echo "Data inserted successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
4. READ (Fetch Data)
<?php
include 'db.php';
$stmt = $conn->prepare("SELECT id, name, email FROM users");
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Email: " . $row['email'] . "<br>";
}
$stmt->close();
$conn->close();
?>
5. UPDATE (Update Data)
<?php
include 'db.php';
$id = 1;
$name = "Updated Name";
$email = "updated@gmail.com";
$stmt = $conn->prepare("UPDATE users SET name=?, email=? WHERE id=?");
$stmt->bind_param("ssi", $name, $email, $id);
if ($stmt->execute()) {
echo "Data updated successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
6. DELETE (Delete Data)
<?php
include 'db.php';
$id = 1;
$stmt = $conn->prepare("DELETE FROM users WHERE id=?");
$stmt->bind_param("i", $id);
if ($stmt->execute()) {
echo "Data deleted successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
