Introduction

Before the midterm we were talking about common architectures for web applications
We discussed the Model View Adapter (MVA) design pattern.
The models of the this pattern are used to persist information between browser requests.
This usually involves interactions with the file system or a database management system.
We then introduced MySQL and discussed the MySQL command shell.
The most common language used to interact with a database system is called SQL (Structured Query Language).
Although we started talking about SQL before the Midterm, I'd like to refresh people's memory before continuing...

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

Some basic SQL commands are:

select col1, col2, col3
from table1, table2, table3
where condition_holds;

Returns rows consisting of given columns of tables such that the given condition holds. For example,

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

SQL Insert, Delete, Update

To insert rows into a table you can use a command like:

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

or

INSERT INTO table_name VALUES ('col1_value_1', 'col2_value_1', 'col3_value_1'),
    ('col1_value_2', 'col2_value_2', 'col3_value_2'), ...
    ('col1_value_n', 'col2_value_n', 'col3_value_n');

or

INSERT INTO table_name 
    SELECT col_1, ..., col_n
    FROM table_1, ..., table_m
    WHERE condition;

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;
```

Quiz

Which of the following statements is true?

Every key of the $_REQUEST super-global variable appears as a key in the $_POST super-global variable.
Cookies are implemented in the browser using the HTTP Session header.
In the MVA design pattern views in a web app are typically used to draw the HTML that a user sees.

PHP Database APIs

PHP can connect with many different database systems: DB2, Oracle, MariaDB, Mysql, Postgres, SQLite, etc.
To start we are going to look at how to do this for Mysql/MariaDB because this is what comes with MAMP, WAMP, XAMPP.
As we'll see there is usually more than one API to connect to the same DBMS in PHP: the "native" API, and the "generic" API using PDO.
The former tends to expose more DBMS specific abilities, the later tends to help your code portability if you need to switch which DBMS you are using.
For some DBMS's, there are actually two "native" APIs rather than one. For example, for mysql there are functions mysql_ and mysqli_, the former being the very common original way to connect to a Mysql database from PHP and the latter, the more modern way.
For new code, you should use mysqli_ as the mysql_ functions have been removed in PHP 7.
mysql_ is also a function only interface; whereas, mysqli_ can be used in a function (almost the same call as mysql_ but using mysqli_ ) or object-oriented approach (where create an object and use its methods).
Another situation of this type occur with the Sqlite and Sqlite3 APIs.

Connecting to a database from PHP

To start a connection to a MySQL database one can issue the command:
```
$db = mysqli_connect();
```
This function actually can take up to six parameters: the host, the username, the password, a database, a port, and a socket.
These default to localhost, the default mysql user and password, blank, default port and socket. A slightly more advanced, connection call might be:
```
$db = mysqli_connect($host, $uname, $pword, $db_name); 	
```
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 connection, one can call mysqli_close();

Selecting a Database and queries

To select a database after a connection has been made (sometimes useful) one calls:
```
mysqli_select_db($db, "vehicles");
```

One can then do a query with code like:

$query ="SELECT * FROM CAR";
$result = mysqli_query($db, $query);
$num_rows = mysqli_num_rows($result);
$num_fields = mysqli_num_fields($result);
echo $num_fields;
for($j = 1; $j <= $num_rows; $j++)
{
	$row = mysqli_fetch_array($result);
	print_r($row);
}

mysqli_query can also be used to do inserts, etc.

Accessing More Advanced Features of Mysql

As we mentioned before, mysqli can be used in both a procedural and object-oriented fashion.
We illustrate this by showing another way we could do queries in mysql: Using prepared statements.
When one does a mysqli_query, the database has to compile the query each time it gets it, it can't compile the query, optimize it, and store it for the next time. This is because the argument to the mysql query might completely change between calls.

Using prepared statements, we can reuse a query we have already compiled and set up with fixed slots that may change. For example:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db"); 
// note: here we are using the OO interface to mysqli
// we'll talk more about OO in PHP later today; for now, pretend its like Java/C++
$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("SELECT FNAME, AGE FROM EMPLOYEE WHERE LNAME=? AND AGE > ?")) {
    $lnames = ["Smith", "Jones", "Pollett"];
    $min_age = 25;
    foreach($lnames as $lname) {
       $stmt->bind_param("si", $lname, $min_age); //s == string, i == int, d==double
       $stmt->execute();
       $stmt->bind_result($fname, $age);
       $i = 0;
       while($stmt->fetch()) {
          print("The {$i}th person I found with last name $lname and age greater than $min_age was  $fname, $age\n");
          $i++;
       }
    }
    $stmt->close();
}
$mysqli->close();

Another advantage of the above is that there is less of an issue about properly escaping the parameters.

Databases and PHP

Outline