msis 501 spring 2011 studentcourse studentlname...

MSIS 501 Spring 2011

1

Web-Based Data-Driven Applications Here is an example of a database.

This is an ERD (entity-relationship diagram). It is a blueprint for creating an actual physical database. To create an actual physical database, we need a database server. The database server manages the data and handles requests for data. Requests for data are called queries. Queries are written in a language called sql. There are many different database servers on the market. Commercial offerings: Oracle, SQL Server, Access Open-Source offerings: MySQL, Postgre We will implement our database using MySQL

Where does the db server fit in? Most applications involving a database are written using a multi-tier architecture. The most common multi-tier architecture is called three-tier client-server.

This picture shows a typical set up for a web-based application. The client is the person who comes to your page using a browser. He interacts with a page that has an HTML form on it. Eventually he presses a submit button. When the user presses submit, the browser sends a request to the web server. That request references a particular script (in our case, a PHP script). In addition to referencing a script, the web page passes the script the parameters it needs to do its job. The script typically will use the passed parameters to formulate a query for that it then passes to the database server. The database server responds to the query by getting the data the user requested and then sending it back to the web server script. The web server script will produce a new page that shows the results. This new page goes back to the client.

Course

PK CourseID

CourseNum

CourseName

FK1 DeptID

Course Section

PK CSID

FK1 CourseID

CSCode

CSBegTime

CSDays

FK2 SemID

FK3 InstID

Book

PK BookISBN

BookTitle

BookEdition

BookAuthor

BookPublisher

has

Department

PK DeptID

DeptNum

DeptName

Instructor

PK InstID

InstFName

InstLName

FK1 DeptID

InstLogin

InstPW

has

has

Student

PK StudentID

StudentFName

StudentLName

StudentLogin

StudentPW

StudentCourse

PK,FK2 StudentID

PK,FK1 CSID

has

has

Semester

PK SemID

SemName

has

has

ClassBook

PK,FK1 CSID

PK,FK2 BookISBN

CBRequired

has

has

Client

(e.g. web browser)Client Layer

Database Server

(e.g. MySQL)

Web Server

(e.g. IIS running PHP)

Script

(e.g. PHP)

Middle Layer

Server Layer


2

MySQL Specifically You can download MySQL from www.mysql.com. When you install it on Windows, it will install as a service, which means it will run whenever you boot up. To launch it, you type mysql –h localhost –u root –p -h stands for host name. -u stands for username. -p stands for password. The password in the lab here is irishsuck This command gets you into interactive mode. It is also possible launch mysql so that it takes a file full of commands: mysql –h localhost –u root –p –t –vvv < nameOfFile.sql SQL SQL, or structured query language, contains just a few keywords: Creating things

create database databaseName

create table tableName … Destroying things

drop database databaseName.

drop table tableName Insert rows into a table

insert into tableName values (list, of, values)

insert into tableName (subset, of, column, names) values (list, of, corresponding, values)

Deleting rows from a table

delete from tableName where criteria Selecting rows from a table

select list, of, columns from tableName1 where criteria Selecting rows from multiple tables

select list, of, columns from tableName1, tableName2, tableNameN where criteria Criteria have the following form: columnName comp value boolop columnName comp value comp can be >, <, =, >=, <= boolop can be and or or


3

Here is the SQL script for creating the database shown on the first page: /* MSIS 501

2010-11-15

Creates database for Book Seller Website

*/

drop database if exists BookSeller;

create database BookSeller;

use BookSeller;

create table Department (

DeptID char(4) not null unique,

DeptNum integer,

DeptName varchar(30),

primary key (DeptID)

);

create table Course (

CourseID integer auto_increment not null unique,

DeptID varchar(5) not null,

CourseNum integer not null,

CourseName varchar(50),

primary key (CourseID),

foreign key (DeptID) references Department (DeptID)

on delete cascade on update cascade

);

create table Semester (

SemID char(1) not null unique,

SemName varchar(10),

primary key (SemID)

);

