Update Data of MySQL using PHP
In today’s tutorial we will see how to update MySQL data using PHP.
I have divided this updation code in two php files. First is emp_update.php and second update.php.First file emp_update.php will fetch all data from specified table with update link associated to each individual record. When you click on that update link, associated id of employee will be passed to the next php file update.php.
Lets see how to update MySQL data using PHP.
Update Syntax :
update table_name set column1= value_1, column_2=value_2,… coulmn_n=value_n where column_name=Value ;
In this code I have used :
- mysql_connect() : Used to open connection in MySQL and takes three parameter i.e. SERVER,USER_NAME and PASSWORD. we take default values for these parameters i.e. localhost, root and empty string respectively. This function returns a MySQL link identifier on successful connection and return false if fails to open connection.
- mysql_error() : this returns the text of the error message from previous MySQL operation and empty string if no error occurred.
- mysql_select_db() : Sets the current active database on the server which is associated with specified link identifier returned by mysql_connect().
- mysql_query() : This sends a query to current active database on the server. It takes SQL query as parameter.
- mysql_fetch_row() : This function get a result row as an enumerated array. It returns numerical array corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_row() fetches one row of data from the result associated with the specified result identifier. The row is returned as an array and each result column is stored in array offset which starts at offset 0.
- header() : With this function you can redirect to the another page without any click or link. Ex : Header(‘Location : https://www.freefeast.info’); But the header() function, you have to call before any of the actual output is sent may be HTML tags, blank lines.
Lets first see : emp_update.php
<html>
<body>
<center>
<h2 align="center">Employee Data</h2>
<table border="1">
<tr align="center">
<th>Emp_Id </th> <th>Emp_name </th> <th>Age</th> <th>Designation</th> <th>Salary</th>
</tr>
<?php
$con=mysql_connect("localhost","root","");
if(!$con)
{
echo "Connection error".mysql_error();
}
else
{
$res=mysql_select_db("employee",$con);
if(!$res)
{
echo "Database not Found...".mysql_error();
}
else
{
$res=mysql_query("Select * from emp");
echo "<tr><td colspan='6 '></td></tr>";
while($r=mysql_fetch_row($res))
{
echo "<tr>";
echo "<td align='center'>$r[0]</td>";
echo "<td width='200'>$r[1]" . " $r[2]</td>";
echo "<td align='center' width='40'> $r[3]</td>";
echo "<td align='center' width='200'>$r[4]</td>";
echo "<td width='100' align='center'>$r[5]</td>";
echo "<td><a href=update.php?uno=$r[0]> update</a>";
echo "</tr>";
}
}
}
?>
Output :
Lets see another PHP file : update.php
In this file, You will able to see Employee Id but that is read-only, you can not edit it and other data corresponding to that Employeed Id will also be displayed and you can edit those data. Enter data you want to change and others keep as it is and by clicking on update, that record will be get updated and you will be redirected to emp_update.php.
<html>
<body>
<h2 align="center"><b>Edit Employee Data </b></h2>
<form name="f1" method="post" action="update.php">
<center>
<table border="1" cellspacing="20">
<tr> <td> Employee Id : </td>
<td><input type="text" name="txtEno" size="30" value="<?php echo $_REQUEST["uno"] ?>" readonly="true"> </td>
</tr>
<?php
$con=mysql_connect("localhost","root","");
$res=mysql_select_db("employee",$con);
$res=mysql_query("Select * from emp where empId='".$_REQUEST["uno"]."'");
$r=mysql_fetch_row($res)
?>
<tr> <td> Enter First Name : </td>
<td><input type="text" name="txtFname" size="30" value="<?php echo $r[1] ?>"></td></tr>
<tr> <td> Enter Last Name : </td>
<td><input type="text" name="txtLname" size="30" value="<?php echo $r[2] ?>"></td> </tr>
<tr> <td> Enter Age : </td>
<td><input type="text" name="txtage" size="30" value="<?php echo $r[3] ?>"></td> </tr>
<tr> <td> Enter Designation : </td>
<td><input type="text" name="txtdes" size="30" value="<?php echo $r[4] ?>"></td> </tr>
<tr> <td> Enter Salary : </td>
<td><input type="text" name="txtsalary" size="30" value="<?php echo $r[5] ?>"></td> </tr>
<tr><td colspan="2" align="center"><input type="submit" value="Update" name="Update"></td></tr>
</table>
</form>
</body>
</html>
<?php
if(isset($_REQUEST["Update"]))
{
$eid=$_REQUEST["txtEno"];
$fname=$_REQUEST["txtFname"];
$lname=$_REQUEST["txtLname"];
$age=$_REQUEST["txtage"];
$des=$_REQUEST["txtdes"];
$salary=$_REQUEST["txtsalary"];
$con=mysql_connect("localhost","root","");
if(!$con)
{
echo "<br>Could Not Connect".mysql_error();
}
else
{
$result=mysql_select_db("employee",$con);
if(!$result)
{
echo "<br>Could Not Select database ".mysql_error();
}
else
{
$qry="update emp set firstName='".$fname."',lastName='".$lname."', age='".$age."', designation='".$des."',salary='".$salary."' where empId='".$eid."'" ;
$res=mysql_query($qry);
if(!$res)
{
echo "Updation Error ".mysql_error();
}
else
{
header("location:emp_update.php"); // This function will redirect page to the emp_update.php
}
}
}
}
?>
Output :
