Web Coding Tips - Guide For Web Programmers: April 2011

Now let's build a user record viewer, entitled userviewer.php, that's going to use the tables from the sample_db database and perform the following functions:

Connect to the database sample_db that holds the user and access_log tables.
Display a list of registered users and navigation links, giving the administrator the option to move back and forth.
Display field name links that can be toggled to sort the list of users either in ascending or descending order on the specified field.
For each user display a link to a new window that presents more detailed information on that user and his access log records.

It'll take two separate files to create the database browser. The global variables and commonly used functions go into the include file, common_db.inc, and the business end of the browser will be placed in a file calleduserviewer.php. You can assume the existence of database sample_db and tables user and access_log, and that data is present in both these tables.

The common_db.inc File Contents

The following sections explain piece by piece what you should have in the file common_db.inc.

For the sake of brevity, the code for functions db_connect () and sql_error() isn't shown here. These should be exactly as specified in the last chapter.

Global Variables

There are a few variables that you must use in the script. You can place all of them in common_db.inc, and access them as global variables from within the userviewer script:(changing the variables to reflect your settings):

<?php
$dbhost = 'Iocalhost';
$dbusername = 'phpuser';
$dbuserpassword = 'phppass';
$default_dbname = 'sample_db';

Of course you'll change the variables to reflect your settings.

Stipulate the default for number of records to be displayed per page (5):

$records_per_page = 5;

Indicate the names of the user and access log tables:

$user  tablename = 'user';
$access.log.tablename = 'access.log';

Denote holders for MySQL error numbers and error text:

$MYSQL_ERRNO = ' ';
$MYSQL, ERROR  = ' ';

And state the size of the new browser window:

$new_win_width = 600;
$new_win_height = 400;

html_header()

The html_header() function starts an HTML page and defines the JavaScript function open_window() that you can call to open a new window when displaying a user's record:

function html_header()
{
   global $new_win_width, 5new_win_height;
   ?>
   <HTML>
   <HEAD>
   <SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
   <!--
   function open_window(url)
   {
      var NEW_WIN = null;
      NEW_WIN = window.open ("", "RecordViewer",
                             "toolbar-no, width="+
                             <?php echo $new_win_width ?>+
                             ",height="+<?php echo $new_win_height?>+
                             ",directories=no, status=no" +
                             ",scrollbars=yes,resize=no, menubar=no");
      NEW_WIN.location.href = url;
   }
   // --> </SCRIPT>
   <TITLE>User Record Viewer</TITLE>
   </HEAD>
   <BODY>
   <?php
}

html_footer()

The html_footer() function ends an HTML page (you've already seen the db_connect() and sql_error() functions in action):

function html_footer()
{
   ?>
   </BODY>
   </HTML>
   <?php
}

function db_connect($dbname=' ')
{
   ...}
}

function sql_error()
{
   ...
}

error_message()

The error_message() function reports errors using the JavaScript alert() method:

function error_message($msg)
{
   html_header();
   echo "<SCRIPT>alert(\"Error: $msg\ ");history.go (-1) </SCRIPT>";
   html_footer();
   exit;
}
?>

With the .inc file sorted out, let's go to the business end of the application.

The userviewer.php File Contents

The userviewer.php script makes use of the include file common_db.inc. It's included from the current directory for the sake of simplicity:

<?php
include "./common_db.inc";

Remember, though, that in practice it's never a good idea to include your database username and password in Web scripts. Put them in a separate file and place it beyond the Web document root.

Most of the actual code you use to implement the browser is contained in a couple of functions, which gather and display your database information. Let's take a look at those functions now.

list_records()

The list_records() function displays a list of registered users, along with navigation links and a record viewer link that calls the view_record() function. Start by connecting to the default database, and fetching the total number of registered users. If there aren't any, display an error message to that effect.

