Update record through PHP

SAMPLE: A FEEDBACK SYSTEM


Let’s 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 is not a very easy work because it takes about four (4) steps:
 
Step1. Have your record retrieve and displayed as we have above, 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 you can see and edit or modify the data from form text fields.
Also this page will have an hidden field which we will also use to 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. After editing, changing or modifying the data in the form element and submit, this data will now the posted or submitted to a final page that will affect the change in our database (using the UDATE SQL query), don’t forget the unique id (or just id) that is also submitted using the hidden field, is also use to know the exact record to update in our database or 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> </td>. See the line (bolded ) below

...
//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 displaying section also, but it will not be empty as it will have the edit link to a page URL with a query string carrying the unique id of each record or row. See the link line (bolded) below.

//loop through the query result and print
while($info = mysql_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>
"; }

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.

 

THE BROWSER VIEW
items

The screenshot above is what the browser view of the page will look like; now when I move my mouse to the Edit link of Ben Onuorah…. Record, as illustrated using the arrow with label 1, you will notice that the URL in the status bar of the browser, attached 10 to the id variable.

    http://localhost/bws/feedback_edit.php?id=10


This indicates that the id of the record being pointed to is 10 in the database, when you click the link this id take the number 10 to the page being linked to, in this example feedback_edit.php,  therefore you can collect this value their using $_GET[‘id’] , this we will discuss shortly.

Note: I hope you’ve already created the page feedback_edit.php, please do so if you have not, this page should contain a form with the elements as we have in contact.htm.

 

BY THE WAY - Link query with multiple variable: as you’ve learnt now that the link query (?id=10) means the variable id is taking one value 10 to the receiving page, similarly you may want to pass more than one variable using the link query, let’s say I need the name and email as well. Below is what I should have in my source code.

<a href=\"feedback_edit.php ?id=$data_id&name=$data_name&email=$data_email\">

And the browser status view will look like:

multi

 

Note: in this case we introduce more variables name and email and join it using the ampersand (&) not question mark (?) as use in initial id variable, as usual we assign their respective variables $data_name and $data_email to them as we did for id.

Also note that a query string is appended to the URL when we click on the link, and because the URL bar of a browser may have a specific limit, it is advisable to limit the number of variables we assign or be watch their expected value. For example I won’t advice you include the comment $data_comment because you cannot reassume how long it would be.

Note: if you had try testing this multiple variable example, please return the code to the initial example <a href=\"feedback_edit.php?id=$data_id\"> Edit </a> Then continue with the Step 3

 

Editing Mode (Step3)
Again I assumed that you already created the page feedback_edit.php, please do if you have not already, this page should contain a form with the elements as we have in contact.htm.
Now when the you click on the edit link, this is what you should have in your browser

edit1

These form elements are expected to show the details of the selected record, so that we can edit by changing this detail from here.

Now let’s go to the source of feedback_edit.php and

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

Below is the code in my feedback_edit.php page, it contains the (A) and (B) as I listed above, I will introduce the (C) after explaining this code.


feedback_edit.php

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

//get the from the query link
$query_id = mysql_real_escape_string($_GET['id']);

//query the table using the value from the query link
$query  = "SELECT * FROM feedback WHERE id='$query_id'";
$result = mysql_query($query, $conn) or die(mysql_error());

$info = mysql_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'];
?>

<!-- THE HTML SECTION -->
<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" />
</td>
</tr>
<tr>
<td><strong>Email</strong></td>
<td>
<input name="email" type="text" id="email" size="30" />
</td>
</tr>
<tr>
<td></td>
<td><textarea name="comment" cols="30" rows="5" id="comment"></textarea>
</td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" name="Submit" value="Submit" />
</td>
</tr>
</table>
</form>
</BODY>
</HTML>


This code is similar to the one we use in listing the record in the last exercise; except that the record is not in while loop, this is not necessary because the ID is expected to retrieve just a single record.

I bolded the only line and area that need further explanation

$query_id = mysql_real_escape_string($_GET['id']);

The PHP $_GET[ ] special variable is use to collect the value of a query link, since the variable name is id (http://localhost/bws/feedback_edit.php?id=10) that is why we collect the value using $_GET['id'] and as we do when taken in data earlier we need to clean up the information using mysql_real_escape_string(), the assign it to the variable $query_id, this is what the bolded line above does.

Now I edit the HTML section the FORM to be precise I echo the variables retrieved to the values of the element (echo or print may be use, I purposely use echo here), see the bolded lines below.


<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" value="<?php echo $data_name; ?>" size="30" />
</td>
</tr>
<tr>
<td><strong>Email</strong></td>
<td>
<input name="email" type="text" id="email"  value="<?php echo $data_email; ?>" size="30" />
</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="Submit" value="Submit" /></td>
</tr>
</table>
</form>  

Notice that the last line element type is hidden and its value is to hold the unique id of the value retrieve (this value would be 10 for this example as we already seen in the URL query) it is important we do this, because the value will be use in the UPDATE query in the page this form will be submitted to feedback_edited.php

 <form method="post" action="feedback_edited.php">

Before we continue with the final stage feedback_edited.php where the UPDATE query would be, let’s see the preview of what we have done in the last exercise.

feedback_edit.php
edit2

With the record in editing mode (that is; seen in the form), it is easy to modify a data, simply by changing it. For example I decide to change “Ben” to my full “Benjamin”


Before you click the submit button to update the change let’s create and program the page it will be posting to, that is feedback_edited.php

 

Update record (Step4)
Please create the file feedback_edited.php if you have not done so already, and let it contain the code below.

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

// keep the form data in variable   
$name=mysql_real_escape_string($_POST['names']);
$email=mysql_real_escape_string($_POST['email']);
$comment=mysql_real_escape_string($_POST['comment']);
$id=mysql_real_escape_string($_POST['sel_id']);

 

//update query

$sql = "UPDATE feedback SET         
           name='$name',
           email='$email',
           comment='$comment'  
           WHERE id= '$id'";         

//execute the query
mysql_query($sql,$conn) or die(mysql_error());

//store the msg
$msg = "Record Successfully Updated";
?>

<!-- THE HTML SECTION -->
<HTML>
<HEAD>
<TITLE>Company Feedback</TITLE>
</HEAD>
<BODY>
<p><a href="feedback_pg.php">Return</a></p>

<p> 
<?php
// display the msg
if(isset($msg)){
     echo $msg;
}
?>
 
</p>
</BODY>
</HTML> 

This program is similar to the one we use in inserting into the database (See the Insert the data submitted into the database page) except for the UPDATE query.

$sql = "UPDATE feedback SET         
           name='$name',
           email='$email',
           comment='$comment'  
           WHERE id= '$id'";  
       

The variable $id, is collected from the hidden field sel_id and kept in the $id, notice the line just above the UPDATE query $id=mysql_real_escape_string($_POST['sel_id']);

By the time I click on the submit form, the left screenshot below is what I have on my browser.


That’s all, the record is updated, to confirm it I click on the Return link taken me back to feedback_pg.php and as shown in the screenshot below, Ben is now Benjamin.

 

So we have come to the end of the lesson, next we will be looking at how to DELETE a record.

 

Next »


Return to PHP Sample Main Page