Technology. Education. Art (TEA) TEAlearn.org

Home > Courses > Web development using PHP and MySQL > Update record using PHP

Update record using PHP

Subject: Web development using PHP and MySQL

Let us assume that you have a small news web portal, after posting and displaying the news well paginated for your user to view but then you notice a mistake in the posted news, to correct this you will have to update or edit that content.

Updating a record through a web form as illustrated above involve four steps:

Step1. Select or retrieve and displayed the record as we have done in previous topic with or without pagination

Step2. Introduce the edit link to a page URL with a query string carrying the unique id of each record (this is where we will start this lesson from)

Step3. When the edit link is clicked, you are lead to a page (usually this page will contain a form), here you will use the value in the query string to retrieve or SELECT the record having that unique id from the database and display the individual values in the form element so that the user can see and modify the data in the form text fields.

Also this page will have an hidden field which we will also hold the unique id, because this form must be posted together to the final page with the id of the record to be edited

Step4. The form data with the hidden field is then posted or submitted to the URL that will affect the change in our database using the UDATE SQL query, the hidden id field data is use to know the exact record to update in our table.

The Edit Link (Step2)
From step 2, Open the feedback_pg.php where we have the record displayed and paginated. Now locate the table heading in the code and include an extra empty table data tag (td)


...
//the table header
print "
<table width=\"50%\" border=1>
<tr>
<td>NAME</td>
<td>EMAIL</td>
<td>COMMENT</td>
<td>DATE POST</td>
<td> </td>
</tr>
";  ...

Now we will have to do the same at the data section also with a special link (with a query string carrying the unique id of each record or row) to a page URL


....
//loop through the query and print the result
while($info = mysqli_fetch_array($result)){
     $data_id = $info['id'];
     $data_name = $info['name'];
     $data_email = $info['email'];
     $data_comment = $info['comment'];
     $data_date = $info['date_submit'];

           print "
           <tr>
               <td> $data_name </td>
               <td> $data_email </td>
               <td> $data_comment </td>
               <td> $data_date </td>
               <td><a href=\"feedback_edit.php?id=$data_id\">Edit</a></td>
           </tr>
           ";
}
print "</table>";
....

Let’s look at the newly introduced link, we are use to the first section of the link a href=\"feedback_edit.php , while the ?id=$data_id\" section needs some explanation, the question mark ? is the character that we use in initiate the query link, the id is just a variable name (querystring variable - it does not begin with the dollar $ sign), so you can use any name, and the remaining =$data_id assigns the value of the variable ($data_id) to the querystring variable (id).

Notice that $data_id is already declared and assign a value earlier, see the line $data_id = $info['id']; which is the id of the record retrieve from the database.

Editing Mode (Step3)
Create the feedback_edit.php file that the link with send it's data to and let it have the following code:

feedback_edit.php


<?php
//include the connection string
include("conn.php");

if(isset($_GET['id'])){
//get the from the query link
	$query_id = mysqli_escape_string($conn, $_GET['id']);
	
	//query the table using the value from the query link
	$query  = "SELECT * FROM feedback WHERE id='$query_id'";
	$result = mysqli_query($conn, $query) or die($query);
	
	$info = mysqli_fetch_array($result);
		//assign the result to valriable   
		$data_id = $info['id'];
		$data_name = $info['name'];
		$data_email = $info['email'];
		$data_comment = $info['comment'];
		$data_date = $info['date_submit'];
}
?>
<HTML>
<HEAD>
<TITLE> Company Feedback </TITLE>
</HEAD>
<BODY>
<form method="post" action="feedback_edited.php">
    <table>
<tr><td colspan="2"><b>UPDATE REDCORD</b></td></tr>
     <tr><td><strong> Name</strong></td>
<td>
<input name="names" type="text" id="names" size="30" value="<?php echo $data_name; ?>" />
</td>
    </tr>
    <tr><td><strong>Email</strong></td>
<td>
<input name="email" type="text" id="email" size="30" value="<?php echo $data_email; ?>" />
</td>
    </tr>
    <tr><td></td>
<td>
<textarea name="comment" cols="30" rows="5" id="comment"><?php echo $data_comment; ?></textarea>
</td>
</tr>
     <tr>
<td></td>
<td>
<!-- HIDDEN ELEMENT -->
<input name="sel_id" type="hidden" value="<?php echo $data_id; ?>" />
<input type="submit" name="edit" value="Update" /></td>
     </tr>
     </table>
 </form>
</BODY>
</HTML>

The fields of this form in this file are expected to show the details of the selected record, so that we can edit by changing this detail from there before finally submitting for update.

Here is what the code in feedback_edit.php basically doing:

1. Connect of database
2. Query or SELECT from the required table using the value from the query link
3. Append the respective data to the form element (including the hidden field)

See the screenshot (Update form)

Update record (Step4)
Create the file feedback_edited.php with the code code below.

feedback_edited.php


<?php
//include the connection string
include("conn.php");
if(isset($_REQUEST['edit'])){
	
	// keep the form data in variable  
	$names = mysqli_escape_string($conn, $_POST['names']);
	$email = mysqli_escape_string($conn, $_POST['email']);
	$comment = mysqli_escape_string($conn, $_POST['comment']);
	$id=mysqli_escape_string($conn, $_POST['sel_id']);
	
	$sql="UPDATE feedback SET 
			name='$names', 
           	email='$email',
           	comment='$comment'  
           	WHERE id= '$id'"; 
	$result = mysqli_query($conn, $sql) or die($sql);
	
	if($result){
		$msg = "Record Successfully Updated";
	}
}
?>
<HTML>
<HEAD>
<TITLE> Company Feedback </TITLE>
</HEAD>
<BODY>
<p><a href="feedback_table_paginate_update.php">Return</a></p>
<?php
if(isset($msg)){
	// display the thanks msg
	print $msg;
}
?>
</BODY>
</HTML>

Notice that the variable $id, is collected from the hidden field sel_id and kept in the $id, and use to specify the id of the record in the table to be update


....
$id=mysqli_escape_string($conn, $_POST['sel_id']);
.....
UPDATE feedback SET 
			name='$names', 
       email='$email',
       comment='$comment'  
        WHERE id= '$id'

See the screenshots below.

Record with "Edit" link