Sessions

Last day, we were talking about the HTTP cookie protocol. Today, we consider the concept of sessions which are a application of this protocol.
A session is a collection of information about one user's interaction with a web site.
It is usually transient in nature and has some lifetime associated with it.
For example, interactions with a web-site leading up to a purchase. If a purchase is eventually made, the purchase data is permanently recorded in the database. The permanent store being different from the session.
Cookies can be used to implement sessions. A single name-value pair: session_id=some-random-string is sent as a cookie together with an expiry time. This pair when sent from the browser is used to look up on the server a file containing a serialized associative array of user data.
PHP supports session management.
To start a session one calls session_start(); . This is what sends the Set-Cookie header so needs to before any html is output. The default name used for the session_id is PHPSESSID. You can change this with the function session_name($some_name);

Then to set/get values of the session one uses the global array variable $_SESSION:

$_SESSION["test"]=37; /* sets the test session variable*/
echo $_SESSION["test"];

Website Architecture

At this point, we know how to output html from PHP, how to use the control structures available in PHP, and a little about how to make data permanent (on a hard drive somewhere) in PHP.
Before we continue talking about databases, I thought it would be useful to talk about how to organize your web applications as you are developing them.
A common approach is to use a variant of the Model-View-Controller (MVC) design pattern.
We split the project into different types of files (later we will use classes) which we might put into different project folders: views, models, and controllers.
views folder files are responsible for drawing web pages. They should mainly be HTML with little or no logic. They may have functions like those from earlier today which take data created elsewhere and draw it in the content of an HTML page.
controllers folder files contain the logic of the application. Code here should not touch permanent storage directly nor should it render HTML.
models folder files contain code to read and write data from the file system. It shouldn't contain logic related to the web app or render things to a browser.

More Website Architecture

In a traditional MVC set-up, a user sees a view, manipulates the GUI, which causes controller functions to be called. These functions in turn make models calls to read and write storage. If storage is written then the model might call view functions to update what the user sees.
In web-development we often use a slightly different MVC set-up known as Model-View-Adapter or mediating-controller MVC .
Here the user points his browser at a web page. Typically, this is set up to always hit the same index.php page.
From the request query string, the index.php determines a controller to use and a function in that controller to call.
This controller function uses the request to determine which model calls to make to read and write storage.
Based on the above, the controller function, determines which view is appropriate to send back to the user and intializes any data it may need.
The controller then calls the appropriate view render function to draw the web page the user will see in his browser.
Notice this is different from MVC in that the controller invokes both model and view methods. In traditional MVC, we have controller function invoking model functions which in turn invoke view updates.

Quiz

Which of the following statements is true?

The most typical way PHP programmers access form data is by manipulating the superglobal field $_SERVER['QUERY_STRING'].
Cookies are read and written using a non-HTTP protocol.
The glob function is used to return an array of paths to files or folders matching a search criterion.

Our Website Design Paradigm So Far...

Our basic set-up for website interaction so far is:
1. User comes to initial page of web-site.
2. A default web page (the landing page) for the site is served, either using a static HTML page or a with minimal PHP intervention.
3. The user clicks links or enters form data to navigate around the site.
4. No dynamic processing happens on the client.
5. On the server, a controller processes the link or form data, uses session information, gets/sets database information using models, and chooses a new view to display.
In choosing a form whether it should use method GET or POST, ask whether the result of processing the form data should be a bookmarkable location?
If it can be, use GET; otherwise, POST.
For forms that do things that we don't want to be repeatable with the same information, like purchasing items, posting comments or blog entries, etc., we should use POST.

POST REDIRECT GET

When you click the reload button on a page that is the result of a POST'd form, or use the back button to cycle through pages where forms were POST'd, your browser will typically ask if you want to resubmit the form data.
Not only is the back button somewhat broken by this, people have a nasty habit of clicking okay on any dialog box that pops up, which can result in a re-purchase, re-blog post, etc.
To solve this problem, we use a web design pattern known as POST-REDIRECT-GET.
The idea is when POST is used for the form:
```
<form method="post">

<input type="submit" name='submit button' value='submit' />
</form>
```
we process the data in the receiving controller. Rather than choose a view and display it, we instead, output a 301 redirect, location header to some safe page. In PHP, we could do this with a line like:
```
<?php
header("Location:http://somewhere.safe.com/");
```
When the browser, goes to http://somewhere.safe.com/, it will use GET to request the page.
Because the POST'd form data resulted in a redirect, the get location only is stored in the browser history. So now going back through the history using the back button will go through this same page and before this the page where the form was initially blank.

Database Management Systems and Databases

Models in the MVA pattern often interact with a database so we are going to have a whirlwind tour of databases to learn how to do this.

