Here’s a step-by-step guide for creating a basic CRUD (Create, Read, Update, Delete) application using PHP and MySQLi with prepared statements:
1. Set Up the Database
First, create a database and a table to store data.
SQL to create the database and table:
CREATE DATABASE crud_app;
USE crud_app;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);2. Project Structure
Organize your project files:
crud-app/
│
├── db.php // Database connection file
├── index.php // Main file for reading records
├── create.php // Form for adding new records
├── update.php // Form for updating existing records
├── delete.php // Logic to delete a record3. Database Connection (db.php)
<?php
$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'crud_app';
$conn = new mysqli($host, $user, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>4. Read Records (index.php)
<?php
require 'db.php';
// Fetch all records
$sql = "SELECT * FROM users";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CRUD Application</title>
</head>
<body>
<h1>Users</h1>
<a href="create.php">Add User</a>
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Action</th>
</tr>
<?php while ($row = $result->fetch_assoc()): ?>
<tr>
<td><?= $row['id']; ?></td>
<td><?= $row['name']; ?></td>
<td><?= $row['email']; ?></td>
<td>
<a href="update.php?id=<?= $row['id']; ?>">Edit</a>
<a href="delete.php?id=<?= $row['id']; ?>" onclick="return confirm('Are you sure?')">Delete</a>
</td>
</tr>
<?php endwhile; ?>
</table>
</body>
</html>5. Create Record (create.php)
<?php
require 'db.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ss', $name, $email);
if ($stmt->execute()) {
header("Location: index.php");
exit();
} else {
echo "Error: " . $stmt->error;
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Add User</title>
</head>
<body>
<h1>Add User</h1>
<form method="post">
<label>Name:</label>
<input type="text" name="name" required>
<br>
<label>Email:</label>
<input type="email" name="email" required>
<br>
<button type="submit">Add</button>
</form>
</body>
</html>6. Update Record (update.php)
<?php
require 'db.php';
$id = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = $_POST['name'];
$email = $_POST['email'];
$sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssi', $name, $email, $id);
if ($stmt->execute()) {
header("Location: index.php");
exit();
} else {
echo "Error: " . $stmt->error;
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Edit User</title>
</head>
<body>
<h1>Edit User</h1>
<form method="post">
<label>Name:</label>
<input type="text" name="name" value="<?= $user['name']; ?>" required>
<br>
<label>Email:</label>
<input type="email" name="email" value="<?= $user['email']; ?>" required>
<br>
<button type="submit">Update</button>
</form>
</body>
</html>7. Delete Record (delete.php)
<?php
require 'db.php';
$id = $_GET['id'];
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('i', $id);
if ($stmt->execute()) {
header("Location: index.php");
exit();
} else {
echo "Error: " . $stmt->error;
}
?>8. Conclusion
This is a simple CRUD application using PHP and MySQLi with prepared statements. Ensure you validate and sanitize user inputs in a production environment to avoid SQL injection or other vulnerabilities.
Leave a Reply