PHP Update Data of MySQL - CodingCage.com

In this Post we will see that how to Update Data of MySql Using PHP , Data of MySql table Can be Updated by executing SQL UPDATE Query(Statement) through PHP Function mysql_query ,So let’s take a look at This tutorial.

Download Script

After data insert , select and delete , data updation is also necessary for any web application.
Update syntax


update  table_name 
set     column1= value1, column2=value2,coulmn_n=value_n 
where   column_name=Value ;

and PHP Query would be as follow :


$query = "UPDATE table_name 
          SET column1='value1',column2='value2'
          WHERE column_name=any_value";

database : dbtuts
table : users
here i use following details for this tutorial


CREATE DATABASE `dbtuts` ;
CREATE TABLE `dbtuts`.`users` (
`user_id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 25 ) NOT NULL ,
`last_name` VARCHAR( 25 ) NOT NULL ,
`user_city` VARCHAR( 45 ) NOT NULL
) ENGINE = InnoDB;

i hope you should know that how to select data from database , so i come to the point.
first of all select the data from database table and display it on page.
Suppose following is the data :

data.php


<?php

mysql_connect("localhost","root","");
mysql_select_db("dbtuts");
$sql_query="SELECT * FROM users";
$result_set=mysql_query($sql_query);
?>

<table align="center">
    <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>City</th>
    <th>Edit</th>
    </tr>
    <?php
 if(mysql_num_rows($result_set)>0)
 {
  while($row=mysql_fetch_row($result_set))
  {
   ?>
            <tr>
            <td><?php echo $row[1]; ?></td>
            <td><?php echo $row[2]; ?></td>
            <td><?php echo $row[3]; ?></td>
            <td align="center"><a href="edit_data.php?edit_id=<?php echo $row[0]; ?>"><img src="b_edit.png" alt="Edit" /></a></td>
            </tr>
            <?php
  }
 }
 else
 {
  ?>
        <tr>
        <th colspan="3">There's No data found !!!</th>
        </tr>
        <?php
 }
 ?>
 </table>

OutPut:

By selecting the data to be edited the another php file will be displayed with the selected data as follow :

Now we need to fetch the selected data From the QueryString URL.


http://localhost/edit-data/edit_data.php?edit_id=36

$_GET[‘edit_id’] : is the php variable that get the QueryString ID From The URL.


if(isset($_GET['edit_id']))
{
 $sql_query="SELECT * FROM users WHERE user_id=".$_GET['edit_id'];
 $result_set=mysql_query($sql_query);
 $row=mysql_fetch_array($result_set);
}

By fetching all the selected data you can Update them , But for Viewing the data in the textbox do as follow.

<input type="text" name="first_name" value="<?php echo $row['first_name']; ?>" />

Do The same for all updatable fields but with the different name variable.

Now The Main Update Condition :


if(isset($_POST['btn-update']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data
 
 // sql query for update data into database

 $sql_query = "UPDATE users 
                      SET first_name='$first_name',last_name='$last_name',user_city='$city_name' 
                      WHERE user_id=".$_GET['edit_id'];

 // sql query for update data into database
 
}

Conclusion: Here i Shown you that How can you Update the Data of Mysql table Using PHP Step by Step easily hope you Got It. That’s it.

Complete Script

index.php


<?php
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
$sql_query="SELECT * FROM users";
$result_set=mysql_query($sql_query);
?>
<!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=utf-8" />
<title>PHP Update Data From MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>PHP Update Data From MySql - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <table align="center" width="100%">
    <tr>
    <th>First Name</th>
    <th>Last Name</th>
    <th>City</th>
    <th>Edit</th>
    </tr>
    <?php
 if(mysql_num_rows($result_set)>0)
 {
  while($row=mysql_fetch_row($result_set))
  {
   ?>
            <tr>
            <td><?php echo $row[1]; ?></td>
            <td><?php echo $row[2]; ?></td>
            <td><?php echo $row[3]; ?></td>
            <td align="center"><a href="edit_data.php?edit_id=<?php echo $row[0]; ?>"><img src="b_edit.png" alt="Edit" /></a></td>
            </tr>
            <?php
  }
 }
 else
 {
  ?>
        <tr>
        <th colspan="3">There's No data found !!!</th>
        </tr>
        <?php
 }
 ?>
    </table>
    </div>
</div>

<div id="footer">
 <div id="content">
    <hr /><br/>
    <label>for more tutorials and blog tips visit <a href="http://cleartuts.blogspot.com"> : cleartuts.com</a></label>
    </div>
</div>
</center>
</body>
</html>

edit_data.php


<?php
mysql_connect("localhost","root");
mysql_select_db("dbtuts");
if(isset($_GET['edit_id']))
{
 $sql_query="SELECT * FROM users WHERE user_id=".$_GET['edit_id'];
 $result_set=mysql_query($sql_query);
 $fetched_row=mysql_fetch_array($result_set);
}
if(isset($_POST['btn-update']))
{
 // variables for input data
 $first_name = $_POST['first_name'];
 $last_name = $_POST['last_name'];
 $city_name = $_POST['city_name'];
 // variables for input data
 
 // sql query for update data into database
 $sql_query = "UPDATE users SET first_name='$first_name',last_name='$last_name',user_city='$city_name' WHERE user_id=".$_GET['edit_id'];
 // sql query for update data into database
 
 // sql query execution function
 if(mysql_query($sql_query))
 {
  ?>
  <script type="text/javascript">
  alert('Data Are Updated Successfully');
  window.location.href='index.php';
  </script>
  <?php
 }
 else
 {
  ?>
  <script type="text/javascript">
  alert('error occured while updating data');
  </script>
  <?php
 }
 // sql query execution function
}
if(isset($_POST['btn-cancel']))
{
 header("Location: 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=utf-8" />
<title>PHP Update Data From MySql - By Cleartuts</title>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<center>

<div id="header">
 <div id="content">
    <label>PHP PHP Update Data From MySql - By Cleartuts</label>
    </div>
</div>
<div id="body">
 <div id="content">
    <form method="post">
    <table align="center">
    <tr>
    <td><input type="text" name="first_name" placeholder="First Name" value="<?php echo $fetched_row['first_name']; ?>" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="last_name" placeholder="Last Name" value="<?php echo $fetched_row['last_name']; ?>" required /></td>
    </tr>
    <tr>
    <td><input type="text" name="city_name" placeholder="City" value="<?php echo $fetched_row['user_city']; ?>" required /></td>
    </tr>
    <tr>
    <td>
    <button type="submit" name="btn-update"><strong>UPDATE</strong></button>
    <button type="submit" name="btn-cancel"><strong>Cancel</strong></button>
    </td>
    </tr>
    </table>
    </form>
    </div>
</div>

<div id="footer">
 <div id="content">
    <hr /><br/>
    <label>for more tutorials and blog tips visit <a href="http://cleartuts.blogspot.com"> : cleartuts.com</a></label>
    </div>
</div>

</center>
</body>
</html>

style.css


@charset "utf-8";

*
{
 margin:0;
 padding:0;
}
body
{
 background:#fff;
 font-family:"Courier New", Courier, monospace;
 color:#000;
}
#header
{
 width:100%;
 height:50px;
 background:#00a2d1;
 color:#f9f9f9;
 font-family:"Lucida Sans Unicode", "Lucida Grande", sans-serif;
 font-size:30px;
 text-align:center;
}
#body
{
 margin-top:50px;
}
table
{
 width:90%;
 font-family:Tahoma, Geneva, sans-serif;
 font-weight:bolder;
 color:#000;
}
table,td,th
{
 border-collapse:collapse;
 border:solid #d0d0d0 1px;
 padding:20px;
}
table td input
{
 width:97%;
 height:35px;
 border:dashed #00a2d1 1px;
 padding-left:15px;
 font-family:Verdana, Geneva, sans-serif;
 box-shadow:0px 0px 0px rgba(1,0,0,0.2);
 outline:none;
}
table td input:focus
{
 box-shadow:inset 1px 1px 1px rgba(1,0,0,0.2);
 outline:none;
}
table td button
{
 border:solid #f9f9f9 0px;
 box-shadow:1px 1px 1px rgba(1,0,0,0.2);
 outline:none;
 background:#00a2d1;
 padding:15px;
 color:#f9f9f9;
 font-family:Arial, Helvetica, sans-serif;
 font-weight:bolder;
 border-radius:3px;
 width:49%;
}
table td button:active
{
 position:relative;
 top:1px;
}
#footer
{
 margin-top:100px;
 font-weight:bolder;
 color:#00a2d1;
 font-family:Verdana, Geneva, sans-serif;
 font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;
 font-family:"Courier New", Courier, monospace;
 font-size:20px;
}
#footer a
{
 color:#004567;
 text-decoration:none;
}
#footer a:hover
{
 color:brown;
}
#footer hr
{
 width:80%;
}

Download Script