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.

Other Database Interfaces in PHP

As I may have mentioned, PHP has support for the usual database suspects: Oracle, DB2, Postgres, Sybase, etc, each having their own suite of functions orcl_, db2_, pg_, sybase_ ...
PHP's default build has also includes in it support for Sqlite 2 and Sqlite 3 databases.
Sqlite is a tiny highly optimized and tested C program to allow you to create SQL databases.
It stores databases in a flat file and is used as an embedded persistence layer in a wide variety of platforms: Firefox, iPhone, Android, Adobe, etc.
Sqlite database files created on one of these platforms can be read by any other. Further, since the database is stored in a flat file, it can be very useful for storing default data needed in deploying an app.

Sqlite2 in PHP uses a function style interface:

$dbh = sqlite_open($filename, $write_mode); 
$result = sqlite_query($query);
$row = sqlite_fetch_array($result);
sqlite_close($dbh);

Sqlite3 in PHP uses an OO style interface:

$dbh = new SQLite3($filename, $mode); 
$results=$dbh->query($query); 
$row = $results->fetchArray(); 
$dbh->close();

Finally, PHP has an OO interface known as PDO, which can be used to access any DBMS database for which PDO drivers have been written. This includes all the major databases listed above.

Some Quick Remarks on Database Design

It is possible to store all of your data in a big table with lots of columns.
This is typically not done because it is wasteful of space. For instance, if we had a big table with Employee names, ssns, and children of employee names, we'd have to repeat the full Employee name for each child name; whereas, if we had a separate table called dependents we could just have columns in it: Employee ssn, child names, and not have to repeat the Employee name for each child of that employee.
One goal in DB design is to split the database into tables to store the data as efficiently as possible; yet, on the other hand, be sure we are preserving the original information, and are not introducing any false information when we join tables together.
An artistic approach to this problem can be done by trying to figure out the classes/entities that need to be stored in the database and the corresponding relationships between them.
After doing this modeling phase, people typically map each of the entities and relationships that were found to tables.
To check the result, a notion called functional dependency is used. A set of columns Y functionally depends on a set of columns X, if when we know the values for X we know the values of Y.
A dependency like X fixes X (or some subset of X's columns) is considered trivial.
We would like our tables to have the property that the only nontrivial dependencies X fixes Y on our tables involve X's which fix whole rows. (Boyce-Codd Normal Form).

Example

As an example, consider the table EMP_PROJ(SSN, PNUMBER, HOURS, ENAME, PNAME, PLOCATION).

Suppose the functional dependencies are:

SSN, PNUMBER--> HOURS
SSN --> ENAME
PNUMBER --> PNAME, PLOCATION.

Some drawbacks of this table are that:
- To insert an employee before they have a project means, null padding rows.
- Deleting the last employee from a project might end up deleting the project.
- When we add an employee to a second project we are duplicating ENAME unnecessarily, as once we know the SSN we've already fixed the ENAME.
The above table is not in BCNF as SSN --> ENAME is a non-trivial FD, yet knowing the SSN does not fix the whole row as someone might work on multiple projects.
We can make a database schema with the above attribute which is in BCNF using the following tables: EMPLOYEE(SSN, ENAME), PROJECT(PNUMBER, PNAME, PLOCATION), WORKS_ON(SSN, PNUMBER, HOURS).
Notice this database schema avoids the three issues above, giving evidence to the idea that putting things into BCNF avoids the kind of DB anomalies we don't want.

Quiz

Which of the following statements is true?

The post redirect get pattern as presented in class uses location headers to performs redirects.
SQL queries can only involve a single table, SQL++ queries can involve multiple tables.
You should use the mysql_ group of functions in new PHP code that connects to a mysql database.

Classes in PHP

Classes in PHP can be defined with a syntax like:

class MyClass
{
   public $field_var1;
   public $field_var2 = 5;
   ...
   const constant1;
   const constant2;
   ...

   function __construct($arg1, $arg2) {
     //constructor code
   }

   function myFun1() {
     // code
   }
   ...
}

To create instances of a class we use the new operator. To dereference fields, or invoke instance functions we use the arrow -> operator.
```
$arg1 =5;
$arg2 = 6;
$my_class = new MyClass($arg1, $arg2);
echo $my_class->field_var1;
$my_class->myFun1();
```
Typically, you put the code for your class into a file and then use a line like:
```
require("MyClass.php");
//or more likely
require_once("MyClass.php");
```
We mentioned this earlier, but I'll just remind people: The require syntax is similar to include except when it fails it gives a fatal error rather than a warning. Also you cannot use require to include remote files even if allow_url_fopen is enabled.
require_once will not re-include the file if it has already been included.
PHP comes with several built-in classes and interfaces.
Several common data structures are also implemented in the Standard PHP Library (SPL).

Constructors/Destructors

We saw we could set up an initial value of a field variable of a class when we declare it:
```
public $my_variable = 0;
```
You can also have functions __construct and __destruct to do initialization and clean-up.
```
function __construct($n=0) {
     $this->my_variable = $n;
}
```

Private, Protected, Public

Member variables and member functions can be declared private, protected or public:
```
private $my_field;
protected function myMethod() { /* some code*/}
```
Methods without any declaration are the same as public. It is a good habit though to explicitly state the visibility
private means only visible within the class.
protected is visible within the class or within subclasses.
public means variable or method is visible to anyone. There is an older way to declare public fields:
```
var $my_field;
```
This is less clear than the public syntax so should be avoided.

Static and Const

The static keyword creates one instance of the field or method for the class.
```
class Foo{ static $bob=1;} 
echo "bob: ".Foo::$bob;
```
Within the class use self:: to refer to static members.
The const keyword can be used to define constants for a class:
```
class Goo{ const blob=1;}
```
Notice no dollar sign. Can refer to this using
```
Goo::blob
```
Values of constants cannot be changed.
You can create constants not within a class using define:
```
define("PI", 22/7);
echo PI;
```

Cloning

PHP has a command clone for cloning objects:
```
$my_copy = clone $my_obj;
```
To specify how the copying is done you can write a __clone() method for your class.

Inheritance

So far we've described how to make a PHP class, constructors, destructors, and cloning.
We've said unlike the default copy-on-write semantics of array assignment, object assignment in PHP is by reference. Let's now turn to inheritance.
Inheritance in PHP is very similar to Java.
A PHP class can extend one other class.
```
class A{}
class B extends A {}
```

PHP also has a notion of interface:

interface MyInterface
{
    function method1($a, $b);
}

A PHP class can implement multiple interfaces:

class C implements MyInterfaceA, MyInterfaceB {}

If you want to have a class with some but not all of its methods defined. You can use the keyword abstract on those methods which will be overriden in subclasses.

Finish Databases -- Object-oriented PHP

Outline