SQL

CSE 190 M (Web Programming) Spring 2007

University of Washington

Reading: Sebesta Ch. 14 sections 14.1 – 14.2, 14.4, 14.6
References: SQL syntax reference, w3schools tutorial

Except where otherwise noted, the contents of this presentation are © Copyright 2007 Marty Stepp and are licensed under the Creative Commons Attribution 2.5 License.

Relational databases

• relational database: A method of structuring data as tables associated to each other by shared attributes.
• a table row corresponds to a record (tuple); a column corresponds to an attribute (field) of the record
• relational databases typically use Structured Query Language (SQL) to define, manage, and search data

Why databases?

• powerful: can search it quickly, filter data, combine data from multiple sources
• big: scale well up to very large data sizes
• safe: built-in mechanisms for failure recovery (transactions)
• multi-user: concurrency features let many users view/edit data at same time
• abstract: provides layer of abstraction between stored data and app(s)
  • many database programs understand the same SQL commands

Database software

• Oracle database
• Microsoft SQL Server (powerful) and Microsoft Access (simple)
• IBM DB2
• PostgreSQL (powerful/complex free open-source database system)
• MySQL (simple free open-source database system)
  • many "LAMP" servers run Linux, Apache, MySQL, and PHP
  • Wikipedia is run on PHP and MySQL
  • we will use MySQL in this course

Database design

• database design : the act of deciding the schema for a database
• database schema: a description of what tables a database should have, what columns each table should contain, which columns' values must be unique, etc.
• some database design principles:
  • keep it simple, stupid
  • eliminate redundancy, especially redundancy of lengthy data (strings)

First database design

• what's good and bad about this design?
• uses only one table, but contains redundancy

Second database design

• splitting data into two tables (linked by Student ID) avoids redundancy
• this is also called normalizing the database
• normalized tables are often linked by unique integer IDs

Structured Query Language (SQL)

SELECT name FROM Student WHERE SID = 456;

INSERT INTO Grade VALUES ('123', 'CPS130', 'C+');

• a language for searching and updating a database
• a standard syntax that is used by all database software (with minor incompatiblities)
• a declarative language: describes what data you are seeking, not exactly how to find it

The SQL SELECT statement

SELECT column(s) FROM table;

SELECT SID, CID FROM Grade;

• the SELECT statement searches a database and returns a set of results
  • the column name(s) written after SELECT filter which parts of the rows are returned
  • table and column names are case-sensitive
  • * keeps all columns

Issuing SQL commands directly in MySQL

% mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> USE Simpsons;

Database changed

mysql> SELECT * FROM Student;

+-----+----------+------------------+

| SID | name     | email            |

+-----+----------+------------------+

| 123 | Milhouse | milhouse@fox.com |

| 142 | Bart     | bart@fox.com     |

| 456 | Ralph    | ralph@fox.com    |

| 857 | Lisa     | lisa@fox.com     |

+-----+----------+------------------+

• other commands:
  • SHOW TABLES;
  • SHOW DATABASES;

The DISTINCT modifier

SELECT DISTINCT column(s) FROM table;

SELECT SID FROM Grade;

SELECT DISTINCT SID FROM Grade;

• eliminates duplicates from the result set

The WHERE clause

SELECT column(s) FROM table WHERE condition(s);

SELECT CID, grade FROM Grade WHERE SID = 142;

• WHERE clause filters out rows based on their columns' data values
• in large databases, it's critical to use a WHERE clause to reduce the result set size
• suggestion: when trying to write a query, think of the FROM part first, then the WHERE part, and lastly the SELECT part

More about the WHERE clause

WHERE column operator value(s)

SELECT * FROM Grade WHERE grade <> 'A+';

• the WHERE portion of a SELECT statement can use the following operators:
  • =, >, >=, <, <=
  • <> : not equal
  • BETWEEN min AND max
  • LIKE pattern
  • IN (value, value, …, value)

Multiple WHERE clauses: AND, OR

SELECT * FROM Grade WHERE grade <> 'A+' AND SID <= 142;

• multiple WHERE conditions can be combined using AND and OR

Approximate matches: LIKE

WHERE column LIKE pattern

SELECT * FROM Grade WHERE grade LIKE 'B%';