create table Instructor (

InstID integer auto_increment not null unique,

InstFName varchar(15),

InstLName varchar(25),

DeptID char(4),

InstLogin varchar(20),

InstPW varchar(20),

primary key (InstID),

foreign key (DeptID) references Department (DeptID)


);

create table CourseSection (

CSID integer not null unique,

CourseID integer not null,

CSCode char(1) not null,

CSBegTime Time,

CSDays varchar(5),

SemID char(1),

CSYear integer,

InstID integer,

primary key (CSID),

foreign key (CourseID) references Course (CourseID)

on delete cascade on update cascade,

foreign key (InstID) references Instructor (InstID)

on delete cascade on update cascade,

foreign key (SemID) references Semester (SemID)


);

create table Student (

StuID varchar(9) not null unique,

StuFName varchar(15),

StuLName varchar(20),

StuLogin varchar(20),


4

StuPass varchar(20),

primary key (StuID)

);

create table StudentCourse (

StuID varchar(9) not null,

CSID integer not null,

primary key (StuID, CSID),

foreign key (StuID) references Student (StuID)

on update cascade on delete cascade,

foreign key (CSID) references CourseSection (CSID)

on update cascade on delete cascade

);

create table Book (

BookISBN varchar(10) not null unique,

BookTitle varchar(80),

BookAuthor varchar(50),

BookEdition integer,

BookPublisher varchar(20)

);

create table ClassBook (

CSID integer not null,

BookISBN varchar(10) not null,

CBRequired bool,

primary key (CSID, BookISBN),

foreign key (CSID) references CourseSection (CSID)

on update cascade on delete cascade,

foreign key (BookISBN) references Book (BookISBN)

on update cascade on delete cascade

);

Assuming this file is called DBBuild.sql, you could execute these commands by typing mysql –h localhost –u root –p –t –vvv < DBBuild.sql This simply builds the structure for holding the data. We would then have to populate this structure with data. Once that’s done, we could extract data from it. Examples

1. Create a database called School.

2. Designate School as the current database.

3. Create a table called Major with columns MajorID and MajorName.

4. Create a table called Student with columns StuID, StuLast, StuFirst, StuYear, along with a foreign key to Major.

5. Insert two records into Major, one describing Computer Science and the other describing Mathematics.

6. Insert two records into Student table.

5

7. Write a query to select all students who are in year 3 of their studies.

8. Write a query that returns the last name of each student and his or her major.

A Web Page A web page is composed in HTML. It consists sections enclosed by pairs of codes in angle brackets, which are called tags.

<HTML>

<HEAD>

<TITLE>Title of Page</TITLE>

</HEAD>

<BODY>

<H1>This is a big heading</H1>

<H2>This is not-as-major a heading</H2>

<P>This is a new paragraph</P>

<BR>This is a line break</BR>

<UL>This is the start of an unordered list

<LI>This is a bulleted item</LI>

<LI>This is another bulleted item</LI>

</UL>



<TABLE>

<TR><TH>Column 1 Heading</TH><TH>Column 2 Heading</TH></TR>

<TR><TD>Data Value 1</TD><TD>Data Value 2</TD></TR>

</TABLE>

</BODY>

</HTML>

Enabling the User to Send Data to the Web Server It is possible to put a form on an HTML page. You enclose a form in the FORM tag pair: <FORM method=”methodName” action=”scriptName”>

</FORM>

A form consists of controls. People use controls to specify the data they want to send to the server. One class of controls is called input. Input controls include text fields, password fields, checkboxes, radio buttons, submit buttons, reset buttons, and hidden fields.

“get” or “post” Name of a script on the server that will handle the values the user sends


6

Here’s an example: <FORM action="http://somesite.com/prog/adduser" method="post">

<P>

First name: <INPUT type="text" name="firstname"><BR>

Last name: <INPUT type="text" name="lastname"><BR>

email: <INPUT type="text" name="email"><BR>

<INPUT type="radio" name="sex" value="Male"> Male<BR>

