Using Click Interceptions with a Database-Driven Application - Fetching user-related data with MySQL
(Page 2 of 4 )
As I stated in the introduction, my intention here is merely to demonstrate how to use click interceptions to improve the behavior of a simple MySQL-driven application, which will display, on the browser, basic data about some fictional users, including their first and last names, email addresses, comments, and so forth.
Based on this hypothetical scenario, I'm going to create a web page from scratch that shows the full names of these users, along with a group of links that display more detailed information about each of them.
Having explained that, here's how the code for this brand new web page looks:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Fetching user data with MySQL</title>
<style type="text/css">
body{
padding: 0;
margin: 0;
background: #fff;
}
h1{
font: bold 18pt Arial, Helvetica, sans-serif;
color: #000;
}
h2{
font: bold 16pt Arial, Helvetica, sans-serif;
color: #000;
}
a:link,a:visited{
font: normal 10pt Arial, Helvetica, sans-serif;
color: #00f;
}
a:hover{
color: #f90;
}
p{
font: normal 10pt Arial, Helvetica, sans-serif;
color: #000;
}
#linkcontainer{
width: 300px;
padding: 5px;
background: #ffc;
}
#userviewer{
width: 300px;
}
</style>
</head>
<body>
<h1>Fetching user data with MySQL</h1>
<div id="linkcontainer">
<ul>
<li><a href="viewuserdetails.php?id=1" title="View details about Alejandro Gervasio...">View details about Alejandro Gervasio...</a></li>
<li><a href="viewuserdetails.php?id=2" title="View details about John Doe...">View details about John Doe...</a></li>
<li><a href="viewuserdetails.php?id=3" title="View details about Susan Norton...">View details about Susan Norton...</a></li>
<li><a href="viewuserdetails.php?id=4" title="View details about Marian Wilson...">View details about Marian Wilson...</a></li>
</ul>
</div>
<div id="userviewer"></div>
</body>
</html>
So far, nothing unexpected, right? As you can see, the above (X)HTML file shows a list with the full names of these fictional users, and at the same time, it provides several links to a "details" web page, which obviously will show additional information about each user in particular.
The visual appearance of this recently-created web page is as follows:
You should notice that each detail web page is generated dynamically with a PHP file called "viewuserdetails.php" to display the data associated with a specific user. Obviously we need to see the corresponding definition of this file too, so here it is:
<?php
// include classes
class MySQL{
private $host;
private $user;
private $password;
private $database;
private $conId;
// constructor
function __construct($options=array()){
if(!is_array($options)){
throw new Exception('Connection options must be an array');
}
foreach($options as $option=>$value){
if(empty($option)){
throw new Exception('Connection parameter cannot be empty');
}
$this->{$option}=$value;
}
$this->connectDb();
}
// private 'connectDb()' method
private function connectDb(){
if(!$this->conId=mysql_connect($this->host,$this->user,$this->password)){
throw new Exception('Error connecting to MySQL');
}
if(!mysql_select_db($this->database,$this->conId)){
throw new Exception('Error selecting database');
}
}
// public 'query()' method
public function query($sql){
if(!$result=mysql_query($sql)){
throw new Exception('Error running query '.$sql.' '.mysql_error());
}
return new Result($this,$result);
}
}
class Result{
private $mysql;
private $result;
// constructor
public function __construct($mysql,$result){
$this->mysql=$mysql;
$this->result=$result;
}
// public 'fetchRow()' method
public function fetchRow(){
return mysql_fetch_array($this->result,MYSQL_ASSOC);
}
// public 'count()' method
public function countRows(){
if(!$rows=mysql_num_rows($this->result)){
throw new Exception('Error counting rows');
}
return $rows;
}
// public 'get_insertId()' method
public function getInsertId(){
if(!$insId=mysql_insert_id($this->mysql->conId)){
throw new Exception('Error getting insert ID');
}
return $insId;
}
// public 'seek()' method
public function seekRow($row=0){
if(!is_int($row)&&$row<0){
throw new Exception('Invalid row parameter');
}
if(!$row=mysql_data_seek($this->mysql->conId,$row)){
throw new Exception('Error seeking row');
}
return $row;
}
// public 'getAffectedRows()' method
public function getAffectedRows(){
if(!$rows=mysql_affected_rows($this->mysql->conId)){
throw new Exception('Error counting affected rows');
}
return $rows;
}
}
try{
// connect to MySQL
$db=new MySQL(array('host'=>'host','user'=>'user','password'=>'pass','database'=>'database'));
$id=!$_GET['id']?1:$_GET['id'];
// run query
$result=$db->query("SELECT firstname,lastname,email,comments FROM users WHERE id='$id'");
// display user information
$row=$result->fetchRow();
if($result->countRows()==1){
echo '<h2>User Information</h2>';
echo '<p><strong>First Name: </strong>'.$row['firstname'].'</p>';
echo '<p><strong>Last Name: </strong>'.$row['lastname'].'</p>';
echo '<p><strong>Email: </strong>'.$row['email'].'</p>';
echo '<p><strong>Comments: </strong>'.$row['comments'].'</p>';
}
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>
If you're not familiar with using classes in PHP 5, don't feel intimidated by the above PHP file. All it does is connect to MySQL, then extract a few simple user-related rows from a sample database table, and finally print this data on screen.
Of course, you will understand all of these operations better if you look at the following screen capture, which depicts the visual appearance of this user detail web page:
Well, at this point I showed you how to build a simple MySQL-driven application, which is comprised essentially of two source files. As you saw, the first one is tasked with displaying a list of all the users stored in a sample database table, while the second one is responsible for showing more detailed information about each of them.
Naturally, this user detail page will be shown in a different window, since this is its default behavior, right? However, in this case it's possible to use a click interception to display the pertinent user details on the same web page. That would be a really good thing to do, because this database-driven application can remain functional even if scripting has been disabled on the browser.
In the next section I'm going to teach you how to utilize the click interception approach to visualize the user detail web pages that you saw earlier in the same web document, so jump ahead and read the next few lines!
Next: Visualizing multiple user detail pages in the same web document >>
More JavaScript Articles
More By Alejandro Gervasio
