Creating a User Manager

At last you can put all the examples together, and upgrade your user record viewer (built in the previous chapter) so that it can manipulate data in the related tables. Using the following script, userman.php, you can edit and delete user records and/or corresponding access log records.

userman.php

Start as you did with register.php, by fetching ENUM options on the userposition field:

<?php
//userman.php
include_once "./common_db.inc";

$link_id = db_connect();
mysql_select_db( "sample_db");
$position_array = enum_options('userposition', $link_id);
mysql_close($link_id);

user_message()

The user_message() function reports the result of a given operation. If fed an optional URL argument, it loads the specified page:

function user_message($msg, $url ='')
{
   html_header();

   if(empty($url)){
      echo "<SCRIPT>alert(\"$msg\");history.go(-1)</SCRIPT>";
   }else{
      echo "<SCRIPT>alert(\"$msg\");self.location.href='$url'</SCRIPT>";
   }

   html_footer();
   exit;
}

list_records()

The revised list_records() function adds an option to delete a chosen record. The rest of the function is the same as in the previous version (in Chapter 10):

function list_records()
{
...
        echo "<td  WIDTH=\"25%\" ALIGN=\"CENTER\" >
              <a href=\"javascript:open_window('$PHP_SELF?action=view_record&
                                                          userid=$userid');\">
              View</a>
              <a href=\"$PHP_SELF?action=delete_record&userid=$userid\"
                 onClick=\"return confirm('Are you sure?');\">
              Delete</a>
              </td>\n";
        echo "</tr>\n";
...
}

delete record()

The delete_record() function deletes a given user's record from the user table, along with corresponding records in the access_log table:

