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.

In-Class Exercise

Suppose we are trying to build an online student registration system.
We need to model students and the class sections they take of some course.
Suggest some tables for this and indicate the non-trivial functional dependencies.
Try to rework your tables so they are in BCNF.
Post your solution to the Oct 14 In-Class Exercise Thread.

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

PHP Classes

Outline

Other Database Interfaces in PHP

Some Quick Remarks on Database Design

Example

In-Class Exercise

Classes in PHP

Constructors/Destructors

Private, Protected, Public

Static and Const

Cloning