PHP CRUD Application

     

Implementing  CRUD Application in PHP

What is CRUD?

CRUD is an acronym for Create, Read, Update and Delete. CRUD operations are basic data manipulation for database. 

In this tutorial we are going to implement queries for the following operations,

such as Create (i.e. insert), Read (i.e. select), Update and Delete operations.

In this tutorial we'll create a simple PHP application to perform all these operations on a MySQL database table in one place.

Well, let's start by creating the table which we'll use in all of our example.


CREATE TABLE users(
 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
 phone VARCHAR(10) NOT NULL
);

 To connect to the MySQL database server. Let's create a file named "config.php" and put the following code inside it.

config.php

<?php /* Database Details */ define('DB_SERVER', 'localhost'); define('DB_USERNAME', 'root'); define('DB_PASSWORD', ''); define('DB_NAME', 'usersdb'); /* To connect to MySQL database */ $conn = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME); // Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } ?>
To insert records in the users table.
create.php

<?php
// Include config file
require_once "config.php";
// Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){
$username = $_POST["username"];
$password= $_POST["password"])
$email= $_POST["email"];
    $phone= $_POST["phone"];

// Prepare an insert query statement $sql = "INSERT INTO users(username, password, email, phone) VALUES ('$username', '$password', '$email','$phone')";
$result= mysqli_query($conn, $sql);
echo("Record Added to the Database";
} // Close connection mysqli_close($conn); ?>

<!DOCTYPE html>
<html lang="en"> <body>
<h2>Create Record</h2>
<p>Please fill this form and submit to add User record to the database.</p>
<form action="#" method="post">
<label>Username</label>
<input type="text" name="username" >
<label>Password</label>
<input type="text" name="password" >
<label>Email</label>
<input type="text" name="email" >
<label>Phone</label>
<input type="text" name="phone" >
<input type="submit" name="submit: value="Submit">
<a href="index.php">Home</a>
</form>
</body>
</html>

Let's create a file named "read.php" and copy the following code into it. It will reads the records from the users table based the id attribute of the user.

read.php
<?php // Check existence of id if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){ // Include config file require_once "config.php";
    $userId = $_GET["id"]; // Prepare a select statement $sql = "SELECT * FROM users WHERE id = ".$userId;