function list_records()
{
   global $default_dbname, $user_tablename;
   global $records_per_page;
   $PHP_SELF = "userviewer.php";

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

   $query = "SELECT count (*) FROM $user_tablename" ;

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

   $query_data = mysql__fetch_row($result);
   $total_num_user = $query_data[0];
   if(!$total_num_user) error_message("No User Found!');

The $cur_page global variable—denoting the current page number—holds the key to the list navigation. If more than one page of data is returned in the list, you chop it up into multiple pages. The page length is determined by the global variable $records_per_page. The total number of pages is obtained by dividing the total number of users ($total_num_user) by the length of a page ($records_per_page) and rounding up the resulting value with the ceil() function.

You increment the page number before it's echoed out because the $cur_page global variable counts from zero, and the page numbering that you display should start at 1. The variable $record is defined as the upper bound for the LIMIT statement, which you use in a SQL query in a moment. $cur_page is initilized with a value sent once the user has clicked a navigation link. If there is no value, set it to 0:

if(empty($_GET['next_page'])) {
   $_GET['next_page'] = 0;
}
$cur_page = $_GET['next_page'];
$page_num = $cur_page + 1;
$record = $cur_page * $records_per_page + 5;
$total_num_page = $last_page_num = ceil($total_num_user/$records_per_page);

html_header();

echo "<CENTER><H3>$total_num_user users found. Displaying the page
                  $page_num out of $last_page_num.</H3></CENTER>\n";

It's time to begin constructing the SQL query that will pull the data from your database and organize it in the manner you want. The way in which this query is constructed really depends on which link the user clicks. How do you dynamically build a SQL query?

Well, the answer lies in using PHP's special $_GET array. If you send the information for the query in the URL, you can pull the information you need from it, and use it in the query. Remember that on the first visit to the page, there are no values in $_GET, so the first thing to do is initialize all of your values to defaults that you can choose. Set your default order by to be the userid field, the default sort order to be ascending, and the default page number to be 1 (Recall that this entails setting $cur_page to 0).

The sort order can be toggled by clicking field names in the header cell of the list table. This feature is implemented by giving the opposite sort order value to the $sort_order variable before echoing it out in HTML anchors. Take another look at Figure 10-1 presented earlier in the chapter and notice that there are links below the table: Next and Bottom (as well as Top and Prev, which are not visible on the opening screen). These links don't want to change the sort order of the list, they merely enable you to navigate the table, so give them their own sort order variable called $org_sort_order:

if (empty ($_GET['order_by'])){
   $_GET['order_by'] = 'userid';
}
$order_by = $_GET['order_by'];

if (empty($GET['sort_order']))
{
   $_GET['sort_order'] = 'ASC';
   $sort_order = 'ASC';
}

if ($_GET['sort_order'] == 'ASC') {
   $sort_order = 'DESC';
   $org_sort_order = 'ASC';
}
else {
   $sort_order = 'ASC';
   $org_sort_order  = 'DESC';
}

Build a LIMIT clause in $limit_str using variables $cur_page, $records_per_page, and $record to calculate start and end pointers.

$limit_str = "LIMIT ". $cur_page * $records_per_page . ", $record";

The finalized query is constructed by passing the values you've captured from the $_GET array, and the $limit_str variable that you just created:

   $query = "SELECT usernumber, userid, username FROM $user_tablename ORDER BY
                                  $_GET[order_by] $_GET[sort__order]
                                  $limit_str";
   $result = mysql_query($query);
   if(!$result){
   error_message(sql_error());
   }
?>

Now create a table to hold the list:

<DIV ALIGN="CENTER">
<TABLE BORDER="1" WIDTH="90%" CELLPADDING="2">
<TR>
     <TH WIDTH="25%" NOWRAP>
        <A HREF="<?php echo "$PHP_SELF?action=list_records&
                                  sort_order=$sort_order&
                                  order_by=usernumber"; ?>">
        User Number
        </A>
     </TH>
     <TH WIDTH="25%" NOWRAP>
        <A HREF="<?php echo "$PHP_SELF?action=list_records&
                                  sort_order=$sort_order&
                                  order_by=userid"; ?>">
        User ID
        </A>
     </TH>
     <TH WIDTH="25%" NOWRAP>
        <A HREF="<?php echo "$PHP_SELF?action=list_records&
                                  sort_order=$sort_order&
                                  order_by=username"; ?>" >
           User Name
        </A>
     </TH>
     <TH WIDTH="25%" NOWRAP>Action</TH>
    </TR>
<?php

The links you supply in the table headers are an integral part of this program. Each link refers to the userviewer page ($PHP SELF), and supplies values that will populate the $ GET array, which will then be used in your script once it is run again. Notice that you specify the action as list records; this is important because you'll use a switch statement at the end of the program to determine which function in the userviewer file you will use.

Next, a while loop steps through the result set, constructing each row in the table. Notice, that the last row contains a JavaScript call to the open_window function that's defined in common_db.inc. You've also specified that action be set to view_record, which means that you'll call the view_record() function instead of list_records()—we'll discuss view_record() in a moment. Here's the code:

   while ($query_data = mysql_fetch_array($result)) {
      $usernumber = $query_data["usernumber"];
      $userid = $query_data["userid"];
      $username = $query_data["username"];
      echo "<TR>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">$usernumber</TD>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">$userid</TD>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">$usernam6</TD>\n";
      echo "<TD WIDTH=\"25%\" ALIGN=\"CENTER\">
            <A HREF=\"javascript:open_window('$PHP_SELF?action=view_record&
            userid=$userid');\">View Record</A></TD>\n";
      echo "</TR>\n";
   }
?>
</TABLE>
</DIV>
<?php
   echo "<BR>\n";
   echo "<STRONG><CENTER>";

Finally, you need to build navigation links based on the current page number and the total number of pages. Because the $cur_page variable starts counting from zero, you use another variable, $page_num, to maintain the current page number. If the current page number is greater than 1, you need the Top and Prev links to enable the user to jump to the first page and previous page, respectively:

if($page_num > 1) {
   $prev_page = $cur_page - 1;

   echo "<A HREF=\"$PHP_SELF?action=list_records&
      sort_order=$org__sort_order&order_by=$order_by&next_page=0\"> [Top] </A>";

   echo "<A HREF=\"$PHP_SELF?action=list_records&sort_order=$org_sort_order
      &order_by=$order_by&next_page=$prev_page\">[Prev]</A> ";
}

Likewise, the Next and Bottom links are displayed if the current page number is smaller than the total number of pages:

if($page_num < $total_num_page) {
   $next_page = $cur_page + 1;
   $last_page = $total_num_page - 1;

   echo "<A HREF-\"$PHP_SELF?action=list_records&sort_order=$org_sort_order
     &order_by=$order_by&next_page=$next_page\">[Next]</A> ";

   echo "<A HREF=\"$PHP_SELF?action=list_records&sort_order_$org_sort_order&
     order_by=$order_by&next_page=$last_page\">[Bottom]</A>";
}

echo "</STRONG></CENTER>";

Finish off the list_records() function with a call to the html_footer() function defined in common_db.inc:

html_footer();
}

You can run the example now without the use of the View Record links on the right side of the table. In order for those to work, you need to create the view_record() function.

view_record()

The view_record() function displays detailed information about a given user and his or her access log data.

When clicked, a View Record link opens up a new browser window to display the specified user's information by calling the JavaScript function open_window(), which in turn calls the script with the $action variable set to invoke the view_record() function.

You start by fetching the entire record of the specified user (from the user table), along with the access log records (from the access log table). If no access has been made to any of the Web pages currently set up to be logged, a message to that effect is displayed and the script terminates. You don't have to worry about whether $_GET is set in this function because view_record() is called only from the userviewer page itself—in other words, you can't go straight to the view records page without first clicking a link that will set the $_GET variables for you:

function  view_rocord()
{
   global $default_dbname, $user_tablename, $access_log_tablename;
   $PHP_SELF = $_SERVER['PHP_SELF'];
   $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 = "SELECT * 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["userprofile"];
   $userprofile = $query_data["userprofile"];

You display a table of information pertaining to the selected user:

   html_header();
   echo "<CENTER><H3>
         Record for User No.$usernumber - $userid($username)
         <H3><CENTER>";

?>
<DIV ALIGN="CENTER">
<TABLE BORDER="1" WIDTH="90%" CELLPADDING="2">
   <TR>
      <TH WIDTH="40%">position</TH>
      <TD WIDTH="60%"><?php echo $userposition ?></TD>
   </TR>
   <TR>
      <TH WIDTH="40%">Email</TH>
      <TD WIDTH="60%"><?php echo "<A HREF=\"mailto:$useremail\">$useremail</A>"
      ; ?></TD>
   </TR>
   <TR>
      <TH WIDTH="40%">Profile</TH>
      <TD WIDTH="60%"><?php echo $userprofile ?></TD>
   </TR>
</TABLE>
</DIV>
<?php
   echo "<HR SIZE=\"2\" WIDTH=\"90%\">\n";

Then, display a second table, listing the user's access log records:

   $query = "SELECT page, visitcount, accessdate FROM $access_log_tablename
            WHERE userid = '$userid'";

   $result = mysql_query($query);
   if (!$result){

       error_message(sql_error());
}

The mysql_num_rows () function returns 0 when no record is found in the access_log table containing the specified user's access data:

   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">
<TABLE BORDER="1" WIDTH="90%" CELLPADDING="2">
   <TR>
      <TH WIDTH="40%" NOWRAP>Web Page</TH>
      <TH WIDTH="20%"  NOWRAP>Visit Counts</TH>
      <TH WIDTH="40%" NOWRAP>Last Access Time</TH>
   </TR>
<?php
      while($query_data = mysql_fetch_array($result)) {
         $page = $query_data["page"];
         $visitcount = $query_data["visitcount"];

To get a formatted date string, use PHP's substr() function (you'll meet a MySQL server function in the next chapter that does the trick in one go):

         $accessdate = substr($query_data["accessdate"], 0, 4) . '-' .
         substr($query_data["accessdate"], 4, 2) .'-' .
         substr($query_data["accessdate"], 6, 2) . ' ' .
         substr($query_data["accessdate"], 8, 2) . ':' .
         substr($query_data["accessdate"], 10,  2) . ':' .
         substr($query_data["accessdate"], 12, 2);

         echo "<TR>\n";
         echo "<TD WIDTH"\"40%\">$page</TD>\n";
         echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$visitcount</TD>\n";
         echo "<TD WIDTH=\"40%\" ALIGN=\"CENTER\">$accessdate</TD>)\n";
         echo "</TR>\n";
      }
?>
   </TR>
</TABLE>
</DIV>
<?php
   }
   html_footer();
}