• LIKE 'text%' searches for text that starts with a given prefix
• LIKE '%text' searches for text that ends with a given suffix
• LIKE '%text%' searches for text that contains a given substring

Sorting: ORDER BY

ORDER BY column(s)

SELECT * FROM Grade WHERE grade LIKE 'B%' ORDER BY CID;

• sorts the result set by a given column
• can write ASC or DESC to sort in ascending (default) or descending order:

·         SELECT * FROM Grade ORDER BY CID DESC;

• can specify multiple orderings in decreasing order of significance:

·         SELECT * FROM Grade ORDER BY CID DESC, SID;

Connecting to MySQL in PHP: mysql_connect

$db = mysql_connect("host", "username", "password");

mysql_select_db("database name");

# connect to Simpsons database on local computer

$db = mysql_connect("localhost", "stepp", "6uldv8");

mysql_select_db("simpsons");

• mysql_connect opens connection to database on its server
  • any/all of the 3 parameters can be omitted (default: localhost, anonymous)
• mysql_select_db sets which database to examine

Error-checking: mysql_error

# connect to Simpsons database on local computer

$db = mysql_connect("localhost", "stepp", "6uldv8");

if (!$db) {

    die("A SQL error occurred: " . mysql_error());

}

if (!mysql_select_db("simpsons")) {

    die("A SQL error occurred: " . mysql_error());

}

• SQL commands can fail for a variety of reasons
  • database not reachable, wrong username/password, bad query syntax, …
• for debugging, always test the results of PHP's mysql functions
  • if they are FALSE (NULL), print mysql_error result to see what failed

Reading result data: mysql_query

$db = mysql_connect("host", "username", "password");

mysql_select_db("database name");

$results = mysql_query("SQL query");

while ($row = mysql_fetch_array($results)) {

    do something with $row;

}

• mysql_query sends SQL query to database and returns results
• mysql_fetch_array returns one result row as an associative array
  • the column names are its keys, and the record's values are its values

PHP MySQL example

# connect to Simpsons database on local computer

$db = mysql_connect("localhost", "stepp", "6uldv8");

mysql_select_db("Simpsons");

$results = mysql_query("SELECT * FROM Grade WHERE SID = 142;");

# loop through each of Bart's course grade records

while ($row = mysql_fetch_array($results)) {

    print("Course ID: {$row['cid']}\n");

}

Checking rows returned: mysql_num_rows

# connect to Simpsons database on local computer

$db = mysql_connect("localhost", "stepp", "6uldv8");

mysql_select_db("Simpsons");

$results = mysql_query("SELECT * FROM Grade WHERE SID = 142;");

# check whether Bart took any courses

if (mysql_num_rows($results) == 0) {

    print("Student 142 did not take any classes.\n");

    ...

}

• mysql_num_rows returns how many rows are in the results
  • if it returns 0, no data matched the query
  • if it returns 1, a single record (row) matched the query
  • if it returns > 1, many records matched the query

Other MySQL PHP functions

• mysql_num_fields : returns number of columns per result
• mysql_list_dbs : returns a list of databases on this server
• mysql_list_tables : returns a list of tables in current database
• mysql_list_fields : returns a list of fields in the current data
• complete list

IMDb database

• database name is imdb on server webster.cs.washington.edu
• other tables:
  • Director (id, fname, lname)
  • Movie_Director (did, mid)
  • Movie_Genre (mid, genre)

Practice problem: Movie search

• Write a PHP script that connects to the imdb database on webster and searches for all movies whose names match a given prefix, displaying them as an HTML table. Assume that the prefix is a query string parameter passed into the script.
• Consider modifying the code so that, if only one movie matches, it will print the IDs of all actors who acted in that movie. (This isn't very useful, but we'll improve it next time.)

Combining multiple tables: cross product

SELECT column(s) FROM table1, table2, ..., tableN;

SELECT * FROM Student, Grades;

• cross product : combines each row of first table with each row of second
• achieved in SQL by specifying multiple tables in FROM clause of SELECT statement
• produces M * N rows, where table 1 has M rows and table 2 has N
• you probably don't want to do this (too much irrelevant data)

Cross product example

SELECT * FROM Student, Grades;

