SQL Queries and CRUD OperationsSQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows users to create, retrieve, update, and delete data within a database. The fundamental set of operations that interact with a database are commonly referred to by the acronym CRUD:
1. C - Create (INSERT): This operation involves adding new records or rows of data into a database table. The SQL command used for creation is `INSERT INTO`.
* Syntax Example: `INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);`
2. R - Read (SELECT): This operation involves retrieving data from one or more database tables. It allows you to query the database and fetch specific information based on certain criteria. The SQL command for reading is `SELECT`.
* Syntax Example: `SELECT column1, column2 FROM table_name WHERE condition;`
3. U - Update (UPDATE): This operation involves modifying existing records in a database table. You can change the values of one or more columns for rows that meet specified conditions. The SQL command for updating is `UPDATE`.
* Syntax Example: `UPDATE table_name SET column1 = new_value1, column2 = new_value2 WHERE condition;`
4. D - Delete (DELETE): This operation involves removing existing records from a database table. You can delete specific rows based on certain conditions. The SQL command for deleting is `DELETE FROM`.
* Syntax Example: `DELETE FROM table_name WHERE condition;`
When interacting with SQL databases from a programming language like PHP, it's crucial to use secure methods like prepared statements (e.g., via PDO - PHP Data Objects). Prepared statements help prevent SQL injection attacks by separating the SQL logic from the actual data.
Example Code
<?php
// --- Database Configuration ---
$host = 'localhost';
$db_name = 'my_database';
$username = 'root';
$password = '';
// --- Database Connection (PDO) ---
try {
$pdo = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Database connected successfully!\n\n";
// --- Create Table (if not exists) for demonstration ---
$createTableSQL = "
CREATE TABLE IF NOT EXISTS products (
id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
";
$pdo->exec($createTableSQL);
echo "Table 'products' ensured.\n\n";
// --- CRUD Operations ---
// C - CREATE (INSERT new product)
echo "\n--- Creating a new product ---\n";
$name = "Laptop X";
$price = 1200.00;
$stock = 50;
$insertSQL = "INSERT INTO products (name, price, stock) VALUES (:name, :price, :stock)";
$stmt = $pdo->prepare($insertSQL);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':stock', $stock);
$stmt->execute();
$lastId = $pdo->lastInsertId();
echo "Product '{$name}' added with ID: {$lastId}\n";
$name2 = "Mouse Y";
$price2 = 25.50;
$stock2 = 200;
$stmt->execute([':name' => $name2, ':price' => $price2, ':stock' => $stock2]);
echo "Product '{$name2}' added with ID: {$pdo->lastInsertId()}\n";
// R - READ (SELECT all products)
echo "\n--- Reading all products ---\n";
$selectSQL = "SELECT id, name, price, stock FROM products";
$stmt = $pdo->query($selectSQL);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($products) {
foreach ($products as $product) {
echo "ID: {$product['id']}, Name: {$product['name']}, Price: {$product['price']}, Stock: {$product['stock']}\n";
}
} else {
echo "No products found.\n";
}
// R - READ (SELECT a specific product by ID)
echo "\n--- Reading a specific product (ID: {$lastId}) ---\n";
$productIdToRead = $lastId;
$selectSingleSQL = "SELECT id, name, price, stock FROM products WHERE id = :id";
$stmt = $pdo->prepare($selectSingleSQL);
$stmt->bindParam(':id', $productIdToRead, PDO::PARAM_INT);
$stmt->execute();
$product = $stmt->fetch(PDO::FETCH_ASSOC);
if ($product) {
echo "Found - ID: {$product['id']}, Name: {$product['name']}, Price: {$product['price']}, Stock: {$product['stock']}\n";
} else {
echo "Product with ID {$productIdToRead} not found.\n";
}
// U - UPDATE (Update product stock by ID)
echo "\n--- Updating product (ID: {$lastId}) ---\n";
$newStock = 45;
$productIdToUpdate = $lastId;
$updateSQL = "UPDATE products SET stock = :stock WHERE id = :id";
$stmt = $pdo->prepare($updateSQL);
$stmt->bindParam(':stock', $newStock, PDO::PARAM_INT);
$stmt->bindParam(':id', $productIdToUpdate, PDO::PARAM_INT);
$stmt->execute();
if ($stmt->rowCount() > 0) {
echo "Product with ID {$productIdToUpdate} stock updated to {$newStock}.\n";
// Verify update
$stmt = $pdo->prepare($selectSingleSQL);
$stmt->bindParam(':id', $productIdToUpdate, PDO::PARAM_INT);
$stmt->execute();
$updatedProduct = $stmt->fetch(PDO::FETCH_ASSOC);
echo "New stock after update: {$updatedProduct['stock']}\n";
} else {
echo "No product found with ID {$productIdToUpdate} to update or no changes made.\n";
}
// D - DELETE (Delete a product by ID)
echo "\n--- Deleting a product (ID: {$lastId}) ---\n";
$productIdToDelete = $lastId;
$deleteSQL = "DELETE FROM products WHERE id = :id";
$stmt = $pdo->prepare($deleteSQL);
$stmt->bindParam(':id', $productIdToDelete, PDO::PARAM_INT);
$stmt->execute();
if ($stmt->rowCount() > 0) {
echo "Product with ID {$productIdToDelete} deleted successfully.\n";
} else {
echo "No product found with ID {$productIdToDelete} to delete.\n";
}
// R - READ (Verify deletion)
echo "\n--- Verifying deletion ---\n";
$stmt = $pdo->query($selectSQL);
$remainingProducts = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($remainingProducts) {
echo "Remaining products:\n";
foreach ($remainingProducts as $product) {
echo "ID: {$product['id']}, Name: {$product['name']}\n";
}
} else {
echo "All products deleted or no products remaining.\n";
}
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
} finally {
// Close the connection
$pdo = null;
echo "\nDatabase connection closed.\n";
}
?>