Web Design Wikiclass | Lecture / Case study: the functions of database, server-side scripts, and client-side scripts

A Web Design Lecture by Steven Choy

Overview: In this lecture, we will go through all the necessary steps in building a website which uses MySQL (database) to store information, PHP (server-side script) to retrieve and update information in database, JavaScript (client-side script) to asynchronously send request to the PHP program. You will understand more about the functions of database, server-side scripts, and client-side scripts in building a website. You will also be introduced what is AJAX.

Introduction: the end product

Let have a look and test on the final product we are going to build.
We will do it step by step.
In each stp, there are something for you to learn about the various components in building an interactive website.

(The final website we are going to build)

Step 1: HTML for contents

Make a HTML as follows:

step1.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

<head>
<title>ITS234F 2009 Assignment One Submission</title>
</head>

<body>
<h1>ITS234F 2009 Web Design Assignment One</h1>
<p>The following are web design works submitted by your fellows. Please show your support and appreciation.</p>
<ol>
<li><a href="/its234f2009/a1/s556676x">Chan Pui Cheung</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559066x">Chow Man Wai</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559098x">Lee Man Yi</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559099x">Lee Tat Ming</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559133x">Lee Cheuk Kin</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559136x">Po Chi Kai</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559273x">Ng Pui Yan</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559330x">Wong Chok Pang</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559561x">Wong Yiu Wai</a> (<a href="#">Like</a>: 1)</li>
<li><a href="/its234f2009/a1/s559916x">Yu Suet Yee</a> (<a href="#">Like</a>: 1)</li>
</ol>
<p>Please let Steven konw if there are any problems.</p>
</body>
</html>

[Get Code]

(The website screen shot after Step 1)

Step 2: CSS for presentation

Make the following CSS and link it to the HTML file

step2.css
ol {
list-style:none;
line-height:200%}

li {
background:url(note.png)centerleftno-repeat;
padding:0 0 0 20px}
.counter{
background:url(heart.png)centerleftno-repeat;
padding:0 0 0 20px;
font-weight:bold;
margin-left:10px;}
.counter a {
cursor:pointer;}
.counter a.image{
background:url(add.png)centerrightno-repeat;
padding:0 20px 0 0;}

[Get Code]

step2.html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">
 
<head>
<title>ITS234F 2009 Assignment One Submission</title>
<link href="step2.css" type="text/css" rel="stylesheet" media="screen"/>
</head>
 
<body>
<h1>ITS234F 2009 Web Design Assignment One</h1>
<p>The following are web design works submitted by your fellows.
  Please show your support and appreciation.</p>
<ol>
<li><a href="/its234f2009/a1/s556676x">Chan Pui Cheung</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559066x">Chow Man Wai</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559098x">Lee Man Yi</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559099x">Lee Tat Ming</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559133x">Lee Cheuk Kin</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559136x">Po Chi Kai</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559273x">Ng Pui Yan</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559330x">Wong Chok Pang</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559561x">Wong Yiu Wai</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
<li><a href="/its234f2009/a1/s559916x">Yu Suet Yee</a>
  <span class="counter"><a href="#" class="image">0</a></span></li>
</ol>
<p>Please let Steven konw if there are any problems.</p>
</body>
</html>

[Get Code]

(The website screen shot after Step 2)

Step 3: MySQL for database