For our purposes, a database management systems (DBMS) is used to manage a collection of databases.
It manages how the data is stored in each database, and who can access it when.
A given database consists of a collection of tables.
Each table has a type for each of its columns and then has rows of data where the data values match the values of the column types.
For example, car(make VARCHAR(10), model VARCHAR(10), year DATE) is an example table schema (specification). A possible row of car is:
```
('Toyota', 'Corolla', '1970-01-01')
```

MySQL Databases

Let's learn how to write to databases now, so we have a good infrastructure to write models.
To manage MySQL databases you need to know how to:
- Create and manage users
- Create new databases and tables
- Do lots of other stuff I won't talk about
- Many of these operations can be done using phpMyAdmin.
- Alternatively, you can use the command line.

MySQL Command Line

I will assume you have set your path so that you can directly run mysqladmin and mysql.
To set the root password you can go to the command prompt and type:
```
mysqladmin -uroot password what_password_is
```
You can get a mysql shell by then typing:
```
mysql -uroot -pwhat_password_is
```

MySQL Command Line

To see what databases are available you can use the command:
```
show databases;
```
To use a particular database type:
```
use dbname;
```
To see what tables the currently use database has type:
```
show tables;
```

To add a new user you can type at the prompt:

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES 
  on dbname.* TO username@127.0.0.1 IDENTIFIED BY 'password';

To quit mysql type \q or quit. For help type \h or help.
To login using this new user type at the command prompt:
```
mysql -uusername -ppassword
```

Basic SQL

SQL (Structured Query Language) is the most common language for issuing commands to a database system.
These commands can usually be given either from the mysql prompt or from within a PHP transaction that is connecting to the database.

Database, Table Creation Deletion

To create a database one uses the CREATE DATABASE command:
```
CREATE DATABASE my_nifty_db;
```
Then one can type use my_nifty_db; to use this database.

To create a table in the currently used database one can use a command like:

CREATE TABLE customer (
    cust_id int(5) NOT NULL,
    name VARCHAR(50),
    title CHAR(3),
    PRIMARY KEY(cust_id)
);

To delete a table type:
```
DROP TABLE customer;
```
To delete a database
```
DROP DATABASE my_nifty_db;
```

SQL Query

A basic SQL query has the format:

select col1, col2, col3
from table1, table2, table3
where condition_holds;
// you may also have additional clauses for grouping and ordering results.

The above query return rows consisting of the given columns of tables such that the given condition holds.

Let's look at some examples:

/* To Get First Names and Last Names of 
   Employees with Salary's bigger than $10000
 */
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SALARY > 10000;

/*
   To get triples (FNAME, LNAME, PNAME) of Employees
   who work on projects with names containing
   the string "SECRET", sorting result in descending
   alphabetical order, showing results 10 through 60.
 */
SELECT E.LNAME AS LNAME,
   E.FNAME AS FNAME,    
   P.PROJECT_NAME AS PNAME
FROM EMPLOYEE E, PROJECT P, WORKS_ON W
WHERE E.EID=W.EID AND P.PID=W.PID
   AND P.PROJECT_NAME LIKE '%SECRET%'
ORDER BY E.LNAME DESC, E.FNAME DESC, 
   P.PROJECT_NAME DESC
LIMIT 10,50; /* start at 10 plus next 50 */

/* Number of classes each student is taking
 */
SELECT STUDENT.NAME, COUNT(*) /* AVG, COUNT, SUM are aggregate functions*/
FROM STUDENT, COURSE
WHERE STUDENT.ID=COURSE.SID
GROUP BY STUDENT.NAME;

The general form of a select statement looks like:
```
 
SELECT T1.FIELD_NAME_1 AS NAME1, ..., T1.FIELD_NAME_N AS NAME_N,
 ..., TM.FIELD_NAME_N AS SOME_NAME
FROM T1, T2, ..., TM
WHERE T1.FIELD_NAME_j = some value [OR|AND] ... T1.FIELD_NAME_k = TJ.FIELD_NAME_w
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
[LIMIT ...]
```
The above are called SELECT statements and consist of three main lines (plus optional ones). The first line lists the columns we want, the second line lists the tables to use, the last line lists the conditions that must be met by the returned row (join conditions).
We have tried to format the SQL nicely but there is no requirement to do so.

Sessions, Web Architecture, Databases

Outline

Sessions

Website Architecture

More Website Architecture

Quiz

Our Website Design Paradigm So Far...

POST REDIRECT GET

Database Management Systems and Databases

MySQL Databases

MySQL Command Line

MySQL Command Line

Basic SQL

Database, Table Creation Deletion

SQL Query