3b) Design Webpage for Data collection, store, retrieve
and manipulate data using SQL database using
PHP(CRUD):
index.php
<!DOCTYPE html>
<html>
<head>
<title>CRUD Operations</title>
</head>
<body>
<h2>Create New User</h2>
<form action="crud.php" method="post">
Username: <input type="text" name="username"><br><br>
Email: <input type="email" name="email"><br><br>
<input type="submit" name="create" value="Create User">
</form>
<hr>
<h2>Update User</h2>
<form action="crud.php" method="post">
User ID: <input type="number" name="id"><br><br>
Username: <input type="text" name="username"><br><br>
Email: <input type="email" name="email"><br><br>
<input type="submit" name="update" value="Update User">
</form>
<hr>
<h2>Delete User</h2>
<form action="crud.php" method="post">
User ID: <input type="number" name="id"><br><br>
<input type="submit" name="delete" value="Delete User">
</form>
<hr>
<h2>View Users</h2>
<a href="crud.php?action=view">View Users</a>
</body>
</html>
crud.php
<?php
// Include the CRUD operations functions
include 'crud_operations.php';
// If the form is submitted for creating a new user
if (isset($_POST['create'])) {
$username = $_POST['username'];
$email = $_POST['email'];
echo create($username, $email);
}
// If the form is submitted for updating an existing user
if (isset($_POST['update'])) {
$id = $_POST['id'];
$username = $_POST['username'];
$email = $_POST['email'];
echo update($id, $username, $email);
}
// If the form is submitted for deleting a user
if (isset($_POST['delete'])) {
$id = $_POST['id'];
echo delete($id);
}
// If the action is to view users
if (isset($_GET['action']) && $_GET['action'] == 'view') {
$users = read();
if (is_array($users)) {
echo "<h2>Users:</h2>";
foreach ($users as $user) {
echo "ID: " . $user['id'] . "<br>";
echo "Username: " . $user['username'] . "<br>";
echo "Email: " . $user['email'] . "<br><br>";
}
} else {
echo $users;
}
}
?>
crud_operations.php
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase"; // Change this to your database name
$conn = new mysqli($servername, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// CREATE operation
function create($username, $email) {
global $conn;
$sql = "INSERT INTO users (username, email) VALUES ('$username',
'$email')";
if ($conn->query($sql) === TRUE) {
return "New record created successfully";
} else {
return "Error: " . $sql . "<br>" . $conn->error;
}
}
// READ operation
function read() {
global $conn;
$sql = "SELECT * FROM users";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
return $result->fetch_all(MYSQLI_ASSOC);
} else {
return "0 results";
}
}
// UPDATE operation
function update($id, $username, $email) {
global $conn;
$sql = "UPDATE users SET username='$username', email='$email' WHERE
id=$id";
if ($conn->query($sql) === TRUE) {
return "Record updated successfully";
} else {
return "Error updating record: " . $conn->error;
}
}
// DELETE operation
function delete($id) {
global $conn;
$sql = "DELETE FROM users WHERE id=$id";
if ($conn->query($sql) === TRUE) {
return "Record deleted successfully";
} else {
return "Error deleting record: " . $conn->error;
}
}
?>
Sample Outputs:
