Website Architecture

On Monday, we talked PHP functions to handle the file system, sessions, and we started talking about databases.
At this point, we know how to output html from PHP, control structure available with PHP, and a little about making data permanent (on a hard drive somewhere).
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 imagine 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 we imagine as being responsible for drawing web pages. They should mainly be HTML with little or no logic. They may have functions like those from last day which take data created elsewhere and draw it in the content of an HTML page.
controllers folder files we imagine as containing the logic of the application. Code here should not touch permanent storage directly nor should it render HTML.
models folder files we imagine as containing 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, we imagine this as always hitting the 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.

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

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

Let's Build Something

Using the mysql shell, create a database THE_WORLD with table PERSON with integer attributes for id and age, and with varchar(20) attributes for first_name and last_name.
Still in the shell, insert three rows into this table.
Write and execute a query which will select exactly two of the above rows.
Copy a transcript of the above session into a text editor add a single space to each line. (This ensures when you post it that it will preserve formatting.)
Then post your transcript to the Mar 2 Class Thread.

Connecting to a database from PHP

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 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 portable 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.
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); 	
```
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("cars");
```

One can then do a query with code like:

$query ="SELECT * FROM Corvettes";
$result = mysqli_query($query);
$num_rows = mysqli_num_rows($result);
$num_fields = mysqli_num_fields($result);
for($j = 1; $j <= $num_rows; $j++)
{
	$row = mysqli_fetch_array($result);
	print $row[0].$row["some_attr"]. "<br />";
}

mysqli_query can also be used to do inserts, etc.

Web-site Architecture, Databases and Classes

Outline

Website Architecture

More Website Architecture

MySQL Databases

MySQL Command Line

MySQL Command Line

Basic SQL

Database, Table Creation Deletion

SQL Query

SQL Insert, Delete, Update

Let's Build Something

Connecting to a database from PHP

Connecting to a database from PHP

Selecting a Database and queries