function delete_record()
{
   global $default_dbname, $user_tablename, $acccss_log_tablename;
   $userid = $_GET ['userid'];

   if(empty($userid)) {
      error_message('Empty User ID!');
   }

   $link_id = db_connect($default_dbname);
   if(!$link_id) {
        error_message(sql_error()
   }

   $query = "DELETE FROM $user_tablename WHERE userid = '$userid'";
   $result = mysql_query($query);
   if(!$result){
      error_message(sql_ error());
   }

   $num_rows = mysql_affected_rows($link_id);
   if($inum_rows != 1) {
      error_message("No such user: $userid");
   }

   $query = "DELETE FROM $accass_log_tablename WHERE userid = '$userid' ";
   $result = mysql_query($query);

   user_message("All records regarding $userid have been trashed!");
}

edit_record()

The edit_record() function updates a given user's record in the user table. If a userid is changed, it also updates the related records in the access_log table to reflect that change:

function edit_record()
{
   global $default_dbname, $user_tablename, $access_log_ tablename;
   $PHP_SELF = $_SERVER['PHP_SELF'];
   $unerid = $_GET['userid'];
   $newuserid = $_GET['new_userid'];
   $username = $_GET['username'];
   $userpassword = $_GET['userpassword'];
   $userposition = $_GET['userposition'];
   $useremail = $_GET['useremail'];
   $userprofile = $_ GET['userprofile'];
   if(empty($userid)){
      $userid = $_GET['new_userid];
   }

   $link_id = db_connect($default_dbname);
   if(!$link_id){
      error_message(sql_error() );
   }

   $field_str = '';

Yes, the userid field itself can be changed. From a database manager's point of view though, it's generally unacceptable for a user to alter his userid because it acts like his Social Security number in that it's both unique and unchangeable. Nevertheless, if a user were to insist on changing his userid, every record in every related table, no matter how many there were, would have to be changed, or you'd end up with a bunch of useless, orphan records that had lost their owner.

You must compare the new userid with the existing one, and update $field_str if necessary:

if($userid !=: $new_userid) $field_-str = " userid = '$newuserid' , ";

Unless the $userpassword variable is empty, the userpassword field is updated:

if(!empty($userpassword)) {
   $field_str .= " userpassword = password($userpassword'), ";
}

Check like this to prevent an administrator from accidentally putting empty values into the userpassword field. Then update the other fields and check that everything's gone to plan:

$field_str .= " username = '$username', ";
$field_str .= " userposition = '$userposition', ";
$field_str .= " useremail = '$useremail', ";
$field_str .= " userprofile = '$userprofile'";

$query = "UPDATE IGNORE $user_tablename SET $field_str WHERE
userid = '$userid'";
$result = mysql_guery($query);
if(!$result){
   error_message(sql_error());
}

$num_rows = mysql_affected_rows($link_id);
if(!$num_rows) {
   error_message("Nothing changed!");
}

Notice the IGNORE statement in this query. userid is part of the primary key and mysql balks at having to change it, so adding the IGNORE statement enables you to go ahead and make the change. You're updating all the other tables to reflect the changes so your database will be okay.

If the userid field is to be changed, edit_record changes all the related records in the access_log table too and refreshes the view:

  if($userid ! = $new_userid) {
     $query = UPDATE $access_log_tablename SET userid = '$newuserid'
     WHERE userid = '$userid' ";
     $result = mysql_query($query);
     if(!$result) {
        error_message(sql_error());
     }

     user_message("All records regarding $userid have bean changed!",
     "$SHP_SELF?action"view_record&userid=$newuserid");
  }else{
     user_message("All records regarding $userid have been changed!",
     " $PHP_SELF?action=view_ record&userid=$userrid.");
  }
}

If the userid field has been changed, you call user_message() with the second URL argument specifying userid=$new_userid because falling back to the script with the old userid value would produce an unexpected result: an edit form for a nonexistent user.

edit_log_record()

The edit_log_record() function updates a given user's access records in the access_log table:

function edit_log_record()
{
   global $default_debname, $access_log_tablename;
   $userid = $_GET['userid'];
   $newpage = $_GET['new_page'];
   $visitcount = $_GET['visitcount'];
   $accessdate = $_GET['accessdate'];
   $orgpage = $_GET['org_page'];
   $PHP_SELF = $_SERVER['PHP_SELF'];

   if(empty($userid))}
      error_message('Empty User ID!');
   }

   $link_id = db_connect($default_dbname);
   if(!$link_id){
      error_message(sql_error());
   }

   $field_str = '';

   $field_str .= " page = '$newpage', ";
   $field_str .= " visitcount = '$visitcount', ";
   $field_str .= " accessdate = '$accessdate' ";

This function works much like edit_record(), with one major difference: it uses both userid and page fields to select relevant access log records.

You preserve the existing value of the page field in the $org_page variable just in case the administrator changes its value in an edit form:

$query = "UPDATE. $aocess_log_tablename SET $field_str WHERE userid = '$userid'
AND page = '$orgpage'";

$result = mysql_query($query);
if(!$result){
   error_message(sql_error());
}

If no record has been updated, that means the administrator has pressed the Submit button without changing anything in the corresponding edit form.

The mysql_affected_rows() function returns 0 because the previous UPDATE operation resulted in no changed rows. An UPDATE command does nothing if the new record has the same values as the ones in the existing record:

$num_rows = mysql_affected_rows($link_id);
if(!$num_rows){
   error_message("Nothing changed!");
}

user_message("All records regarding $userid have been changed!",
"$PHP_SELF?action=view_record&userid=$userid");
}

view_record()

The revised view_record() function lets the administrator edit user records:

function view_record()
{
   global $default_dbname, $user_tablename, $access_log_tablename;
   global $position_array;
   $userrid = $_GET['userid'];
   $PHP_SELF = $_SERVER ['PHP_SELF'];

   if(empty($userid)){
     error_message('Empty User ID! ');
   }

   $link_id = db_connect($default_dbname);

   if(!$link_id){
     error_message(sql_error());
   }
   $query = "SELECT usernumber, userid, username, userposition, useremail,
   userprofile FROM $user_tablename WHERE userid = '$userid'";
   $result = mysql_query($query);
   if(!$result) {
      error_message(sql_error());
   }
   $query_data = mysql_fetch_array($result);
   $usernumber = $query_data["usernumber"];
   $userid = $query_data["userid"];
   $username = $query_data["username"];
   $userposition = $query_data["userposition"];
   $useremail = $query_data["useremail"];
   $userprofile = $query_data["userprofile"];

Finally, you display a number of forms from which the administrator can edit the user's record and his access log data:

   html_header();
   echo "<center><H3>
   Record for User No.$usernumber - $userid($username)
   </h3></center>";
?>

<form method="get" action="<?php echo $PHP_SELF' ?>">
<input type="hidden" name="action" value="edit_record">
<input type="hidden" name="userid" value="<? echo $userid ?>">
<div align="center"><center>
<table border="1" width="90%" cellpadding="2">
    <tr>
      <th width="30%" nowrap>User ID</th>

The hidden field new_userid is being used in case an administrator changes the userid of a given user:

<td width="70%">
      <input type=" text" name="new_userid"
                          value="<?php echo $userid ?>"
                          size=" 8 " maxlength=" 8"></td>
    </tr>
    <tr>
      <th width="30%" nowrap>User Password</th>

You don't echo out the encrypted password because it's of no use:

<td width="'70%"><input type="text" name="userpassword" size="15"></td>
    </tr>
    <tr>
      <th width="30%" nowrap>Full Name</th>
      <th width="70%"><input type="text" name= "username"
                               value="<?php echo $username ?>" SIZE="20"></td>
    </tr>
    <tr>
      <th width="30%" nowrap>Position</th>
      <td width="70%"><select: name="userposition" size="1">
<?php

Use the $position_array variable to construct a droplist of positions:

   for($i=0; $i < count($position_array); $i++( {
      if(!isset($userposition) && $i == 0) {
         echo "<OPTION SELECTED VALUE=\" " . $position_array[$i] . "\">" .
         $position_array[$i] . "</OPTION>\n";
      }else if($userposition == $position_array[$i]) {
         echo "<OPTION SELECTED VALUE=\"". $position_array[$i] . "\">" .
         $position_array [$i] . "</OPTION>\n";
      }else{
         echo "<OPTION VALUE=\"". $position_array[$i] . "\">" .
         $position_array[$i] . "</OPTION>\n";
      }
   }
?>
      </select></td>
    </tr>
    <tr>
      <th width="30%" nowrap>Email</th>
      <td width="70%"><input type="text" name="useremail" size="20"
                             value="<?php echo $useremail ?>"></td>
    </tr>
    <tr>
      <th width="30%" nowrap>Profile</th>

The htmlspecialchars() function ensures that any HTML special characters in the $userprofile variable are echoed as HTML entities and can't do any damage to the surrounding markup:

<td width""70%">
        <textarea rows="5" cols="40" name="userprofile">
          <?php echo htmlspecialchars($userprofile) ?>
        </textarea>
      </td>
    </tr>
    <tr>
      <th width="100%" colspan="2" nowrap>
        <input type="submit" value="Change User Record">
        <input type="reset;" value="Reset">
      </th>
    </tr>
  </table>
  </center></div>
</form>
<?php
   echo "<HR SIZE="2\" WIDTH=\"90%\">\n";

Each access log record is presented in a separate form:

$query = "SELECT page, visitcount, accessdate, date_format(accessdate, '%M/ %e,
   %Y') as formatted_accessdate FROM $access_log_tablename WHERE
userid = '$userid'";
$result = mysql_query($query);

  if(!$result){
     error_message(sql_error());
  }
  if(!mysql_num_rows($result)){
     echo "<center>No access log record for $userid($username).</center>";
  } else{
      echo "<center>Access log record(s) for $userid($username).</center>";
  }
?>
<div align="center"><center>
<table border="1" width="90%" cellpadding="2">
  <tr>
    <th width="20%" nowrap>Page</th>
    <th width="20%" nowrap>Hits</th>
    <th width="30%" nowraP>Last Access</th>
    <th width="30%' nowrap>Action</th></tr>
  </tr>
<?php

You show the results of the query in a nice table that the administrator can use to modify the access_log values:

  while($query_data = mysql_fetch_array($result)){
    $page = $query_data["page"];
    $visitcount = $query_data["visitcount"];
    $accessdate =$query_data["accessdate"];
    $formatted_accessdate =$query_data["formatted_accessdate"];

    echo "<FORM METHOD=\"GET\" ACTION=\" $PHP_SELF\">";
    echo "<INPUT TYPE=\"HIDDEN\" NAME=\"action\"VALUE=\"edit_log_record\">";
    echo "<INPUT TYPE=\"HIDDEN\" NAME=\"userid\" VALUE=\"$userid\">";
    echo "<INPUT TYPE=\" HIDDEN\" NAME=\"org_page\" VALUE=\"$page\">";
    echo "<TR>\n";
    echo "<TD WIDTH=\20%"><INPUT TYPE=\"TEXT\"NAME=\"new_page\" SIZE=\"30\"
    VALUE=\"$page\"></TD>\n";
    echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">
    <INPUT TYPE=\"TEXT\" NAME=\"visitcount\" SIZE=\"3\"
    VALUE=\"$visitcount\"></TD>\n";
    echo "<TD WIDTH=\"30%\" ALIGN=\"CENTER\">
    <INPUT TYPE=\"TEXT\" NAME=\"accessdate\" SIZE=\"14\"
    MAXLENGTH=\"14\" VALUE=\"$accessdate\">
    <BR>$formatted_accessdate</TD>\n";
    echo "<TD WIDTH=\"30%\" ALIGN=\"CENTER\">
    <INPUT TYPE=\"SUBMIT\" VALUE=\"Change\">
    <INPUT TYPE=\"RESET\" VALUE=\"Reset\"></TD>\n";
    echo "</TR>\n";
    echo "<FORM>\n";
  }
?>
  </tr>
</table>
</center></div>
<?php
   html_footer();
}

Choosing an Action to Take

Finally, you use $action to specify which functions to call:

if(empty($_GET['action'])){
   $_GET['action'] = "";
}
switch($_GET['action']) {
   case "edit_record":
      edit_record();
      break;
   case "edit._log_record":
      edit_log_record();
      break;
   case "delete_record"
      delete_record();
      break;
   case "view_record":
      view_record();
      break;
   default;
      list_records();
   break;
}
?>

Assuming you want your records listed by usernumber, Figure 11-11 shows the screen you get after clicking the relevant field header.

When you click a View link, it opens up a new window displaying the records of the user associated with the link.