<INPUT type="radio" name="sex" value="Female"> Female<BR>

<INPUT type="submit" value="Send"> <INPUT type="reset">

</P>

</FORM>

There is also a TEXTAREA control that allows you to specify the number of rows and columns. For example, <TEXTAREA name=”Response” rows=”5” cols=”40”>

This is the initial text

in the text area.

</TEXTAREA>

So You Can Code an HTML Form. Now What? Now you have to write the script that will process what the data the user entered on the form. Lots of languages can be used to write such scripts. We shall use PHP for this project. What is PHP? PHP is a server-side scripting language that provides a convenient way of embedding programs in web pages. Such a page is processed by PHP running on the server before content is sent to the client. This means that web content can be generated dynamically. For example, can generate a page that shows the results of running a MySQL query. In fact, PHP literally means “PHP: Hypertext Preprocessor.” How does PHP work? When a client browser sends a request for a PHP page to a web server, PHP executes any script it finds in the page and replaces it with the script’s output. The result is then sent back to the browser. Again, PHP is installed and run on the web server. Most web servers support it, but it is particularly popular for use with Apache Here’s a diagram:


7

Server

Data

a PHP file

Outputs html

Where can you get PHP? To download it, go to www.php.net. Strategy:

Learn the basics of PHP – syntax, data structures, programming structures

Learn how PHP interfaces with databases

A Quick PHP Reference

How do you include

a PHP script in a

web page?

<html>

<head><title>This is the title of the web page</title></head>

<body>

<h1>Big Lizard in my Backyard</h1>

<?php

this is where your php script goes

?>

</body></html>

Outputting text to

a web page – print

<?php

print “hello world<br>”;

?>

Very often, your print statement will include html tags like <br>,

<h1>, <h2>, <p>, <ul>, <ol>, <li>, <table>, <tr>, <td>, etc.

Comments Same as in C++ & Java

// single-line

/* multi-line comment

same as in C++ */

Variables

A variable consists of a name that you can choose preceded by a

dollar sign. The name can consist of letters, numbers, and

underscores.

$a;

$a_long_variable_name

$booyah123

In the output html, all PHP scripts are hidden. They are replaced by the dynamically created output.

http://www.php.net/


8

print and the

value of a

variable

If a variable is embedded in a string that is to be printed, its

value will be output:

$value = 3.14;

print “The value is $value.”; // outputs 3.14

Standard data

types

integer

double

string

boolean

object

array

