If you are the one who is just starting off with the PHP and looking for how you can insert, update and delete in MySQL database using PHP then here’s a simple code with line by line explanation.
I know there are lot of websites where you can learn this exact same thing but they just posted the code. They didn’t took the time to explain the code. Here I am fixing that and all the newbies and beginners in PHP will find this guide extremely useful.
So now without wasting a single minute. Lets proceed with the steps.
6 Easy Steps to Insert, Update and Delete Data in MySQL Database using PHP
- Create database in MySQL and Table with the required fields.
- Connect the database to your sample PHP code.
- Create a index.php where all data inside the table will be displayed with options to insert, update and delete the records.
- Create a PHP file to insert data with HTML form to take data from user.
- Create a PHP file to update the data with HTML form and pre-display the present data in database.
- Create a PHP file to delete the data.
Let’s begin with the creation of database.
This can be done in 2 different ways. Either you can go to mysql console and fire up the commands or you can use the GUI (not available in all servers) where you have to just fill up the simple form and you will get database and required table created in easy way.
Here’s the video to do so…
Follow it to create the database and table to insert, update and delete the data in MySQL using PHP.
Once you create the database and table. You can begin with coding your PHP script to manipulate the data of your table in database.
> index.php <<
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″ />
<title>Untitled Document</title>
</head>
<body>
<?php
include_once(“connection.php”);
$obj= new connection();
$obj->connect();
$obj->sql=”SELECT * FROM reg_tab”;
$obj->select_query($obj->sql);
?>
<table align=”center” border=”1″>
<form method=”post”>
<tr><th></th><th>ID</th><th>Name</th><th>Gender</th><th>Email</th><th>Contact Number</th></tr>
<?php
while($row=mysqli_fetch_array($obj->res))
{ ?>
<tr>
<td> <input type=”checkbox” name=”chkid[]” value=”<?php echo $row[0]; ?>” /> </td>
<td> <?php echo $row[0]; ?> </td>
<td> <?php echo $row[1]; ?> </td>
<td> <?php echo $row[2]; ?> </td>
<td> <?php echo $row[3]; ?> </td>
<td> <?php echo $row[5]; ?> </td>
<td> <a href=”editdata.php?id=<?php echo $row[0]; ?>”> Edit</a></td>
</tr>
<?php }
?>
<tr><td colspan=”7″ align=”center”><input type=”submit” name=”delete” value=”Delete” /></td></tr>
<?php
if(isset($_REQUEST[‘delete’]))
{
$obj->connect();
foreach($_REQUEST[‘chkid’] as $item)
{
$obj->sql=”DELETE FROM `reg_tab` WHERE red_id='”.$item.”‘”;
$obj->insert_update_delete($obj->sql);
echo”<script>alert(‘Data deleted’); document.location=’viewdata.php'</script>”;
}
}
?>
<form>
</table>
</body>
</html>
In this file we have just executed one SQL query and that is to select the data from the table and display in in our page.
Here’s the query –
SELECT * FROM reg_tab;
We have also putted our code to delete the data inside this file only. So when you want to delete any data you can just click the link on side of the data to execute the delete query on your database.
The query to delete the data is as follows –
DELETE * FROM reg_tab WHERE id='”.$id.”‘;
Note – Here $id is a variable which will tell the database which data to delete. Hence, it will be passed in WHERE clause of the query.
Save index.php in any directory inside the server.
Let’s proceed with the next file now.
> insert.php <<
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″ />
<title>Register</title>
</head>
<body>
<form method=”post” align=”center” enctype=”multipart/form-data”>
<table width=”200″ border=”1″ align=”center”>
<tr>
<td>Name</td>
<td><input type=”text” name=”txtname” placeholder=”Enter your name” required /></td>
</tr>
<tr>
<td>Gender</td>
<td><input type=”radio” name=”rdogender” value=”Male” /> Male
<input type=”radio” name=”rdogender” value=”Female” /> Female</td>
</tr>
<tr>
<td>State</td>
<td></td>
</tr>
<tr>
<td>Email</td>
<td><input type=”email” name=”txtEmail” placeholder=”Enter Your Email” required/></td>
</tr>
<tr>
<td>Contact No</td>
<td><input type=”text” name=”numcon” placeholder=”Enter your Contact Number” required/></td>
</tr>
<tr>
<td>User name</td>
<td><input type=”text” name=”txtuname” placeholder=”Enter your Username” required/></td>
</tr>
<tr>
<td>Password</td>
<td><input type=”password” name=”txtpwd” placeholder=”Enter Password” required/></td>
</tr>
<tr>
<td colspan=”2″><input type=”submit” value=”Save” name=”save”/></td>
</tr>
</table>
</form>
<?php
if(isset($_REQUEST[‘save’]))
{
include_once(“connection.php”);
$obj= new connection();
$obj->connect();
$obj->sql=”INSERT INTO `reg_tab` (`name`, `gender`, `email`, `contact`) VALUES (‘”.$_REQUEST[‘txtname’].”‘,'”.$_REQUEST[‘rdogender’].”‘,'”.$_REQUEST[‘txtEmail’].”‘,'”.$_REQUEST[‘numcon’].”‘)”;
$obj->insert_update_delete($obj->sql);
$obj->sql=”SELECT max(`reg_id`) FROM reg_tab”;
$obj->select_query($obj->sql);
$row=mysqli_fetch_array($obj->res);
//echo $row[0];
$obj->sql=”INSERT INTO `login`(`reg_id`, `uname`, `password`, `type`) VALUES (‘”.$row[0].”‘,'”.$_REQUEST[‘txtuname’].”‘,'”.$_REQUEST[‘txtpwd’].”‘,’user’)”;
$obj->insert_update_delete($obj->sql);
//echo “INSERT INTO `reg_tab`(`name`, `gender`, `email`, `contact`, `profile_pic`, `file`, `uname`, `password`) VALUES (‘”.$_REQUEST[‘txtname’].”‘,'”.$_REQUEST[‘rdogender’].”‘,'”.$_REQUEST[‘txtEmail’].”‘,'”.$_REQUEST[‘numcon’].”‘,'”.$_FILES[‘pimg’][‘name’].”‘,'”.$_FILES[‘rfile’][‘name’].”‘,'”.$_REQUEST[‘txtuname’].”‘,'”.$_REQUEST[‘txtpwd’].”‘)”;
//echo”<script>alert(‘Data inserted’); document.location=’viewdata.php'</script>”;
}
?>
</body>
</html>
In this file we have created a simple form to take the data from user to insert into the table inside the database. We will be taking that data through the POST request and storing into the temporary variables.
Note – you can do it directly as well but this would be easier for beginners to understand.
Once we store the form data into the temporary variables, we will then execute a query to insert the data into table in database.
This is the most important query even one single comma (,) mistake will give error so make sure you follow the right syntax.
Here’s the insert data query that we are executing –
INSERT INTO `reg_tab`(`name`, `gender`, `email`, `contact`, `profile_pic`, `file`, `uname`, `password`) VALUES (‘”.$_REQUEST[‘txtname’].”‘,'”.$_REQUEST[‘rdogender’].”‘,'”.$_REQUEST[‘txtEmail’].”‘,'”.$_REQUEST[‘numcon’].”‘,'”.$_FILES[‘pimg’][‘name’].”‘,'”.$_FILES[‘rfile’][‘name’].”‘,'”.$_REQUEST[‘txtuname’].”‘,'”.$_REQUEST[‘txtpwd’].”‘);
The $_REQUEST ones are the variables inside the query which stores the values from the form to insert into the table in database.
So that was about the inserting data into database using PHP. Insert is the most important query and next important is the UPDATE.
So, let’s now begin with how to update the details in table in MySQL database using PHP.
Here’s the code of it.
> updatedata.php <<
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=iso-8859-1″ />
<title>Untitled Document</title>
</head>
<body>
<?php
echo $_REQUEST[‘id’];
include_once(“connection.php”);
$obj= new connection();
$obj->connect();
$obj->sql=”SELECT * FROM reg_tab WHERE red_id='”.$_REQUEST[‘id’].”‘”;
$obj->select_query($obj->sql);
$row=mysqli_fetch_array($obj->res);
?>
<form method=”post” align=”center”>
<table width=”200″ border=”1″ align=”center”>
<tr>
<td>Name</td>
<td><input type=”text” name=”txtname” value=”<?php echo $row[1]; ?>” required /></td>
</tr>
<tr>
<td>Gender</td>
<?php
if ($row[2]==”Male”)
{
?>
<td><input type=”radio” name=”rdogender” value=”Male” checked=”checked”/> Male
<input type=”radio” name=”rdogender” value=”Female” /> Female</td>
<?php
}
else
{
?>
<td><input type=”radio” name=”rdogender” value=”Male”/> Male
<input type=”radio” name=”rdogender” value=”Female” checked=”checked” /> Female</td>
<?php
}
?>
</tr>
<tr>
<td>Email</td>
<td><input type=”email” name=”txtEmail” value=”<?php echo $row[3]; ?>” required/></td>
</tr>
<td>Contact No</td>
<td><input type=”text” name=”txtcon” value=”<?php echo $row[5]; ?>” required/></td>
</tr>
<tr>
<td colspan=”2″><input type=”submit” value=”update” name=”update”/></td>
</tr>
</table>
</form>
<?php
if(isset($_REQUEST[‘update’]))
{
$obj->connect();
$obj->sql=”UPDATE `reg_tab` SET `name`='”.$_REQUEST[‘txtname’].”‘,`gender`='”.$_REQUEST[‘rdogender’].”‘,`email`='”.$_REQUEST[‘txtEmail’].”‘,`contact`='”.$_REQUEST[‘txtcon’].”‘ WHERE red_id='”.$_REQUEST[‘id’].”‘”;
$obj->insert_update_delete($obj->sql);
echo”<script>alert(‘Data updated’); document.location=’viewdata.php'</script>”;
}
?>
</body>
</html>
For updating the data also we will need the table so first of all create the sample form same as we created on the insert data page. The difference will be, we have to create a PHP script to place the present data from table in placeholder. So that you will get all the data pre-filled when you visit the page. Then you can edit and submit the form.
On clicking submit button, all the form data will be taken by the $_REQUEST variable and passed into the UPDATE query which is as follows…
UPDATE `reg_tab` SET `name`='”.$_REQUEST[‘txtname’].”‘,`gender`='”.$_REQUEST[‘rdogender’].”‘,`email`='”.$_REQUEST[‘txtEmail’].”‘,`contact`='”.$_REQUEST[‘txtcon’].”‘ WHERE red_id='”.$_REQUEST[‘id’].”‘;
After doing this much, you will have 3 pages in total one for viewing the data and deleting the data. Another one for inserting the data and next one for deleting the data from table.
So in this way you can easily insert, update and delete data in MySQL database using PHP. I hope this tutorial helps you in learning one of the most important thing of Basics in PHP. Comments section is always open for all the doubts that you may have.
Share this post with other people who wants to learn how to manipulate the data with database using PHP.
Thank you & Have a Nice Day. Keep visiting for more such detailed programming guides.
