Maintaining Connection State

HTTP is stateless.

  • Each HTTP request is unrelated to any other (from the point of web server)

To maintain connection state, there are 3 common ways:

  • Encode state information in the next URL to be used by the client
    • RUL: localhost/web/wage.html?state='tom' for example
    • Not good to send sensitive data
  • Use Cookie
  • Use Session

What is a Cookie?

  • A cookie is often used to identify a user.
  • A cookie is a small file that the server embeds on the user’s computer.
  • Each time the same computer requests a page with a browser, it will send the cookie too.

We can use PHP Cookie to:

  • Create/Retrieve a Cookie
    • setcookie($cookie_name, $value, time()+(60*60*24)); // 1 day
  • Check if a Cookie is set
    • isset($_COOKIE[$cookie_name])
  • Modify a Cookie value
    • use setcookie() again
  • Delete a Cookie
    • use setcookie() with an expiration date
    • setcookie($cookie_name, $value, time()-3600)

Example Code

login.html

1
2
3
4
5
6
7
8
<body>
  <form action = 'login.php' method = 'post'>
  	<p>Username: <input type = 'text' size = '10' name = 'username'></p>
    <p>Password: <input tpye = 'text' size = '10' name = 'password'></p>
    <input type = 'submit' value = 'Submit'>
  </form>
  <p><a href='delete-cookie.php'>Delete Cookie</a></p>
</body>

login.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<body>
  <?php
  	$username=$_POST["username"];
		$password=$_POST["password"];
		if(isset($_COOKIE["username"])){
      if($username==$_COOKIE["username"] and $password==$_COOKIE["password"]){
        echo "Welcome!";
      }
      else
      {
        echo "Invalid username or password";
      }
    }
		else
    {
      setcookie("username", $username, time()+ (60*60*24));
      setcookie("password", $password, time()+ (60*60*24));
      echo "You log in for the first time. Welcome!";
    }
  ?>
</body>

delete-cookie.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
  setcookie("username", "Tom", time()-3600);
	setcookie("password", "123", time()-3600);
?>
<html>
  <head>
  	<title>Login</title>
  </head>
	<body>
  	<?php
  		echo "Cookie 'username' is deleted!<br>";
			echo "Cookie 'password' is deleted!";
  	?>
  </body>
</html>

PHP Session

What is a Session

  • A session is a way to store information (in variables) to be used across multiple pages.
  • Unlike a cookie, the information is not stored on the user’s computer.
  • Session variables are set with the PHP global variable $_SESSION.

Using PHP Session, we can:

  • Start a Session
    • session_start()
  • Check if a Session is set
    • isset($_SESSION[$session_name])
  • Get/Set Session variable values
    • $_SESSION["score"] = "100"; //Get/Set session vairables for example
  • Destroy a PHP Session
    • We need to rremove all session variables before destroying the session
    • session_unset() - remove all session variables
    • session_destroy() - destroy the session

Code Example

math-quiz.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<?php
  session_start();
?>
<html>
  <head> <title>Math Quiz</title> </head>
	<body>
  <?php
  		if(!isset($_SESSION['score'])){
        $_SESSION['score'] = 0;
        $_SESSION['count'] = 0;
        echo "<h2 style='color:red'>Welcome to the Math Quiz!</h2>";
      }
			if(isset($_POST['userAnswer'])){
        $_SESSION['count'] = $_SESSION['count'] + 1;
        $userAnswer = $_POST['userAnswer'];
        $correctAnswer = $_SESSION['num1'] + $_SESSION['num2'];
        if($correctAnswer == $userAnswer){
          $_SESSION['score'] = $_SESSIONN['score'] + 1;
          echo "<h2 style='color:red'>This is correct!</h2>";
        }
        else
          echo "<h2 style='color:red'>Sorry! The correct sum of". $_SESSION['num1'] . "and " . $_SESSIONN['num2'] . "is $correctAnswer</h2>";
      }
			echo "<p>CURRENT SCORE -- TOTAL: " .$_SESSION['count'] . ", CORRECT: " . $_SESSION['score']. "</p>";
			echo "<h2>Can you add these numbers?</h2>";
			echo "<form action = 'math-quiz.php' method ='post'>";
			$_SESSION['num1'] = rand(1,20);
			$_SESSION['num2'] = rand(1,20);
		
			echo "<p>". $_SESSION['num1'] . "+" . $_SESSION['num2'] . "=";
			echo "<input type = 'text' size = '10' name = 'userAnswer'></p>";
			echo "<p><input type='submit' value='Submit'></p></form>";
			echo "<p><a href='math-quit.php'>Ready to quit?</a></p>";
	?>
	</body>
