PHP Update Data of MySQL | Coding Cage

PHP Update Data of MySQL

By
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.
How to Update Data of MySQL in PHP


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:

PHP update Data of MySQL


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

PHP update Data of MySQL

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%;
}




3 comments:

  1. Very good tutorial, thanks!

    ReplyDelete
  2. Im getting an error with the edit.php page my error does not allow me to update the information it does not fetch the row it says it is undefined but I have it defined earlier in the script any suggestions? The errors come at 67,70 and 73

    ReplyDelete
  3. Nice Effort .. and thanks for the latest information..

    Ecommerce Website Design | Web Development Company Delhi

    Thanks!
    Best regards,
    Domaxy

    ReplyDelete