PHP: Cookies, Sessions, Databases

CS174

Chris Pollett

Sep 27, 2010

Outline

How cookies work
Cookies in PHP
Quiz
Sessions
Databases

Cookies

Sometimes it is useful to remember a client when it comes back, To do this one can use the HTTP-Cookie protocol.
The Server can send as one of its response headers: Set-Cookie: name=value; expires=some date; path=some path; domain=some_domain;
In PHP you can write out a header using the header() function.
When the Client comes back, it will send the cookie as part of its request header as: Cookie: name=value

Cookies in PHP

Cookies can be set using the setcookie function:
setcookie(name, value, expires);
This should be done before output is produced by your script.
To access the value of a cookie returned from a browser you can use the $_COOKIE array.

Quiz

Which of the following statements is true?

In PHP, %my_hash could be the name of an associative array.
The $_REQUEST superglobal contains information about forms variables posted to the given script.
In the PHP snippet:
```
function my_fun($foo) { /* some code*/}
```
$foo is passed by reference.

Sessions

PHP supports session management.
To start a session one calls session_start();

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"];

Database Management Systems and Databases

For our purposes a database management systems (DBMS), is used to manage a collection of databases.
That is, 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.
Example: Might have a table car(make VARCHAR(10), model VARCHAR(10), year DATE). A possible row could be
```
('Toyota', 'Corolla', '1970-01-01')
```

MySQL Databases

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.
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

Some basic SQL commands are:

select col1, col2, col3
from table1, table2, table 3
where condition_holds;

Returns rows consisting of given columns of tables such that the given condition holds. For example,
```
select FNAME, LNAME
from EMPLOYEE
where SALARY > 10000;
```
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
```
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).

SQL Insert, Delete, Update

You can insert a row into table one uses a command like:

INSERT INTO table_name VALUES ('col1_value', 'col2_value', 'col3_value');

To delete a set of rows from a table you can use a command like:
```
DELETE FROM table_name WHERE cond;
```
To update a set of rows you can use a command like:
```
UPDATE table_name SET column='value' WHERE cond;
```

Connecting to MySQL from PHP

To start a connection to a MySQL database one can issue the command: $db = mysql_connect();
This function actually takes three parameters: the host, the username, and the password.
These default to localhost, the process name PHP runs under, and blank.
```
$db = mysql_connect($host, $uname, $pword); 	
```
Depending on how mysql is configured, the first example above might work and saves some typing.
This function returns false if a connection is not made.
To close a database, one can call mysql_close();

Selecting a Database and queries

To select a database one calls:
```
mysql_select_db("cars");
```

One can then do a query with code like:

$query ="SELECT * FROM Corvettes";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
$num_fields = mysql_num_fields($result);
for($j = 1; $j <= $num_rows; $j++)
{
	$row = mysql_fetch_array($result);
	print $row[0].$row["some_attr"]. "<br />";
}

mysql_query can also be used to do inserts, etc.