Create a database (with phpMyAdmin UI or other way you like)

      CREATE DATABASE `its234f` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Create a table (with phpMyAdmin UI or other way you like)

      CREATE TABLE `its234f`.`liked` (
        `ID` varchar(20) NOT NULL,
        `name` varchar(50) default NULL,
        `link` varchar(256) default NULL,
        `likedCount` int(11) NOT NULL,
        PRIMARY KEY  (`ID`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Populate the table with data

(You can import data from an external source to MySQL)

You can import the data with a CSV file:

      s556676x;Chan Pui Cheung;/its234f2009/a1/s556676x;0
      s559066x;Chow Man Wai;/its234f2009/a1/s559066x;0
      s559098x;Lee Man Yi;/its234f2009/a1/s559098x;0
      s559099x;Lee Tat Ming;/its234f2009/a1/s559099x;0
      s559133x;Lee Cheuk Kin;/its234f2009/a1/s559133x;0
      s559136x;Po Chi Kai;/its234f2009/a1/s559136x;0
      s559273x;Ng Pui Yan;/its234f2009/a1/s559273x;0
      s559330x;Wong Chok Pang;/its234f2009/a1/s559330x;0
      s559561x;Wong Yiu Wai;/its234f2009/a1/s559561x;0
      s559916x;Yu Suet Yee;/its234f2009/a1/s559916x;0

You can input the data with SQL statement:

      INSERT INTO `liked` (`ID`, `name`, `link`, `likedCount`) VALUES
      ('s556676x', 'Chan Pui Cheung', '/its234f2009/a1/s556676x', 0),
      ('s559066x', 'Chow Man Wai', '/its234f2009/a1/s559066x', 0),
      ('s559098x', 'Lee Man Yi', '/its234f2009/a1/s559098x', 0),
      ('s559099x', 'Lee Tat Ming', '/its234f2009/a1/s559099x', 0),
      ('s559133x', 'Lee Cheuk Kin', '/its234f2009/a1/s559133x', 0),
      ('s559136x', 'Po Chi Kai', '/its234f2009/a1/s559136x', 0),
      ('s559273x', 'Ng Pui Yan', '/its234f2009/a1/s559273x', 0),
      ('s559330x', 'Wong Chok Pang', '/its234f2009/a1/s559330x', 0),
      ('s559561x', 'Wong Yiu Wai', '/its234f2009/a1/s559561x', 0),
      ('s559916x', 'Yu Suet Yee', '/its234f2009/a1/s559916x', 0);

Step 4: PHP for interaction with MySQL

Step 4.a: HTML and PHP script to retrieve data from database

db.php

      <?php
        $dbhost = 'localhost'; //usually localhost
        $dbuser = 'root'; //your username assigned to your database
        $dbpass = 'yourdbpass'; //your password assigned to your user & database
        $dbname = 'yourdbname'; //your database name
      ?>

step4a.php

      <?php
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        $q = 'SELECT * FROM liked';
        $res = mysql_query($q);
        mysql_close($conn);
        $i=0;
        while($row = mysql_fetch_array($res)) {
          $item[$i]['link'] = $row['link'];
          $item[$i]['name'] = $row['name'];
          $item[$i]['likedCount'] = $row['likedCount'];
          $item[$i]['ID'] = $row['ID'];
          $i = $i + 1;
        }
      ?>

      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

      <head>
      <title>ITS234F 2009 Assignment One Submission</title>
      <link href="step2.css" type="text/css" rel="stylesheet" media="screen"/>
      </head>

      <body>
      <h1>ITS234F 2009 Web Design Assignment One</h1>
      <p>The following are web design works submitted by your fellows.
        Please show your support and appreciation.</p>
      <ol>
      <?php foreach ($item as $output) { ?>
      <li>
      <a href="<?php echo $output['link']; ?>"><?php echo $output['name']; ?></a>
      <span class="counter">
      <a href="#" class="image"><?php echo $output['likedCount']; ?></a>
      </span>
      </li>
      <?php } ?>
      </ol>
      <p>Please let Steven konw if there are any problems.</p>
      </body>
      </html>

Step 4.b: PHP script to update data to database

step4b.php

      <?php
      $id = $_GET['id'];
      include ('db.php');
      $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
      mysql_select_db($dbname);
      if($id != '') {
        $update = "update liked set likedCount = likedCount + 1 WHERE ID = '".$id."'";
        $result = mysql_query($update); 
        $sql= "select * from liked WHERE ID= '".$id."' LIMIT 0, 1";
        $result = mysql_query($sql);
        $rs= mysql_fetch_array($result);
        echo $rs[likedCount]; 
      }
      ?>

You can test the script with the HTTP GET method: step4b.php?id=sxxxxx (i.e. send the request to server by input the URL in your browser).

Step 4.c: Call the script with a HTTP GET method

step4c.php

      <?php
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        $q = 'SELECT * FROM liked';
        $res = mysql_query($q);
        mysql_close($conn);
        $i=0;
        while($row = mysql_fetch_array($res)) {
          $item[$i]['link'] = $row['link'];
          $item[$i]['name'] = $row['name'];
          $item[$i]['likedCount'] = $row['likedCount'];
          $item[$i]['ID'] = $row['ID'];
          $i = $i + 1;
        }
      ?>
      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

      <head>
      <title>ITS234F 2009 Assignment One Submission</title>
      <link href="step2.css" type="text/css" rel="stylesheet" media="screen"/>
      </head>

      <body>
      <h1>ITS234F 2009 Web Design Assignment One</h1>
      <p>The following are web design works submitted by your fellows.
        Please show your support and appreciation.</p>
      <ol>
      <?php foreach ($item as $output) { ?>
      <li>
      <a href="<?php echo $output['link']; ?>"><?php echo $output['name']; ?></a>
      <span class="counter">
      <a href="step4b.php?id=<?php echo $output['ID'] ?>" class="image">
      <?php echo $output['likedCount']; ?>
      </a>
      </span>
      </li>
      <?php } ?>
      </ol>
      <p>Please let Steven konw if there are any problems.</p>
      </body>
      </html>

Step 4.d: Use Cookie to limit the user interaction

step4d.php

      <?php
        $id = $_GET['id'];
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        if($id != '') {
          if (!isset($_COOKIE['liked-'.$id])) {
            $update = "update liked set likedCount = likedCount + 1 WHERE ID = '".$id."'";
            $result = mysql_query($update); 
            $sql= "select * from liked WHERE ID= '".$id."' LIMIT 0, 1";
            $result = mysql_query($sql);
            $rs= mysql_fetch_array($result);
            setcookie('liked-'.$id, time(), time()+3600*24*365, '/');
            echo $rs[likedCount]; 
          } else {
            echo 'You have voted';
          }
        }
      ?>

Step 4.3: Use HTTP POST method to further limit the user interaction

step4e.php

      <?php
        $id = $_POST['id'];
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        if($id != '') {
          if (!isset($_COOKIE['liked-'.$id])) {
            $update = "update liked set likedCount = likedCount + 1 WHERE ID = '".$id."'";
            $result = mysql_query($update); 
            $sql= "select * from liked WHERE ID= '".$id."' LIMIT 0, 1";
            $result = mysql_query($sql);
            $rs= mysql_fetch_array($result);
            setcookie('liked-'.$id, time(), time()+3600*24*365, '/');
            echo $rs[likedCount]; 
          } else {
            echo 'You have voted';
          }
        }
      ?>

Step 5: AJAX to make asynchronous request to server

step5a.php

      <?php
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        $q = 'SELECT * FROM liked';
        $res = mysql_query($q);
        mysql_close($conn);
        $i=0;
        while($row = mysql_fetch_array($res)) {
          $item[$i]['link'] = $row['link'];
          $item[$i]['name'] = $row['name'];
          $item[$i]['likedCount'] = $row['likedCount'];
          $item[$i]['ID'] = $row['ID'];
          $i = $i + 1;
        }
      ?>
      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

      <head>
      <title>ITS234F 2009 Assignment One Submission</title>
      <link href="step2.css" type="text/css" rel="stylesheet" media="screen"/>
      </head>

      <body>
      <h1>ITS234F 2009 Web Design Assignment One</h1>
      <p>The following are web design works submitted by your fellows.
        Please show your support and appreciation.</p>
      <ol>
      <?php foreach ($item as $output) { ?>
      <li>
      <a href="<?php echo $output['link']; ?>"><?php echo $output['name']; ?></a>
      <span class="counter">
      <a onclick="likeThis('<?php echo $output['ID']; ?>');" class="image">
      <?php echo $output['likedCount']; ?>
      </a>
      </span>
      </li>
      <?php } ?>
      </ol>
      <p>Please let Steven konw if there are any problems.</p>

      <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
      <script type="text/javascript">
      function likeThis(postId) {
        if (postId != '') {
          jQuery('#iLikeThis-postId .counter').text('...');
          jQuery.post("step4e.php",
            { id: postId },
            function(data){
              jQuery('#iLikeThis-postId .counter').text(data);
            });
        }
      }
      </script>

      </body>
      </html>

step5b.css
Modify the CSS in Step 2 as follows:

step5b.css
ol {
list-style:none;
line-height:200%}

li {
background:url(note.png)centerleftno-repeat;
padding:0 0 0 20px}
.iLikeThis{
display:inline;}
.iLikeThis.counter{
background:url(heart.png)centerleftno-repeat;
padding:0 0 0 20px;
font-weight:bold;
margin-left:10px;}
.iLikeThis.counter a {
cursor:pointer;}
.iLikeThis.counter a.image{
background:url(add.png)centerrightno-repeat;
padding:0 20px 0 0;}

[Get Code]

step5c.php

      <?php
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        $q = 'SELECT * FROM liked';
        $res = mysql_query($q);
        mysql_close($conn);
        $i=0;
        while($row = mysql_fetch_array($res)) {
          $item[$i]['link'] = $row['link'];
          $item[$i]['name'] = $row['name'];
          $item[$i]['likedCount'] = $row['likedCount'];
          $item[$i]['ID'] = $row['ID'];
          $i = $i + 1;
        }
      ?>
      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

      <head>
      <title>ITS234F 2009 Assignment One Submission</title>
      <link href="step5b.css" type="text/css" rel="stylesheet" media="screen"/>
      </head>

      <body>
      <h1>ITS234F 2009 Web Design Assignment One</h1>
      <p>The following are web design works submitted by your fellows.
        Please show your support and appreciation.</p>
      <ol>
      <?php foreach ($item as $output) { ?>
      <li>
      <a href="<?php echo $output['link']; ?>"><?php echo $output['name']; ?></a>
      <div id="iLikeThis-<?php echo $output['ID']; ?>" class="iLikeThis">
      <span class="counter">
      <a onclick="likeThis('<?php echo $output['ID']; ?>');" class="image">
      <?php echo $output['likedCount']; ?>
      </a>
      </span>
      </div>
      </li>
      <?php } ?>
      </ol>
      <p>Please let Steven konw if there are any problems.</p>

      <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
      <script type="text/javascript">
      function likeThis(postId) {
        if (postId != '') {
          jQuery('#iLikeThis-postId .counter').text('...');
          jQuery.post("step4e.php",
            { id: postId },
            function(data){
              jQuery('#iLikeThis-postId .counter').text(data);
            });
        }
      }
      </script>

      </body>
      </html>

step5d.php

      <?php
        include ('db.php');
        $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to mysql');
        mysql_select_db($dbname);
        $q = 'SELECT * FROM liked';
        $res = mysql_query($q);
        mysql_close($conn);
        $i=0;
        while($row = mysql_fetch_array($res)) {
          $item[$i]['link'] = $row['link'];
          $item[$i]['name'] = $row['name'];
          $item[$i]['likedCount'] = $row['likedCount'];
          $item[$i]['ID'] = $row['ID'];
          $i = $i + 1;
        }
      ?>
      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
      <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en">

      <head>
      <title>ITS234F 2009 Assignment One Submission</title>
      <link href="step5b.css" type="text/css" rel="stylesheet" media="screen"/>
      </head>

      <body>
      <h1>ITS234F 2009 Web Design Assignment One</h1>
      <p>The following are web design works submitted by your fellows.
        Please show your support and appreciation.</p>
      <ol>
      <?php foreach ($item as $output) { ?>
      <li>
      <a href="<?php echo $output['link']; ?>"><?php echo $output['name']; ?></a>
      <div id="iLikeThis-<?php echo $output['ID']; ?>" class="iLikeThis">
      <span class="counter">
      <?php if (!isset($_COOKIE['liked-'.$output['ID']])) { ?>
      <a onclick="likeThis('<?php echo $output['ID']; ?>');" class="image">
      <?php echo $output['likedCount']; ?>
      </a>
      <?php } else { ?>
      <?php echo $output['likedCount']; ?>
      <?php } ?>
      </span>
      </div>

      </li>
      <?php } ?>
      </ol>
      <p>Please let Steven konw if there are any problems.</p>

      <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
      <script type="text/javascript">
      function likeThis(postId) {
        if (postId != '') {
          jQuery('#iLikeThis-postId .counter').text('...');
          jQuery.post("step4e.php",
            { id: postId },
            function(data){
              jQuery('#iLikeThis-postId .counter').text(data);
            });
        }
      }
      </script>

      </body>
      </html>

What is AJAX and how does it work?

AJAX stands for Asynchronous Javascript and XML
Ajax is a way of developing Web applications that combines:
XHTML and CSS standards based presentation
Interaction with the page through the DOM
Data interchange with XML and XSLT
Asynchronous data retrieval with XMLHttpRequest
JavaScript to tie it all together
In the traditional Web application, the interaction between the customer and the server goes like this:
1. Customer accesses Web application
2. Server processes request and sends data to the browser while the customer waits
3. Customer clicks on a link or interacts with the application
4. Server processes request and sends data back to the browser while the customer waits
5. etc....
Rather than waiting for an entire web page to be downloaded (including all of the images, CSS and JavaScript), AJAX just downloads small pieces of information and then use JavaScript to dynamically update the web page.
This makes for a much quicker and generally more user-friendly experience.

(source: http://www.telerik.com/help/aspnet/grid/grdajaxinradgrid.html)

GET and POST Method of HTTP

GET Method

"The Get is one the simplest Http method. Its main job is to ask the server for the resource. If the resource is available then then it will given back to the user on your browser. That resource may be a HTML page, a sound file, a picture file (JPEG) etc. We can say that get method is for getting something from the server. It doesn't mean that you can't send parameters to the server. But the total amount of characters in a GET is really limited. In get method the data we send get appended to the URL so whatever you will send will be seen by other user so can say that it is not even secure."

POST Method

"The Post method is more powerful request. By using Post we can request as well as send some data to the server. We use post method when we have to send a big chunk of data to the server, like when we have to send a long enquiry form then we can send it by using the post method."

(Source: http://www.roseindia.net/servlets/GetAndPostMethodOfHttp.shtml)

Thanks for Reading

If you would rather like to have this lecture note in printed format, please click the print action link in the top right corner.

If you find any problem in this lecture note, please feel free to tell Steven via steven [at] findaway.hk.