Insert, Update, Delete in PHP MySQL example [GIT downloads]

Get Insert, Update, Delete in PHP MySQL example with GIT downloads source code link. As a PHP web developer, you will learn here a simple PHP CRUD (Create, Read, Update, and Delete)  database operations for a HTML contact us form with input text fields – Name, Email, Phone and Message. Attention no need to copy the source code, a complete example code available for download from GIT HUB – Link in bottom of the article.

I am going to extend my earlier article how to connect and insert records of a HTML Contact Form to MySQL using PHP. First of all, you must read earlier articles for creating HTML contact form, database creation, connecting php to MySQL, set your localhost in details.  You can find here => How to connect HTML to database with MySQL using PHP.

Without sharing too much theory, Let’s start with concept and coding.

What is the role of PHP, MySQL(database) and HTML in saving data in database?

The end user is not much technical to manage the database directly for their record purpose. Therefore, they need a simple interface to insert the data in database. In my contact form example, the HTML form(watch image below) is an interface i.e., open in any web browser to fill the form.

This is an easy process for any end user. After filling the form, the form data is processed using PHP coding and inserted in MySQL database(“db_contact”) table name (“tbl_config”).  In short, First we prepare the user interface for website visitor or administrator, then create PHP scripts to perform database operation,  after that show the response to the visitor or end user of your web applications

What is the concept behind database and CRUD operations?

For communication with database, you need the CRUD operation. CRUD stands for Create, Read, Update & Delete. These 4 simple database operations perform using SQL (Structured Query Language) via insert, select, update and delete query commands.  A database has multiple tables and each table has multiple row and tuples(columns) in which data records.

INSERT(Create): Inserting record in table, you need this SQL command. You can insert single or multiple records at a time depending on your requirements.

SELECT(Read): You need this SQL statements for selecting table row and column for viewing purpose.

UPDATE: For updating single or multiple record of a table, you need this SQL command.

DELETE: If you no need any record, this SQL statement help you to delete the one or multiple records at a time from database.

How to list inserted records of a database table using HTML, PHP?

Hope you get my last post to insertion of contact us HTML form in database using PHP. Now, here explain you selection of records and display in simple HTML format using bootstrap responsive framework.

For showing some records you have to inserted some rows in your tables. Please insert these rows in your already deploy database. Or you can feed from your form and inserted in database.

INSERT INTO `tbl_contact` (`id`, `fldName`, `fldEmail`, `fldPhone`, `fldMessage`) VALUES

(1, 'john', 'john@gmail.con', '9958576074', 'dfdf' ),

(2, 'luwis', 'luwis@gmail.con', '9958579075', 'dfdfvv'),

(3, 'martin', 'martins@gmail.con', '9958579075', 'dfdfvvx'),

(4, 'andrew', 'andrew@gmail.con', '9958579222', 'd'),

(5, 'tonny', 'tonny@gmail.con', '9958579223', 'dffd'),

(6, 'ammy', 'ammy@gmail.con', '9958579242', 'dffdx'),

(7, 'ammy jacson', 'ammy_jacson@gmail.con', '9958589240', 'dffdxxx'),

(8, 'randy', 'randy@gmail.con', '99585792999', 'dffdxxxxz'),

(9, 'johnson', 'johnson@gmail.con', '99585722222', 'gbffd'),

(10, 'johnsonandjohnson', 'johnsonandjohnson@gmail.con', '99585722233', 'gbffdscca'),

(11, 'mustafa', 'mustafa@gmail.con', '99585799999', 'gbffdsccazzz');

Here is the screen that create as a web page to show all records that is in database table  tbl_contact.

The example pages create using the responsive framework – Bootstrap, if you install in your working folder the layout design will become automatically at your end also. You will be getting on top of user interface pages the CDN link of Bootstrap.

Now in the project have multiple files therefore create a common database connection and include this file to each other file on top.

/* Include your connection file */

require_once('connect.php');

It is not a good programming practice to keep database connection on multiple pages. Now create a file “connect.php” with the following code.

$con = mysqli_connect('localhost', 'root', '','db_contact');

Below is the complete PHP code with MySQL connection for selecting and showing database tables records:

How to modify (UPDATE operations) inserted table row using MySQL update query using PHP?

For updating your single records, you have to click on edit button and the form need to load for edit the records.  First of all, create a new page edit.php and then add edit link for HREF attribute in the “select.php” page with query sting of inserted record primary database table id. Below is the complete link

<a href=”update.php?id=<?php echo $id;?>”>Update</a>

Now when if someone click on Update link,  then went on “update.php” web page.  On this page first have to check if the variables in URL (query string) “id” then proceed ahead.

Now write the code for selecting corresponding records of the “id” value. The SQL as below:

$sql = "SELECT * FROM `tbl_contact` WHERE Id=".$_GET['id'];

Again, use mysqli_query function with database connection variable $con to run the query. You will get records set. After that you can fetch records in variables: The code as below:

/* Include your connection file */
require_once('connect.php');

/* Write a SQL that pick all records of your my sql table */
$sql = "SELECT * FROM `tbl_contact` WHERE 1=1";



/* Run sql with yout database connection variable */
$result = mysqli_query($con, $sql);



/* get total numbers of records */
$totalitems = mysqli_num_rows($result);