• much of the data is meaningless (e.g. Bart mixed with Lisa's courses)
• some columns might repeat (SID)

Joins

SELECT column(s) FROM table1, table2, ..., tableN WHERE condition(s);

SELECT column(s) FROM table1

JOIN   table2 ON condition(s)

...

JOIN   tableN ON condition(s);

SELECT * FROM Student, Grade WHERE Student.SID = Grade.SID;

• join : a relational database operation that combines records from two or more tables if they satisfy certain conditions
• often the rows are linked by key column values

Join example

SELECT * FROM Student, Grade WHERE Student.SID = Grade.SID;

• table.column disambiguates two columns with the same name
• an equivalent query:

SELECT * FROM Student JOIN Grade ON Student.SID = Grade.SID;

Filtering columns during a join

SELECT name, Grade.* FROM Student, Grade

WHERE  Student.SID = Grade.SID;

• if a column name only exists in one table, it may be written by itself
• to specify all columns from a table, write table.*

Giving names to tables

SELECT name, g.*

FROM   Student s, Grade g

WHERE  s.SID = g.SID;

• can give optional names to tables or columns, like a variable name in Java

Self-joins

SELECT *

FROM   Student s1, Student s2

WHERE  s1.SID < s2.SID;

• can give optional names to tables or columns, like a variable name in Java

Practice problem: Cast list for a movie

Write a PHP script that, when given a movie, shows the names of all female actors that appeared in it. (To do this, you will need to perform an SQL query with join operations.)

Cookies and Sessions

Cookies and Sessions

CSE 190 M (Web Programming) Spring 2007

University of Washington

Reading: Sebesta 12.12 – 12.13, 10.6

References: tizag.com sessions, cookies; Codewalkers

Except where otherwise noted, the contents of this presentation are © Copyright 2007 Marty Stepp and are licensed under the Creative Commons Attribution 2.5 License.

Stateful client/server interaction

Sites like amazon.com seem to "know who I am." How do they do this? How does a client uniquely identify itself to a server, and how does the server provide specific content to each client?

• HTTP is a stateless protocol; it simply allows a browser to request a single document from a web server
• in these slides, we'll learn about pieces of data called cookies used to work around this problem, which are used as the basis of higher-level sessions between clients and servers

What is a cookie?

• cookie: a small amount of information sent by a server to a browser, and then sent back by the browser on future page requests
• cookies have many uses:
  • authentication
  • user tracking
  • maintaining user preferences, shopping carts, etc.
• a cookie's data consists of a single name/value pair, sent in the header of the client's HTTP GET or POST request

How cookies are sent

• when the browser requests a page, the server may send back a cookie(s) with it
• if your server has previously sent any cookies to the browser, the browser will send them back on subsequent requests

Myths about cookies

• Myths:
  • Cookies are like worms/viruses and can erase data from the user's hard disk.
  • Cookies are a form of spyware and can steal your personal information.
  • Cookies generate popups and spam.
  • Cookies are only used for advertising.
• Facts:
  • Cookies are only data, not program code.
  • Cookies cannot erase or read information from the user's computer.
  • Cookies are usually anonymous (do not contain personal information).
  • Cookies CAN be used to track your viewing habits on a particular site.

How long does a cookie exist?

• session cookie : the default type; a temporary cookie that is stored only in the browser's memory
  • when the browser is closed, temporary cookies will be erased
  • can not be used for tracking long-term information
  • safer, because no programs other than the browser can access them
• persistent cookie : one that is stored in a file on the browser's computer
  • can track long-term information
  • potentially less secure, because users (or programs they run) can open cookie files, see/change the cookie values, etc.

Where are the cookies on my computer?

• IE: HomeDirectory\Cookies
  • e.g. C:\Documents and Settings\jsmith\Cookies
  • each is stored as a .txt file similar to the site's domain name
• Firefox: HomeDirectory\.mozilla\firefox\???.default\cookies.txt
  • view cookies in Firefox preferences: Privacy, Show Cookies…
     

Setting a cookie in PHP

setcookie("name", "value");

setcookie("username", "martay");

setcookie("favoritecolor", "blue");

• setcookie causes your script to send a cookie to the user's browser
• setcookie must be called before any output statements (HTML blocks, print, or echo)
• you can set multiple cookies (20-50) per user, each up to 3-4K bytes
• technically, a cookie is just part of an HTTP header, and it could be set using PHP's header function (but this is less convenient, so you would not want to do this):

header("Set-Cookie: username=martay; path=/; secure");

Retrieving information from a cookie

$variable = $_COOKIE["name"];   # retrieve value of the cookie

if (isset($_COOKIE["username"])) {

    $username = $_COOKIE["username"];

    print("Welcome back, $username.\n");

} else {

    print("Never heard of you.\n");

}

print("All cookies received:\n");

print_r($_COOKIE);

• any cookies sent by client are stored in $_COOKIES associative array
• use isset function to see whether a given cookie name exists
• unset function deletes a cookie

Setting a persistent cookie in PHP

setcookie("name", "value", timeout);

$expireTime = time() + 60*60*24*7;   # 1 week from now

setcookie("CouponNumber", "389752", $expireTime);

setcookie("CouponValue", "100.00", $expireTime);

• to set a persistent cookie, pass a third parameter for its timeout in seconds
• time function returns the current time in seconds
  • date function can convert a time in seconds to a readable date

Removing a persistent cookie

setcookie("name", "", time() - 1);

setcookie("CouponNumber", "", time() - 1);

• if the server wants to remove a persistent cookie, it should set it again, passing a timeout that is prior to the present time

What is a session?

• session: an abstract concept to represent a series of HTTP requests and responses between a specific Web browser and server
  • HTTP doesn't support the notion of a session, but PHP does
• sessions vs. cookies:
  • a cookie is data stored on the client
  • a session's data is stored on the server (only 1 session per client)
• sessions are often built on top of cookies:
  • the only data the client stores is a cookie holding a unique session ID
  • on each page request, the client sends its session ID cookie, and the server uses this to find and retrieve the client's session data

How sessions are established

• client's browser makes an initial request to the server
• server notes client's IP address/browser, stores some local session data, and sends a session ID back to client
• client sends that same session ID back to server on future requests
• server uses session ID to retrieve the data for the client's session later, like a ticket given at a coat-check room

Sessions in PHP: session_start

session_start();

• session_start signifies your script wants a session with the user
  • must be called at the top of your script, before any HTML output is produced
• when you call session_start:
  • if the server hasn't seen this user before, a new session is created
  • otherwise, existing session data is loaded into $_SESSION associative array
  • you can store data in $_SESSION and retrieve it on future pages
• complete list of PHP session functions

Accessing session data

$_SESSION["name"] = value;        # store session data

$variable = $_SESSION["name"];     # read session data

if (isset($_SESSION["name"])) {  # check for session data

if (isset($_SESSION["points"])) {

    $points = $_SESSION["points"];

    print("You've earned $points points.\n");

} else {

    $_SESSION["points"] = 0;  # default

}

• the $_SESSION associative array reads/stores all session data
• use isset function to see whether a given value is in the session

Where is session data stored?

• on the client, the session ID is stored as a cookie with the name PHPSESSID
• on the server, session data are stored as temporary files such as

·         /tmp/sess_fcc17f071...

• you can find out (or change) the folder where session data is saved using the session_save_path function
• for very large applications, session data can be stored into a SQL database (or other destination) instead using the session_set_save_handler function

Browsers that don't support cookies

session_start();

# Generate a URL to link to one of our site's pages

$orderUrl = "/order.php?PHPSESSID=" . session_id();

• if a client's browser doesn't support cookies, it can still send a session ID as a query string parameter named PHPSESSID
  • this is done automatically; session_start detects whether the browser supports cookies and chooses the right method
• if necessary (such as to build a URL for a link on the page), the server can find out the client's session ID by calling the session_id function

Session timeout

• because HTTP is stateless, it is hard for the server to know when a user has finished a session
• ideally, user explicitly logs out, but many users don't
• client deletes session cookies when browser closes
• server automatically cleans up old sessions after a period of time
  • old session data consumes resources and may present a security risk
  • adjustable in PHP server settings or with session_cache_expire function
  • you can explicitly delete a session by calling session_destroy

Practice problem: remembering query

• Modify the movie.php movie search script from previous lectures so that it remembers the current user's last query (if any), and offers the user a chance to search for it again, such as:
  • Welcome back! Would you like to repeat your recent search for Fight Club?
• Pretend that the movie-search program is running on a system that wants to limit repeated usage by particular users. Add code so that a given user can only conduct one session per day.

                                   MADEZEE.COM-HOME