Choosing an Action to Take

The last section of userview.php is the one that's initially run when you execute the script. Depending on the value of the variable $action, it calls one or another of the main functions defined previously. You therefore build dual functionality into one script. Of course, you need a default to be run in the event that it is the first visit to the page, so you check to see whether action has been set, and if it hasn't, you assign it a default value of list_records:

if (empty($_GET['action'])){
   $_GET['action'] = 'list_records';
}
switch($_GET['action']) {
   case "view_record":
      view_record();
   break;
   default:
      list_records();
   break;
}

All done! Fire up your browser and navigate to the userviewer.php page.

Using the User Viewer

Just to give you an example of how the program runs, let's say you want to list the records by the user name and view the user details for Brian Reid. Depending on the data in your sample_db.

Notice the URL in both the windows—the top one tells you that the data has been ordered by the username, and the new window shows you the user details for Brian Reid, accessed by the userid Pads.

Web Coding Tips - Guide For Web Programmers

Search This Blog

Monday, April 25, 2011

What Does A Google Search Say About You?

Monday, April 18, 2011

Ten Common Mistakes in Web Promotion

Monday, April 11, 2011

New FTC Guidelines

Monday, April 4, 2011

Putting It All Together

The common_db.inc File Contents

Global Variables

html_header()

html_footer()

error_message()

The userviewer.php File Contents

list_records()

view_record()

Choosing an Action to Take

Using the User Viewer

Welcome!

About Me

Portfolio

Hire Me

Follow Me

Add Me

Add Me

Jobs

PodCasts

Archive

My Services