</html>

math-quit.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
  	session_start();
?>

<html>

<head>
  	<title>Math Quiz - RESULT</title>
</head>
<body>
<?php
  echo "<h1>Thanks for Playing!</h1>";
	echo "<h2>YOU SCORED " . $_SESSION['score'] . "OUT OF " . $_SESSION['count'] . "</h2>";
	session_destroy();
	echo "<p><a href = 'math-quiz.php'>Try Again?</a></p>";
?>
</body>
</html>

Connecting to Database

A relational database allows us to store data in one or more related tables.

A Relational Database Management System (RDBMS) provides management tools , e.g.,creating, removing, modifying, searching databases, tables, records, …

  • Unlike text files, databases and tables in an RDBMS are not directly accessible.
  • Requests must be submitted using the Structured Query Language (SQL) that the RDBMS provides.
  • The RDBMS processes each request (called a query) and returns a result.

MySQL

MySQL is a freeware RDBMS and query language, widely used for Web-based applications.

  • A version of MySQL is installed with your Web server XAMPP.
  • To start MySQL server: just start both Apache and MySQL in the XAMPP Control Panel.

3 Ways to Work with MySQL

  • From CLI (Command Line)
    • Click “Shell” in the XAMPP Control Panel
  • Through a GUI (graphical interface) which is phpMyAdmin
  • From a custom application developed in a programming language such as PHP.
    • use PHP functions designed to submit and process MySQL queries.

To Create a Database

mysql-setup.html

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<!DOCTYPE html>
<html>
<head>
	<title>MySQL Setup</title>
</head>

<body>
	<h1>MySQL Setup</h1>
	<p>This application will set up your MySql server.</p>
	<form action = "mysql-setup.php" method = "post" >

		<p>Use the drop-down list to select whether you are using the xampp or the MAMP Web Server:
		<select name = "server">
		<option value = "xampp">xampp</option>
		<option value = "mamp">MAMP</option>
		</select>
		<p><strong>Advanced Users Only (Windows or Macintosh or Linux)</strong>.If you have already set your own root password for MySql, please type the password here, otherwise leave this field empty. Root Password: <input type = "password" size = "12" name = "rootPW"></p>

		<p>
		<input type = "submit" value = "Submit">
		</p>
	</form>

</body>
</html>

mysql-setup.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
<?php
$server = "localhost";
$user = "root";
if (!empty($_POST['rootPW']))
	$pw = $_POST['rootPW'];
elseif ($_POST['server'] =="mamp")
	$pw = "root";
else 
	$pw = ""; // by default xammp root user has no password

$db = "test";

$connect=mysqli_connect($server, $user, $pw, $db);

if(!$connect) {
	die("ERROR: Cannot connect to database $db on server $server 
	using user name $user (".mysqli_connect_errno().
	", ".mysqli_connect_error().")");
}

$createAccount="GRANT ALL PRIVILEGES ON test.* TO 'wbip'@'localhost' IDENTIFIED BY 'wbip123' WITH GRANT OPTION";

// need this at start of the create table scripts? SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

$dropPersonnelTable = "DROP TABLE IF EXISTS personnel";

$createPersonnelTable = "CREATE TABLE personnel (
  empID int NOT NULL,
  firstName varchar(64) NOT NULL,
  lastName varchar(64) NOT NULL,
  jobTitle varchar(64) NOT NULL,
  hourlyWage float NOT NULL,
  PRIMARY KEY (empID),
  UNIQUE KEY empID (empID)
 ) ENGINE='MyISAM'  DEFAULT CHARSET='latin1'";

$addPersonnelRecords ="REPLACE INTO personnel (empID, firstName, lastName, jobTitle, hourlyWage) VALUES
(12345, 'Chris', 'Smith', 'Sales', 12.15),
(12347, 'Mary', 'Peters', 'Sales', 12.55),
(12348, 'Mike', 'Jones', 'Manager', 24.15),
(12353, 'Anne', 'Humphries', 'Accountant', 25.45),
(12356, 'Ann', 'Jones', 'Sales',13.75),
(12357, 'John', 'Jackson', 'Reception', 8.75),
(12358, 'John', 'King', 'Cleaner', 7.75),
(12360, 'Ken', 'Stewart', 'Accountant', 28.55),
(12361, 'Joan', 'Smith', 'Cleaner', 8.25),
(12363, 'Jesse', 'Andrews', 'Sales', 10.75);";

