Modifying a Record Using the Admin Browser

 

Introduction.  Now we are going to augment our functionality by incorporating some capacity to modify records.  We will assume the admin knows who and what they want to modify so they do not need to enter any extra information.

Rather than using a modify button, we will structure these modifications so that the admin can change whatever they want in the record that is currently displayed and then press an Update button to cause the changes to be committed to the table.

Similarly to the delete, the only potentially surprising thing is that we need a way to identify which record was showing when the update button was pressed.  This was not how it was done on the client side.  We need this identification because when the update button is pressed and the current form information is submitted, some of the form information may have been changed and we won't be able base our update query on form information which may be different than what is in the table.

As usual we will work with the admin_browser.php.  Again, the new code is highlighted in red.

 

<?php
// assign the values for database access
$host = "localhost";
$user = "your_user_name";
$password = "your_password";
$db_name = "database_name";
$table_name = "user_registration";

session_start( );
// initializing the record count
// and session variable to keep track of current record
if (!isset($_SESSION['current_row']))
{

$_SESSION['current_row'] = 1;

}

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "SELECT * FROM $table_name";
// executing the query
$result_set = mysql_db_query($db_name, $query_string, $link);
// obtaining the number of rows in the record set
$number_rows = mysql_num_rows($result_set);
// Now we have our code that handles the command buttons
// the block of code to move the admin to the previous record
//
// if the First button has been pressed
if ($cmdFirst != "")
{

// setting the row counter to 1
$_SESSION['current_row'] = 1;

}
// the block of code to move the admin to the previous record
// if the Previous button has been pressed
if ($cmdPrevious != "")
{

// making certain the admin is not already at the first record
if ($_SESSION['current_row'] == 1)
{

echo("<font size=5>You are already at the first record!</font>");

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}
// the block of code to move the admin to the next record
// if the Next button has been pressed
if ($cmdNext != "")
{

// making certain the admin is not already at the last record
if ($_SESSION['current_row'] == $number_rows)
{

echo("<font size=5>You are already at the last record!</font>");

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] + 1;

}

}
// if the Last button has been pressed
if ($cmdLast != "")
{

// setting the row counter to the last record
$_SESSION['current_row'] = $number_rows;

}
// if the Update button has been pressed
if ($cmdUpdate != "")
{

// ensuring the checkboxes have some value
if ($chk_php != 1) $chk_php = "";
if ($chk_jsp != 1) $chk_jsp = "";
if ($chk_mysql != 1) $chk_mysql = "";
// setting a variable to use for the update query
$user_id_number = $_SESSION['current_user_id'];
// developing the update query string based on the user_id
$update_string = "UPDATE $table_name SET
first_name = '$txt_first_name',
last_name = '$txt_last_name',
email = '$txt_email',
password = '$txt_password',
php_interest = '$chk_php',
jsp_interest = '$chk_jsp',
mysql_interest = '$chk_mysql',
credit_card = '$sel_credit_card',
education = '$rb_education'
WHERE (user_id = '$user_id_number')";
// updating the entry
$update_set = mysql_db_query($db_name, $update_string, $link);
// giving the admin a message that the update occurred
echo("<font size=4><b>The update was completed</b></font>");
// change the current record and display based on where you are
if ($_SESSION['current_row'] == 1)
{

$_SESSION['current_row'] == 2;

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}
// if the Delete button has been pressed
if ($cmdDelete != "")
{

// setting a variable to use for the delete query
$user_id_number = $_SESSION['current_user_id'];
// developing the deletion query string based on the user_id
$delete_string = "DELETE FROM $table_name WHERE (user_id = '$user_id_number')";
// deleting the entry
$delete_set = mysql_db_query($db_name, $delete_string, $link);
// decrementing the total number of entries due to the deletion
$number_rows--;
// change the current record and display based on where you are
if ($_SESSION['current_row'] == 1)
{

$_SESSION['current_row'] = 2;

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}
// a variable for printing
$current_row_number = $_SESSION['current_row'];
// go to this current row
mysql_data_seek($result_set, $_SESSION['current_row']);
// and retrieve it into an array
$row = mysql_fetch_row($result_set);
?>
<html>
<head>
<title>Admin Browser for User Registration Web</title>
</head>

<body bgcolor = "00bbdd" text="004466" link="004466" vlink="007799">
<form action="admin_browser.php" method=post>
<h2>Admin Browser for User Registration</h2>
<table>
<tr>
<td><font size = 4 color=004466>First Name:</font>
</td>
<td><input type=text name="txt_first_name" size=20 value = "<?php echo $row[1]; ?>">
</td>
</tr>
<tr>
<td><font size = 4 color=004466>Last Name:</font>
</td>
<td><input type=text name="txt_last_name" size=20 value = "<?php echo $row[2]; ?>">
</td>
</tr>
<tr>
<td><font size = 4 color=004466>EMail Address:</font>
</td>
<td><input type=text name="txt_email" size=50 value = "<?php echo $row[3]; ?>">
</td>
</tr>
<tr>
<td><font size = 4 color=004466>Password:</font>
</td>
<td><input type=password name="txt_password" size=50 value = "<?php echo $row[4]; ?>">
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td><font size = 4 color=004466>Interests:</font>
</td>
<td><input type=checkbox name="chk_php" value=1
<?php if (($row[5]) == 1 ) echo 'checked'; ?>>
<font size = 4 color=004466>PHP</font>
</td>
</tr>
<tr>
<td>
</td>
<td><input type=checkbox name="chk_jsp" value=1
<?php if (($row[6]) == 1 ) echo 'checked'; ?>>
<font size = 4 color=004466>JSP</font>
</td>
</tr>
<tr>
<td>
</td>
<td><input type=checkbox name="chk_mysql" value=1
<?php if (($row[7]) == 1 ) echo 'checked'; ?>>
<font size = 4 color=004466>MySQL</font>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td><font size = 4 color=004466>Credit Card:</font>
</td>
<td><select name="sel_credit_card">
<option value="Discover" <?php if (($row[8]) == 'Discover') echo 'selected'; ?>>Discover
<option value="MasterCard" <?php if (($row[8]) == 'MasterCard') echo 'selected'; ?>>Mastercard
<option value="Visa" <?php if (($row[8]) == 'Visa') echo 'selected'; ?>>Visa
</select>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td><font size = 4 color=004466>Education:</font>
</td>
<td><input type=radio name="rb_education" value="NoCollege"
<?php if (($row[9]) == 'NoCollege') echo 'checked'; ?>>
<font size = 4 color=004466>No College</font><br>
<input type=radio name="rb_education" value="College"
<?php if (($row[9]) == 'College') echo 'checked'; ?>>
<font size = 4 color=004466>College</font><br>
<input type=radio name="rb_education" value="Masters"
<?php if (($row[9]) == 'Masters') echo 'checked'; ?>>
<font size = 4 color=004466>Masters</font><br>
<input type=radio name="rb_education" value="PhD"
<?php if (($row[9]) == 'PhD') echo 'checked'; ?>>
<font size = 4 color=004466>Ph.D.</font>
</td>
</tr>
<tr>
<td>&nbsp;</td>
<td></td>
</tr>
<tr>
<td align = right>
<?php echo("<font size=4>Record $current_row_number</font>"); ?>
</td>
<td align = left>
<?php echo("<font size=4>of $number_rows</font>"); ?>
</td>
</tr>
<tr>
<td align = right>
<input type = submit name="cmdFirst" value="First">
<input type = submit name="cmdPrevious" value="Previous">
</td>
<td align = left>
<input type = submit name="cmdNext" value="Next">
<input type = submit name="cmdLast" value="Last">
</td>
</tr>
<tr>
<td align = right>
<input type = submit name="cmdUpdate" value="Update">
</td>
<td align = left>
<input type = submit name="cmdDelete" value="Delete">
</td>
</tr>
</table>
</form>
<?php
// establishing a session variable that can be used to
// obtain the user_id of the record currently displayed
// to be accessed when we return
// this will be important for deletions and modifications
$_SESSION['current_user_id'] = $row[0];
mysql_close($link);
?>
</body>
</html>

 

The form should now look like the following.  Notice there are no changes to the form other than there is an additional Update button in the same row with the Delete button.

 

 

So you should modify the code for your server's connections to MySQL and then upload it and move around.

Code Explanation.  The truly new lines of code are the following.  We will put them in a table in order to put an appropriate discussion in an adjacent cell.

 

// if the Update button has been pressed
if ($cmdUpdate != "")
{
 

 If the Update button has been pressed
// ensuring the checkboxes have some value
if ($chk_php != 1) $chk_php = "";
if ($chk_jsp != 1) $chk_jsp = "";
if ($chk_mysql != 1) $chk_mysql = "";
 These statements ensure that a blank is entered into the database table if a corresponding checkbox is unchecked.
 

// setting a variable to use for the update query
$user_id_number = $_SESSION['current_user_id'];

 Developing a local variable to retain the user_id of the record that has been modified.
 

// developing the update query string based on the user_id
$update_string = "UPDATE $table_name SET
first_name = '$txt_first_name',
last_name = '$txt_last_name',
email = '$txt_email',
password = '$txt_password',
php_interest = '$chk_php',
jsp_interest = '$chk_jsp',
mysql_interest = '$chk_mysql',
credit_card = '$sel_credit_card',
education = '$rb_education'
WHERE (user_id = '$user_id_number')";

 Construct the query string to be used for the update. 

Notice how the record selection is determined by the WHERE clause based on this $user_id_number.

// updating the entry
$update_set = mysql_db_query($db_name, $update_string, $link);

Actually execute the query to perform the update

// giving the admin a message that the update occurred
echo("<font size=4><b>The update was completed</b></font>");

Giving the admin a message that the update has been executed.
 

// change the current record and display based on where you are
if ($_SESSION['current_row'] == 1)
{

$_SESSION['current_row'] == 2;

}
else
{

$_SESSION['current_row'] = $_SESSION['current_row'] - 1;

}

}

 Change which record is displayed based on the location of the record which was just updated.

Move previous unless it was the first record in which case you should move next.

I consider this to be a very unfortunate solution to getting the display to update in concurrence with the update in the table.  I hope to find a better way to do this.

 
<td align = right>
<input type = submit name="cmdUpdate" value="Update">
</td>
 		 An entry in the left column in the same row where the Delete button was placed in the last webpage.

Other buttons will be placed in this row in later pages.