resource (such as a database0

NULL (uninitialized variable)

You do not have to declare variables in PHP

Variables and

their types

The gettype() returns the type of the variable. settype($variable,

„type‟) sets the type.

<?php

$undecided = 3.14;

print gettype($undecided); //double

settype($undecided, „integer‟);

print $undecided //3

Casting You can typecast a variable to be of a particular type by placing the

name of the data type in parentheses in front of the variable.

$undecided = 3.14;

$temp = (integer) 3.14;

print $temp;

Operators Operators like +, -, *, % are the same as in C++ and Java.

Division, /, doesn‟t care about int / int -> you never lose the

fractional part, unlike with C++ and Java

Compound operators exist in PHP as in C++ and Java

Comparision operators are likewise the same.

Logical operators include ||, &&, and !.

The logical operators can also be expressed using words such as or,

and, and xor

strings As in C++ and Java, a string is a sequence of characters.

A string can be enclosed in either single or double-quotes, as long

as they are used in pairs.

To concatenate two strings, use the . (dot) operator:

$centimeters = 212;

print “the width is “.($centimeters/100).” meters”;


9

Constants You can define a constant like this:

define(“CONSTANT_NAME”, 42);

Selection

statements

if and else look the same as in C++ and Java

switch statements also look the same

Loops while, do, and for loops are the same in PHP as they are in Java.

There is also a for…each loop, as we‟ll see in a moment when we look

at arrays.

Example: write a

php program that

computes the sum

of odd integers

from 1 to 100.

<html><body>

<?php

$total = 0;

for ($i = 1; $i < 100; $i++) {

$total = $total + $i;

}

print "The total is $total.";

?>

</body></html>

Example: write a

php program that

prints numbers 1

through 25 in an

html table.

<html><body>

<?php

//prints numbers 0 through 24 in an html table

print "<table>";

for ($i = 0; $i <= 24; $i++) {

if ($i % 4 == 0) {

print "</tr>";

if ($i != 24) {

print "<tr>"; //start next row

}

}

print "<td>$i</td>";

}

print "</table>";

?>

</body></html>

Error handling A very popular way to handle errors is the die command:

if (!$good) { die(“Something is messed up”); }

die gets you out of the program immediately after printing an error

message.

Sometimes die is preceded by a command and the word “or”. This will

try to do the command and, if it fails, the program will die. This is

somewhat like try…catch in Java and C++.

Functions Define a function like this:

function function_name($argument1, $argument2) {

//function code here

}


10

For example:

<html><title>FirstFunction.php<body>

<?php

function factorial($num) {

$prod = 1;

for ($i = 2; $i <= $num; $i++) {

$prod = $prod * $i;

}

return $prod;

}

print "Factorial of 5 is " . factorial(5) . ".";

?>

</body></html>

Variable scope A variable declared inside a function is known only inside that

function.

If you want to access a global variable inside a function, reference

that global variable within the function with the $global keyword.

<?php

$aGlobalVariable = 5;

function globExamp() {

global $aGlobalVariable; }

?>

Passing references

to functions

By default, when you pass a value to a function, it is passed by

value. Recall that that means that a copy of the value is made.

It is possible to pass a variable by reference so that any changes

made to the value of the variable remain when you return from the

function.

How? Just put the & symbol in front of the variable.

Example <?php

function addFive(&$num) {

$num = $num + 5;

}

$origNum = 10;

addFive($origNum);

print $origNum;

?>

Arrays An array, of course, is just a list of values.

Here is a single-dimensional array:

<?php

$users = array(“Bret”,”Randy”,”Michael”);

print $users[1];

?>


11

To add a value to the array, just do this:

$users[] = “Ray”;

This will add “Ray” to the list of users.

Alternative, you can identify the index of the slot at which you‟ll

add the value:

$users[3] = “Ray”;

Determining the

length of an array

Use the built-in count function:

$print “Number of elements is “ . count($users) . “.”;

Could use this, for example, to reliably add a value to the end of an

array:

$users[count($users)] = “Ray”;

Associative Arrays Associative arrays enable you to access elements in an array by name

rather than by number.

$character = array (

“name” => “Dee Brown”,

“occupation” => “Point Guard”,

“age” => 22,

“special power” => “human fast-break” );

print $character[„name‟]; // prints the name Dee Brown

Two-dimensional

associative array

Here‟s an example:

<html><title>MultidimensionalArray.php<body>

<?php

$characters = array (

array("name" => "bob", "occupation" => "superhero"),

array("name" => "sally", "occupation" => "programmer"),

array("name" => "mary", "occupation" => "villian"));

print $characters[0]["name"];

?>

</body></html>

foreach loops are

useful for looping

through an array.

Here‟s an example of a foreach with a single-dimensional array:

$users = array(“Bret”,”Michael”,”Randy”);

foreach($users as $person) {

print “$person<br>”;

}

Here‟s an example of a foreach with an associative array:

<html><title>ForEachAssoc.php<body>

<?php

$character = array("name" => "superman", "skill" =>

"faster than a speeding bullet”);


12

foreach ($character as $key=>$val) {

print "$key ----> $val<br>";

}

?>

</body></html>

Here‟s an example of foreach with a multidimensional associative

array:

<html><title>ForEachAssoc.php<body>

<?php

$characters = array(

array("name" => "Superman", "skill" => "fast and strong"),

array("name" => "Aquaman", "skill" => "sonic telepathy"));

foreach ($characters as $arr) {

foreach ($arr as $key=>$val)

{

print "$key ---> $val<br>";

}

print "<br>";

}

?>

</body></html>

Useful functions

for working with

arrays

function array_push takes an array and values you want to push onto

the end of it:

$names = array(“Bret”,”Michael”,”Randy”);

$class = array_push($names,”Keith”,”Kevin”,”RobL”,”RobD”,”Adam”);

function array_slice returns a subset of the values in an array:

$rightSide = array_slice($class,3,4); //starting at location 3,

//return 4 elements

Input and output

files

To open a file for input:

$fp = fopen(“nameOffile”,‟r‟);

To open a file for output:

$fp = fopen(“nameOfFile”,‟w‟); //write the file

$fp = fopen(“nameOfFile”,‟a‟); //append to the file

To write to a file:

fputs($fp,”This is what I want to write.”);

or

fwrite($fp,”This is what I want to write.”);

To read from a file:

$line = fgets($fp, 1024);

//1024 represents the max number of bytes to read

To test for the end of the file while reading:

while (!feof($fp)) { }


13

To close a file, use fclose($fp), where $fp is the file pointer.

File example: read

from one file and

write to another

<html><title>FileIO.php<body>

<?php

$ins = fopen("hi.txt",'r');

$outs = fopen("hi.out",'w');

while (!feof($ins)) {

$line = fgets($ins,1024);

fwrite($outs,$line);

}

fclose($ins);

fclose($outs);

print "output file created. size = ".filesize("hi.out")."<br>";

?>

</body></html>

Using include to

include another

php file within a

php file

The include and include_once functions allow you to paste in code

from another page into your current document.

Here‟s an example. Here‟s a file called ILikePHP.php:

<?php

print "<h2>I like php a lot</h2>"

?>

Here‟s a file that includes it:

<html><head><title>IncludeExample.php</title></head>

<body>

<?php

include("ILikePHP.php");

?>

</body>

</html>

HTML Forms HTML forms are defined within a <form> </form> tag pair.

<form action = “nameOfScript.php” method=”post”>

define form contents here

</form>

The action attribute specifies the name of the script that will

handle the form‟s data. The method indicates which mechanism will be

used to usher the data from the web page to the action script. Two

mechanisms are available:

get, which is suitable for small amounts of data. Data is

appended to the web address of the script using a ? symbol

post, which is suitable for larger amounts of data and for

situations where the supplied information must remain private

and thus shouldn‟t appear appended onto the web address.

Within the <form> tag pair is where you list the components that

comprise the form. Here are some examples:

<input type=”text” name=”username”>

This is a textfield whose content will be referred to by the

variable name “username”


14

<input type=”submit” value=”ok”>

This is a button having the label “ok”

<input type=”hidden” name=”hiddenField” value=”I‟m hidden.”>

This is an invisible field on the web page that contains the

phrase “I‟m hidden”. It‟s value will be referred to by the

variable name “hiddenField”

<textarea name=”lifeStory” rows=”5” cols=”40”>

This is a text area having 5 rows and 40 columns. Its value will

be referred to by the variable “lifeStory”

How to use PHP to

process a form’s

data

You specify the php script you want to use to process the data from a

form in the action attribute.

Your script‟s job is to use the variables that are associated with

each of the form‟s components in the html file. The variables will be

known by the same name as they were given in the html file, except

they will have a $ in front of them.

How do you know what the variables are? Use one of these two globally

defined arrays, both of which return arrays and each of which are

tailored to whether you are using get or post to communicate with the

form:

$HTTP_GET_VARS

$HTTP_POST_VARS

These two arrays are associative arrays, meaning they have a keys and

corresponding values.

Here‟s the html:

<html><head><title>FirstForm.html</title></head>

<body>

<form action="FirstForm.php" method="get">

Enter your name:

<input type="text" name="username" size="20"></input>

<input type="submit" value="ok"></input>

</form>

</body>

Here‟s the php:

<html><head><title>FirstForm.php</title></head>

<body>

<?php

$params = $HTTP_GET_VARS;

print "Welcome " . $params['username'] . ".<br>";

?>

</body>

</html>

Combining HTML and

PHP Code on a

single page

The count( ) function applied to $HTTP_POST_VARS function can be

useful because it can help us write an html form and the php script

that processes it on the same page.

Here is an example:

<?php


15

$params = $HTTP_POST_VARS;

if (count($params)==0)

{

$message = "Welcome. What number am I thinking of?<br>";

}

else if ($params["guess"] != 7)

{

$message = "Wrong!<br>";

}

else

{

$message = "Correct!<br>";

}

?>

<html><head><title>HTML and PHP on the same page</title></head>

<body>

<?php print $message; ?>

<form method = "post" action = "SamePage.php">

Enter a number:

<input type = "text" name = "guess"></input>

<input type = "submit" value = "ok"></input>

</form>

</body>

</html>

PHP Example

One of the key points you will see in the notes is how to write a php program that

processes the data in a form. Basically, the names of the controls on the form become the

names of the variables in your PHP program.

Let‟s try it out.

Write a program that asks the user to supply the radius of a circle and then prints the

circumference and area of it.

The solution consists of two files. One is called Circle.html: <html>

<head>

<title>Compute Area and Circumference of a Circle</title>

</head>

<body>

<form method="get" action="Circle.php">

Enter radius: <input type="text" name="radius" size="5"></input>

<br>

<input type="submit" value="OK"></input>

</form>

</body>

</html>

The other is called Circle.php: <html>

<head><title>Circle Results</title></head>

<body>

<?php

define("PI",3.14159);

function getArea($radius) {

return $radius * $radius * PI;

}

function getCircum($radius) {

return 2*PI*$radius;

}


16

$data = $HTTP_GET_VARS;

$area = getArea($data["radius"]);

$circum = getCircum($data["radius"]);

print("Area is " . $area . " and circumference is " . $circum . "<br>");

?>

</body>

</html>

On to PHP and Databases All of this background will now come in handy as we learn how to send SQL queries to MySQL and process them in a PHP script. Here are the functions you need to use in somewhat of the order you need to use them: $link = mysql_connect($serverName,$username,$password);

REQUIRED. Sets up the connection to the database server. If you want the

connection to be persistent so that other clients can use the same connection

when you are done, use mysql_pconnect. $link will equate to false if

unsuccessful

$errStr = mysql_error();

OPTIONAL. Returns the error if the db has entered into some sort of error

state.

$result = mysql_select_db($nameOfDB, $link);

REQUIRED. This is like the mysql use clause. $result will equate to false if

unsuccessful

$result = mysql_query($sqlQueryString, $link);

REQUIRED. This actually issues the query against the database. $result will

equate to false if unsuccessful

$num = mysql_num_rows($result)

OPTIONAL. Returns the number of rows returned by the query

while ($row = mysql_fetch_assoc($result))

{

foreach($row as $key=>$val)

{

print “Fieldname = $key, value = $val<br>”;

}

}

SOMETHING LIKE THIS IS REQUIRED, assuming you are using a select statement.

If the query is an update or an insert or something else that doesn‟t return

records, then you wouldn‟t use this.

The keys are the field names, and the value is the corresponding

value stored in that field in that row. If you don‟t need to visit every

column, you don‟t need the foreach statement. Just access the individual

row[„fieldname‟] values.

PHP Example

<HTML>

<HEAD>

<TITLE>Cool PHP Page</TITLE>

</HEAD>

<BODY>

<P>Welcome to my site.

<?php


17

$conn = mysql_connect(“localhost”,”root”,”irishsuck”);

mysql_select_db(“school”,$conn);

$sql = “select * from Major”;

$result = mysql_query($sql,$conn);

while ($row = mysql_fetch_assoc($result))

{

$majorID = $row[„majorID‟];

$majorName = $row[„majorName‟];

print “The code is $majorID and the name is $majorName<br>”;

}

?>

</BODY>

</HTML>

msis 501 spring 2011 studentcourse studentlname...

Documents