/* Check if records more then 0 */
if($totalitems > 0)
{
/* Crate a heading row */
$rowheading='<hr/><div class="row " style="font-weight:bold">
<div class="col-md-1">
Sl. No.
</div>
<div class="col-md-2">
Name
</div>
<div class="col-md-3">
Email
</div>
<div class="col-md-2">
Phone
</div>
<div class="col-md-2">
Message
</div>
<div class="col-md-2">
Actiion
</div>

</div>';

$rowstring=''; // for storing html plus database records
/* Run the while loop and make a string of your records */
while($row = mysqli_fetch_assoc($result)){
/* Each row come with each single line of database */
/* Assign each database field records in a variable */
$id=$row['Id'];
$fldName=$row['fldName'];
$fldEmail=$row['fldEmail'];
$fldPhone=$row['fldPhone'];
$fldMessage=$row['fldMessage'];
/* Now take row of html with 6 column */
$rowstring.='<hr/><div class="row">
<div class="col-md-1">'.$id.'</div>
<div class="col-md-2">'.$fldName.'</div>
<div class="col-md-3">'.$fldEmail.'</div>
<div class="col-md-2">'.$fldPhone.'</div>
<div class="col-md-2">'.$fldMessage.'</div>
<div class="col-md-2"><a href="update.php?id='.$id.'">Update</a> | <a href="delete.php?id='.$id.'">Delete</a></div>
</div>';
}
echo $rowheading.$rowstring; // concat row heading and data string and print.
}
else
{
echo "There is no records in our database?";

}

So, the contact form looks like this with their corresponding value.

The contact form action (record submission file name is mention i.e. update-submit.php) . Here is form complete code

<form name="frmContact" class="needs-validation " method="post" action="update-submit.php">

Here is the complete code for “update.php”

<?php
/* Include your connection file */
require_once('connect.php');

/* Write a SQL that pick all records of your my sql table */
$sql = "SELECT * FROM `tbl_contact` WHERE Id=".$_GET['id'];

 

/* Run sql with yout database connection variable */
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_assoc($result);

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Contact Form - PHP/MySQL Demo Code</title>
<!-- Latest compiled and minified CSS -->

</head>

<body class="bg-light">

<div class="container">
<div class="py-5 text-center">
<img class="d-block mx-auto mb-4" src="https://www.raghwendra.com/blog/wp-content/uploads/2018/09/logo-rwsn.png" alt="" width="240" height="64">
<h2>Contact us form</h2>
<p class="lead">Below is an example form How to connect HTML to database with MySQL using PHP? An example</p>
</div>
<fieldset>

<form name="frmContact" class="needs-validation " method="post" action="update-submit.php">
<p>
<label for="Name">Your Name </label>
<input type="text" class="form-control" name="txtName" id="txtName" placeholder="Name" value="<?php echo $row['fldName'];?>" required>
<div class="invalid-feedback">
Valid first name is required.
</div>
</p>
<p>
<label for="email">Your Email</label>
<input type="text" class="form-control" name="txtEmail" id="txtEmail" placeholder="Email" value="<?php echo $row['fldEmail'];?>" required>
</p>
<p>
<label for="phone">Your Phone</label>
<input type="text" class="form-control" name="txtPhone" id="txtPhone" placeholder="Phone" value="<?php echo $row['fldPhone'];?>" required>
</p>
<p>
<label for="message">Message</label>
<textarea name="txtMessage" class="form-control" id="txtMessage" placeholder="Message" required><?php echo $row['fldMessage'];?></textarea>
</p>
<p>&nbsp; <input type="hidden" class="form-control" name="selectedid" id="selectedid" placeholder="Phone" value="<?php echo $_GET['id'];?>" required></p>

<p>
<input type="submit" name="Submit" id="Submit" value="Click me to Contact" class="btn btn-primary btn-lg btn-block">
</p>
</form>
</fieldset>
</div>

</body>
</html>

After Submission you have to put this code :

/* Include your connection file  */
require_once('connect.php');
/* Check if is set the post variable txtName  */
if(isset($_POST['txtName']))
{
/* Get the all form field post variables. */
$id = $_POST['selectedid'];
$txtName = $_POST['txtName'];
$txtEmail = $_POST['txtEmail'];
$txtPhone = $_POST['txtPhone'];
$txtMessage = $_POST['txtMessage'];

/* Update your posted data in your database - insert SQL code. */

$sql = "UPDATE `tbl_contact` SET `fldName` = '$txtName', `fldEmail` = '$txtEmail', `fldPhone` = '$txtPhone', `fldMessage` = '$txtMessage' WHERE `tbl_contact`.`Id` = ".$id;

/* Run insert query in database. */
$rs = mysqli_query($con, $sql);

/* Check records is updated or not. */
if($rs)
{
echo "Contact Records updated";
}
}
else
{
echo "Are you a genuine visitor?";

}

What is the way of single and multiple deletion of inserted records using JavaScript, PHP?

In the web application development project, deleting records is a most important features.  For deletion of single records, you have to be delete link on list page and after clicking of that , you have to load a “delete.php” page. See the code below:

<a href=”delete.php?id=<?php echo $id;?>”>Delete</a>

Now when you click on Delete link, you will come on “delete.php”.  You just need to write a DELETE sql with run the query over database. Here is the final code

/* Include your connection file */
require_once('connect.php');
/* Check if is set the post variable txtName */
if(isset($_GET['id']))
{
/* Delete your posted data in your database - insert SQL code. */

$sql = "DELETE FROM `tbl_contact` WHERE `tbl_contact`.`Id` = ".$_GET['id'];

/* Run insert query in database. */
$rs = mysqli_query($con, $sql);

/* Check records is updated or not. */
if($rs)
{
echo "Contact Records Deleted";
}

}
else
{
echo "Are you a genuine visitor?";

}

Now the coding part is done. Download code from Github

See more answer about PHP script connect to Mysql on Facebook Group

Please go to Facebook group for more discussion click here