About Lesson
CRUD Operations:
CRUD stands for Create, Read, Update, Delete. These are the 4 main operations we perform on database records.
1. Create (INSERT data):
Syntax:
INSERT INTO table_name (column1, column2) VALUES (value1, value2);Example:
PHP
<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");
$sql = "INSERT INTO students (name, email) VALUES ('John Doe', 'john@example.com')";
if($conn->query($sql) === TRUE){
echo "New record created successfully";
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>Explanation:
INSERT INTO→ Adds new record to table.$conn->query($sql)→ Executes the query.$conn->error→ Shows error if insertion fails.
2. Read (Select Data):
Syntax:
SELECT column1, column2 FROM table_name WHERE condition;Example:
PHP
<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");
$sql = "SELECT id, name, email FROM students";
$result = $conn->query($sql);
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
echo "ID: " . $row['id'] . " Name: " . $row['name'] . " Email: " . $row['email'] . "<br>";
}
} else {
echo "No records found";
}
$conn->close();
?>Explanation:
$conn->query($sql)→ Executes SELECT query.$result->num_rows→ Checks if any rows exist.$result->fetch_assoc()→ Fetches one row at a time as associative array.
3. Update Data:
Syntax:
UPDATE table_name SET column1=value1 WHERE condition;Example:
PHP
<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");
$sql = "UPDATE students SET email='john.doe@example.com' WHERE id=1";
if($conn->query($sql) === TRUE){
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
$conn->close();
?>Explanation:
UPDATE ... SET ... WHERE→ Modifies existing record.- Always use
WHEREto prevent updating all records.
4. Delete Data:
Syntax:
DELETE FROM table_name WHERE condition;Example:
PHP
<?php
$conn = new mysqli("localhost", "root", "", "mydatabase");
$sql = "DELETE FROM students WHERE id=1";
if($conn->query($sql) === TRUE){
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>Explanation:
DELETE FROM ... WHERE→ Deletes record.$conn->query($sql)→ Executes deletion query.- Always include
WHEREto avoid deleting all rows.