$dropTimesheetTable = "DROP TABLE IF EXISTS timesheet";

$createTimesheetTable = "CREATE TABLE timesheet (
  empID int NOT NULL,
  hoursWorked int NOT NULL,
  PRIMARY KEY (empID),
  UNIQUE KEY empID (empID)
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1";

$addTimesheetRecords ="REPLACE INTO timesheet (empID, hoursWorked) VALUES
(12345, 30),
(12347, 35),
(12348, 40),
(12353, 35),
(12356, 20),
(12357, 40),
(12358, 32),
(12360, 20),
(12361, 32),
(12363, 35);";

$result = mysqli_query($connect, $createAccount);

if (!$result) 
{
	die("Could not successfully run query ($createAccount) from $db: " .	
		mysqli_error($connect) );
}
else{
	$result = mysqli_query($connect, $dropPersonnelTable);
	if (!$result){
		die("Could not successfully run query ($dropPersonnelTable) from $db: " . mysqli_error($connect) );
	}else  {
		$result = mysqli_query($connect, $createPersonnelTable);
		if (!$result) {
			die("Could not successfully run query ($createPersonnelTable) from $db: " .mysqli_error($connect) );
		}
		else{
			$result = mysqli_query($connect, $addPersonnelRecords);
		
			if (!$result) {
				die("Could not successfully run query ($addPersonnelRecords) from $db: " .mysqli_error($connect) );
			}else {
				$result = mysqli_query($connect, $dropTimesheetTable);
				if (!$result) {
					die("Could not successfully run query ($dropPersonnelTable) from $db: " .mysqli_error($connect) );
				}else {
					$result = mysqli_query($connect, $createTimesheetTable);
					if (!$result) {
						die("Could not successfully run query ($createTimesheetTable) from $db: " .mysqli_error($connect) );
					}else {
						$result = mysqli_query($connect, $addTimesheetRecords);
						if (!$result) {
							die("Could not successfully run query ($addTimesheetRecords) from $db: " .mysqli_error($connect) );
						}else {
							print("<html><head><title>MySQL Setup</title></head>
							<body><h1>MySQL Setup: SUCCESS!</h1><p>Created MySQL user <strong>wbip</strong> with 
							password <strong>wbip123</strong>, with all privileges on the 
							<strong>test</strong> database.</p><p>Created tables <strong>personnel</strong> 
							and <strong>timesheet</strong> in the 
							<strong>test</strong> database.</p>
							</body></html>");
						}
					}
				}
			}
		}
	}
}

mysqli_close($connect);   // close the connection
 
?>


Working with PHP and MySQL

In order to work with MySQL we must:

  • Open a connection to a MySQL server
    • mysqli_connect($URL, $userID, $userPW, $dbName);
  • Connect to a MySQL database
    • $connect = mysqli_connect($URL, $userID, $userPW, $dbName);
  • Submit queries as needed and receive and handle the results of these queries
    • $result = mysqli_query($connect, $userQuery) will receive the result of the query
    • mysqli_fetch_assoc($result) continue to extract records from the result set until no more records are found
  • Close the connection
    • mysqli_close()

Open/Close a connection

We use PHP function mysqli_connect() to open a connection to a MySQL server.

4 Arguments:

  • The URL of the MySQL RDBMS
  • A user ID that has been registered with the system
  • The user password
  • The name of the specific database we wish to work with

Example:

mysql-connect.php

1
2
3
4
5
6
<?php
  $server = "localhost"; 
  $user = "wbip"; 
  $pw = "wbip123"; 
  $db = "test"; 
?>

yourfile.php

1
2
3
4
5
6
7
8
9
10
11
12
<?php
  include "mysql-connect.php";
  $connect = mysqli_connect($server, $user, $pw, $db);
	
	if(!$connect){
    die("Connection failed: " . mysqli_connect_error());
  }

	//Some operations

	mysqli_close($connect); //close the connection
?>
  • mysqli_connect() returns a bool value to let the programmer to see wthether it is connected.
  • die() causes the application to terminate with any error message that you provide.
  • mysqli_connect_error() returns the standard MySQL error message.
  • mysqli_close() closes a connection.

Submit a query

1
2
3
4
5
6
7
8
9
10
11
12
13
//Example: 
$userQuery = "SELECT * FROM personnel"; // MySQL
$result = mysqli_query($connect, $userQuery);

if (!$result) { 
  die("Could not successfully run query." . mysqli_error($connect) );
} 

if (mysqli_num_rows($result) == 0) { 
  print("No records were found with query $userQuery"); 
}else { 
  // process the result set 
}
  • $result = mysqli_query($connect, $userQuery)
    • first argument references the database connection
    • second argument is the query to be submitted
    • $result will receive the result of the query
  • If there is an error and the query cannot be processed (e.g., connection error, MySQL syntax error), then mysqli_query() returns false.
  • If successful, mysqli_query() returns a result set of the 0 or more records that satisfy the query.
  • Before processing the result set, we need to:
    • Test $result to see if there was an error.
    • Use the mysqli_num_rows() function to test if the query found NO records.

Processing the Result Set

1
2
3
4
5
6
$userQuery = "SELECT firstName, lastName FROM personnel"; 
$result = mysqli_query($connect, $userQuery); 
print("<h1>LIST OF EMPLOYEES</h1>"); 
while ( $row = mysqli_fetch_assoc($result) ){ 
  print ("<p>" . $row['firstName'] . " " 	.$row['lastName'] . "</p>"); 
}

Use mysqli_fetch_assoc() function continues to extract records from the result set until no more records are found (then it returns false).

  • Each extracted record is an associative array.

If there is more than a single record, use while ( $row = mysqli_fetch_assoc($result) ) to extact records.

  • Each time the loop repeats, $row is assigned the next record in the result set as an associative array.
  • $row['key'] is your record value. E.g. $row['firstName'] contain the first name of that row.

Example 1: Display the results as an HTML table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
$server = "localhost";
$user = "wbip";
$pw = "wbip123"; 
$db = "test";
$connect = mysqli_connect($server, $user, $pw, $db);
if(!$connect) {
	die("Connection failed: " . mysqli_connect_error());
}
$userQuery = "SELECT * FROM personnel";
$result = mysqli_query($connect, $userQuery);
if (!$result) {
	die("Could not successfully run query.");
}

if (mysqli_num_rows($result) == 0) {
	print "No records were found with query $userQuery";
}else { 
 	print "<h3>LIST OF EMPLOYEES</h3>";
	print "<table border = '1'>";
	print "<tr><th>Firstname</th><th>Lastname</th></tr>";
 	while ( $row = mysqli_fetch_assoc($result) ){
		print "<tr><td>".$row['firstName']."</td><td>".$row['lastName'] . "</td></tr>";
	}
	print "</table>";
}
mysqli_close($connect);

Example 2: Use input from an HTML Form to construct a query

1
2
3
4
$searchName = $_POST['searchName’];
$userQuery = "SELECT * FROM personnel 
		WHERE lastName='searchName' ";
$result = mysqli_query($connect, $userQuery);

Example 3: Perform aggregations on MySQL queries

  • MySQL provides a number of aggregation functions, for example COUNT, SUM, AVG, MIN, MAX,
  • To find the average hourly wage of all employees:
    • $userQuery = "SELECT AVG(hourlyWage) FROM personnel";
  • The average is stored in the associative array with the key name AVG(hourlyWage)
1
2
3
4
$userQuery = "SELECT AVG(hourlyWage) FROM personnel";
$result = mysqli_query($connect, $userQuery);
$row = mysqli_fetch_assoc($result);
print ("<p>Average hourly wage:$".$row['AVG(hourlyWage)']."</p>");

Example 4: Perform JOIN operations on multiple tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$userQuery= "SELECT firstName, lastName, hourlyWage, hoursWorked 
		FROM personnel, timesheet 
		WHERE personnel.empID = timesheet.empID";

$result = mysqli_query($connect, $userQuery);
if (!$result) {
	die("Could not successfully run query.");
}
if (mysqli_num_rows($result) == 0) {
	print "No records were found with query $userQuery";
}else { 
	print "<h3>PAY CHECKS</h3>";
	print "<table border='1'>";
	print "<tr><th>Firstname</th><th>Lastname</th><th>WeeklyWage</th></tr>";
	while( $row = mysqli_fetch_assoc($result) ){
		print "<tr><td>". $row['firstName']. "</td><td>" . $row['lastName'] . "</td><td>" . 
		$row['hourlyWage']*$row['hoursWorked'] . "</td></tr>";		
	}
	print "</table>";				
}