if($stmt = mysqli_prepare($conn, $sql)){ // Bind variables to the prepared statement as parameters $param_id = trim($_GET["id"]); $result= mysqli_query($conn, $sql); if(mysqli_num_rows($result) == 1){ /* Fetch result row */ $row = mysqli_fetch_array($result, MYSQLI_ASSOC); // Retrieve individual field value

$name = $row["username"];
 $address = $row["password"];
 $salary = $row["email"];
    $phone = $row["phone"];

} else{ // URL doesn't contain valid id parameter, then isplays error message echo "Oops! Record Not Found."; } } else{ echo "Oops! Something went wrong. Please try again later."; } // Close connection mysqli_close($link); } ?>
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>View Record</title> </head> <body>

<h1>View Record</h1>
<label>Username</label>
<p><?php echo $row["username"]; ?></p>
<label>Password</label>
<p><?php echo $row["password"]; ?></p>
<label>Email</label>
<p><?php echo $row["email"]; ?></p>
<label>Phone</label
<p><?php echo $row["phone"]; ?></p>
<p><a href="index.php"">Back</a></p>

</body> </html>
It will update the existing records in the users table based the id attribute of the user.
update.php   
<?php // Include config file require_once "config.php"; // Define variables and initialize with empty values $username= $password= $email= $phone=""; $username_err = $password_err = $email_err = $phone_err= ""; // Processing form data when form is submitted if(isset($_POST["id"]) && !empty($_POST["id"])){ // Get hidden input value $id = $_POST["id"]; // Validate name $input_name = trim($_POST["username"]); if(empty($input_name)){ $name_err = "Please enter a username."; } elseif(!filter_var($input_name, FILTER_VALIDATE_REGEXP, array("options"=>array("regexp"=>"/^[a-zA-Z\s]+$/")))){ $username_err = "Please enter a valid username."; } else{ $username = $input_name; } // Validate password $input_password = trim($_POST["password"]); if(empty($input_password )){ $address_err = "Please enter a password."; } else{ $password= $input_password; } // Validate email $input_email = trim($_POST["email"]); if(empty($input_email )){ $email_err = "Please enter a email."; } else{ $email= $input_email; }
// Validate phone $input_phone = trim($_POST["phone"]); if(empty($input_phone )){ $address_err = "Please enter a phone number."; } else{ $password= $input_phone; }
 // Check input errors before inserting in database if(empty($name_err) && empty($password_err) && empty($email_err) && empty($phone_err)){ // Prepare an update statement $sql = "UPDATE users SET username=?, password=?, email=?, phone=? WHERE id=?"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "ssssi", $param_username, $param_password, $param_email, $param_phone, $param_id); // Set parameters $param_username= $username; $param_password= $password; $param_email= $email;
            $param_phonephone; $param_id = $id; // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ // Records updated successfully. Redirect to landing page header("location: index.php"); exit(); } else{ echo "Something went wrong. Please try again later."; } } // Close statement mysqli_stmt_close($stmt); } // Close connection mysqli_close($link); } else{ // Check existence of id parameter before processing further if(isset($_GET["id"]) && !empty(trim($_GET["id"]))){ // Get URL parameter $id = trim($_GET["id"]); // Prepare a select statement $sql = "SELECT * FROM users WHERE id = ?"; if($stmt = mysqli_prepare($link, $sql)){ // Bind variables to the prepared statement as parameters mysqli_stmt_bind_param($stmt, "i", $param_id); // Set parameters $param_id = $id; // Attempt to execute the prepared statement if(mysqli_stmt_execute($stmt)){ $result = mysqli_stmt_get_result($stmt); if(mysqli_num_rows($result) == 1){ /* Fetch result row as an associative array. Since the result set contains only one row, we don't need to use while loop */ $row = mysqli_fetch_array($result, MYSQLI_ASSOC); // Retrieve individual field value $username = $row["username"]; $password = $row["password"]; $email = $row["email"];                                $phone = $row["phone"]; } else{ // URL doesn't contain valid id. Redirect to error page header("location: error.php"); exit(); } } else{ echo "Oops! Something went wrong. Please try again later."; } } // Close statement mysqli_stmt_close($stmt); // Close connection mysqli_close($link); } else{ // URL doesn't contain id parameter. Redirect to error page header("location: error.php"); exit(); } } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Update Record</title> </head> <body> <h2>Update Record</h2> <p>Please edit the input values and submit to update the record.</p> <form action="<?php echo htmlspecialchars(basename($_SERVER['REQUEST_URI'])); ?>" method="post"> <label>Username</label> <input type="text" name="username" value="<?php echo $username; ?>">
<label>Password</label> <input type="text" name="password" value="<?php echo $password ?>"> <label>Email</label> <input type="text" name="email" value="<?php echo $email; ?>">
<label>Phone</label> <input type="text" name="phone" value="<?php echo $phone; ?>">
<input type="hidden" name="id" value="<?php echo $id; ?>"/> <input type="submit" class="btn btn-primary" value="Submit"> <a href="index.php" class="btn btn-default">Cancel</a> </form> </body> </html>
delete.php

<?php


// Process delete operation after confirmation
if(isset($_POST["id"]) && !empty($_POST["id"])){
 // Include config file
 require_once "config.php";
    $userId = $_GET["id"]; // Prepare a delete statement
 $sql = "DELETE FROM users WHERE id = = ".$userId;
if($stmt = mysqli_prepare($conn, $sql)){
if(mysqli_stmt_execute($stmt)){
 // Records deleted successfully. Redirect to main page
 echo "Oops! Record Deleted.";
 } else{
 echo "Oops! Something went wrong. Please try again later.";
 }

// Close connection
 mysqli_close($conn);
} else{
 // Check existence of id parameter
 if(empty(trim($_GET["id"]))){
 // URL doesn't contain id parameter.
echo "does not contains id parameter";
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
 <meta charset="UTF-8">
 <title>Delete Record</title>
</head>
<body>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
<div class="alert alert-danger fade in">
<input type="hidden" name="id" value="<?php echo trim($_GET["id"]); ?>"/>
<p>Are you sure you want to delete this record?</p><br>
<p>
<input type="submit" value="Yes">
<a href="index.php">No</a>
</p></form>
</body>
</html>


Comments

Post a Comment

Popular posts from this blog

Using javascript pass form variables to iframe src

Image
   Open PDF in Bootstrap Modal Window   This tutorial shows How to pass a JavaScript/jQuery variable to bootstrap modal view and sets it to iframe  src attribute. It passes value dynamically to src attribute of the iframe. When the user clicks a link, it opens the URL in a popup window. This  code shows the PDF in a Model without download option by hiding the toolbar.  To show PDF toolbar in modal, just add #toolbar=1   in the above script. index.php <!DOCTYPE html> <html lang= "en" > <head> <title>Bootstrap Model with PDF Example - coding-zon</title> <meta charset= "utf-8" > <meta name= "viewport" content= "width=device-width, initial-scale=1" > <link rel= "stylesheet" href= "https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css" > <script src= "https://cdn.jsdelivr.net/npm/jquery@3.6.1/dist/jquery.slim.
Read more

Creating a new PDF by Merging PDF documents using TCPDF

Image
Merging two PDFs in PHP What is PDF? The Portable Document Format. PDF stands for "Portable Document Format". Essentially, the format is used when you need to save files that cannot be modified but still need to be easily shared and printed. PDF is one of the most commonly used file types around. PDFs generally contain shareable data, such as text or photo documents etc., and are accessible to anyone who has a computer or smartphone. Unlike a Word file, which requires Microsoft Word to view or edit, most browsers will open a PDF. How to merge PDFs? You can merge several documents into one. In PHP, you have a library called TCPDF which allows you to combine one or more PDF documents to one single document. In this article, we will create a new PDF by Merging PDF documents using TCPDF. Download the PDFMerger-tcpdf.zip here. Unzip it and copy it to the PHP server. Copy the PDF files into the same folder that you want to combine. Open the file: merge-pdf-example.php Provide
Read more

Import excel file into mysql in PHP

Image
  Upload Excel file and Import  it into MySQL in PHP  Create a table in some database with given code.   Download Library - extract it, rename the folder to library . Create index.php file with given code in a server. Create excelUpload.php with given code. Create db_config.php . Create one Excel file with some sample data as shown in the below image of Excel file. Create ' uploads ' folder in the same directory. Now start server, run the index.php. Upload a .xls file. If Everything is fine, Excel data inserted in the above created MySQL table - tbl_products . PHP Version:   5.5.11 Folder Structure      Query: CREATE TABLE IF NOT EXISTS `tbl_products` ( `id` int ( 11 ) NOT NULL AUTO_INCREMENT, `productname` varchar ( 100 ) DEFAULT NULL , `description` varchar ( 100 ) DEFAULT NULL , PRIMARY KEY (`id`) )     Top index.php <!DOCTYPE html> <html> <head> <title> coding-zon Demo - Upload Excel to MySQL in PHP </title> <lin
Read more