Full CRUD Operations Using Object-Oriented.
First, this is an example code of (create, read, update, and delete), so take it as an inspiration and define the structure of the table and variable names according to your needs. There are various methods of CRUD like PDO, Procedural but here we are using the Object-Oriented Programming (OOPs) method which is modern and short. Because CSS is not included, it will be straightforward for PHP developers.
Files Order.
Follow the given steps.
- This is the database and table structure we're using.
-- Creating Database
CREATE DATABASE `crud_operation_db`;
-- Creating Table
CREATE TABLE `crud_operation_db`.`crud_operation_table` (
`crud_id` INT NOT NULL AUTO_INCREMENT,
`crud_name` VARCHAR(255) NOT NULL,
`crud_email` VARCHAR(255) NOT NULL,
`crud_number` INT NOT NULL,
PRIMARY KEY (`crud_id`)
) ENGINE = InnoDB;
1. config.php
- This is the connection we are creating to the database.
<?php
// Creating Variables
$server_name = "localhost";
$user_name = "root";
$password = "";
$db_name = "crud_operation_db";
// Creating Connection
$conn = new mysqli($server_name, $user_name, $password, $db_name);
// Checking Connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
2. index.php
- Here is the code for the landing page. Where we are creating a form to insert the value into the database and then retrieving it from the database which is being printed through a while loop with read, update, and delete buttons. which will put the crud_id as id in the URL.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CRUD</title>
</head>
<body>
<form action="create.php" method="POST">
<input type="text" name="crud_name" placeholder="Enter Name" required>
<input type="email" name="crud_email" placeholder="Enter Email" required>
<input type="number" name="crud_number" placeholder="Enter Number" required>
<button type="submit">Create</button>
<hr>
</form>
<?php
// Including config file
require_once 'config.php';
// Preparing query
$sql = "SELECT * FROM `crud_operation_table`;";
$result = $conn->query($sql);
// Checking if empty
if ($result->num_rows > 0) {
// Fetching by rows
while ($row = $result->fetch_assoc()) {
echo "ID : " . $row['crud_id'] . "<br>";
echo "Name : " . $row['crud_name'] . "<br>";
echo "Email : " . $row['crud_email'] . "<br>";
echo "Number : " . $row['crud_number'] . "<br>";
echo '
<a href="read.php?id=' . $row['crud_id'] . '">
<button>Read</button>
</a>
<a href="update.php?id=' . $row['crud_id'] . '">
<button>Update</button>
</a>
<a href="delete.php?id=' . $row['crud_id'] . '">
<button>Delete</button>
</a>
<br>
';
}
} else {
echo "0 results";
}
// Closing the connection
$conn->close();
?>
</body>
</html>
3. create.php
- Here is the PHP code to create or insert a record into the database. We are following SQL INSERT query and sending records to the database.
<?php
// Inculding Config file
require_once 'config.php';
// Getting Records
$crud_name = $_POST['crud_name'];
$crud_email = $_POST['crud_email'];
$crud_number = $_POST['crud_number'];
// Inserting to Database
$sql = "INSERT INTO
`crud_operation_table` (
`crud_id`,
`crud_name`,
`crud_email`,
`crud_number`
)
VALUES
(NULL, '$crud_name', '$crud_email', '$crud_number');";
// Checking Excuting Query
if ($conn->query($sql) === TRUE) {
echo "New record created successfully" . ' <a href="index.php">Go Back</a>';
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Closing the connection
$conn->close();
?>
4. read.php
- Here is the PHP code to read the records. We are getting the user id (crud_id) from the URL we set in index.php and printing it here. Don't try to use a while loop here because you're only getting a single value.
<?php
// Checking id parameter's presence
if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
// Including Config file
require_once "config.php";
// Preparing the statement
$sql = "SELECT * FROM `crud_operation_table` WHERE `crud_id` = ?;";
$stmt = $conn->prepare($sql);
// Binding variable as a parameter
$stmt->bind_param("i", $param_id);
// Setting parameter
$param_id = trim($_GET["id"]);
// Excuting statement
if ($stmt->execute()) {
$result = $stmt->get_result();
if ($result->num_rows === 1) {
$row = $result->fetch_assoc();
// Retrieving the individual value
echo "ID : " . $row['crud_id'] . "<br>";
echo "Name : " . $row['crud_name'] . "<br>";
echo "Email : " . $row['crud_email'] . "<br>";
echo "Number : " . $row['crud_number'] . "<br>";
} else {
echo "Oops! Something went wrong. There may be a problem with the number of rows.";
}
} else {
echo "Oops! Something went wrong. There may be a problem executing the statement.";
}
// Closing the statement
$stmt->close();
// Closing the connection
$conn->close();
} else {
echo "Oops! Something went wrong. URL doesn't contain id parameter.";
exit();
}
?>
5. update.php
- Here is the PHP code to update the records. First, we are getting the user id (crud_id) from the URL and printing it in the fields, and then we're sending the updated value to the database via SQL UPDATE and SET query.
<?php
// Including Config file
require_once "config.php";
// Initializing empty variables
$crud_name = $crud_email = $crud_number = "";
$crud_name_err = $crud_email_err = $crud_number_err = "";
// PROCCESSING ON SUBMIT
// Checking id parameter's presence
if (isset($_POST["id"]) && !empty($_POST["id"])) {
// Getting Values
$hidden_id = $_POST["id"];
$crud_name = trim($_POST["crud_name"]);
$crud_email = trim($_POST["crud_email"]);
$crud_number = trim($_POST["crud_number"]);
// Checking input errors before inserting in database
if (empty($crud_name_err) && empty($crud_email_err) && empty($crud_number_err)) {
// Preparing an update statement
$sql = "UPDATE `crud_operation_table` SET `crud_name` = ?, `crud_email` = ?, `crud_number` = ? WHERE `crud_operation_table`.`crud_id` = ?";
$stmt = $conn->prepare($sql);
// Binding variables as parameters
$stmt->bind_param("sssi", $param_crud_name, $param_crud_email, $param_crud_number, $param_crud_id);
// Setting parameters
$param_crud_name = $crud_name;
$param_crud_email = $crud_email;
$param_crud_number = $crud_number;
$param_crud_id = $hidden_id;
// Excuting the statement
if ($stmt->execute()) {
// Records updated successfully. Redirecting to the landing page
header("location: index.php");
exit();
} else {
echo "Oops! Something went wrong. There may be a problem executing the statement.";
}
// Closing the Statement
$stmt->close();
}
// Closing the Connection
$conn->close();
} else {
// Checking id parameter's presence
if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
// Getting id from URL
$hidden_id = trim($_GET["id"]);
// Preparing the statement
$sql = "SELECT * FROM `crud_operation_table` WHERE crud_id = ?;";
$stmt = $conn->prepare($sql);
// Binding the variable as a parameter
$stmt->bind_param("i", $param_crud_id);
// Setting the parameter
$param_crud_id = $hidden_id;
// excuting the statement
if ($stmt->execute()) {
$result = $stmt->get_result();
if ($result->num_rows === 1) {
$row = $result->fetch_assoc();
// Retrieve individual field value
$crud_name = $row["crud_name"];
$crud_email = $row["crud_email"];
$crud_number = $row["crud_number"];
} else {
echo "Oops! Something went wrong. There may be a problem with the number of rows.";
exit();
}
} else {
echo "Oops! Something went wrong. There may be a problem executing the statement.";
}
// Closing the statement
$stmt->close();
// Closing the connection
$conn->close();
} else {
echo "Oops! Something went wrong. URL doesn't contain id parameter.";
exit();
}
}
?>
<form action='<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>' method="POST">
<input type="text" name="crud_name" placeholder="Enter Name" value="<?php echo $crud_name; ?>" required>
<input type="email" name="crud_email" placeholder="Enter Email" value="<?php echo $crud_email; ?>" required>
<input type="number" name="crud_number" placeholder="Enter Number" value="<?php echo $crud_number; ?>" required>
<input type="hidden" name="id" value="<?php echo $hidden_id; ?>" />
<button type="submit">Update</button>
<hr>
</form>
6. delete.php
- Here is the PHP code to delete records. First, we are getting the user id (crud_id) from the URL, and when the user clicks on the delete button we are showing a confirmation message to the user, if the user clicks NO we're redirecting to the landing page (index.php).
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>CRUD</title>
</head>
<body>
<form action="create.php" method="POST">
<input type="text" name="crud_name" placeholder="Enter Name" required>
<input type="email" name="crud_email" placeholder="Enter Email" required>
<input type="number" name="crud_number" placeholder="Enter Number" required>
<button type="submit">Create</button>
<hr>
</form>
<?php
// Including config file
require_once 'config.php';
// Preparing query
$sql = "SELECT * FROM `crud_operation_table`;";
$result = $conn->query($sql);
// Checking if empty
if ($result->num_rows > 0) {
// Fetching by rows
while ($row = $result->fetch_assoc()) {
echo "ID : " . $row['crud_id'] . "<br>";
echo "Name : " . $row['crud_name'] . "<br>";
echo "Email : " . $row['crud_email'] . "<br>";
echo "Number : " . $row['crud_number'] . "<br>";
echo '
<a href="read.php?id=' . $row['crud_id'] . '">
<button>Read</button>
</a>
<a href="update.php?id=' . $row['crud_id'] . '">
<button>Update</button>
</a>
<a href="delete.php?id=' . $row['crud_id'] . '">
<button>Delete</button>
</a>
<br>
';
}
} else {
echo "0 results";
}
// Closing the connection
$conn->close();
?>
</body>
</html>
- All set:)
In Addition
- The cheatsheet of the SQL queries we're using to perform this CRUD Operation.
-- CHEATSHEET OF THE SQL QUERIES WE'RE USING TO CREATE CRUD
-- Creating Database
CREATE DATABASE `crud_operation_db`;
-- Creating Table
CREATE TABLE `crud_operation_db`.`crud_operation_table` (
`crud_id` INT NOT NULL AUTO_INCREMENT,
`crud_name` VARCHAR(255) NOT NULL,
`crud_email` VARCHAR(255) NOT NULL,
`crud_number` INT NOT NULL,
PRIMARY KEY (`crud_id`)
) ENGINE = InnoDB;
-- Selecting All Records
SELECT * FROM `crud_operation_table`;
-- Selecting Particular Record
SELECT * FROM `crud_operation_table` WHERE `crud_id` = 5;
-- Inserting Record
INSERT INTO
`crud_operation_table` (
`crud_id`,
`crud_name`,
`crud_email`,
`crud_number`
)
VALUES
(NULL, 'dsadad', 'adadasdasd@fds.sgd', '1545');
-- Updating Particular Record
UPDATE `crud_operation_table` SET
`crud_name` = 'Change Hales',
`crud_email` = 'change@hales.com',
`crud_number` = '000000000'
WHERE
`crud_operation_table`.`crud_id` = 19;
-- Deleting Record
DELETE FROM `crud_operation_table` WHERE `crud_operation_table`.`crud_id` = 2;
0 Comments