Connecting to MySQL from PHP

To start a connect 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 a command 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.

Accessing More Advanced Features of Mysql

PHP has a separate set of Mysql interface functions for accessing more advanced feature of Mysql (since Mysql 4.1).
One example of a useful newer feature is the ability to do prepared statements.
When one does a mysql_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:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world"); 
// 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 FROM EMPLOYEE WHERE LNAME=?")) {
    $lnames = array("Smith", "Jones", "Pollett");
    foreach($lnames as $lname) {
       $stmt->bind_param("s", $lname); //s == string, i == int, d==double
       $stmt->execute();
       $stmt->bind_result($fname);
       $stmt->fetch();
       print("First person I found with last name $lname was  $fname");
    }
    $stmt->close();
}
$mysqli->close();
?>

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); sqlite_fetch_array($result); sqlite_close($dbh);
Sqlite3 in PHP uses an OO style interface: $dbh = new SQLite3($filename, $mode); $results=$dbh->query($query); $data = $results->fetch_array(); $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).

Classes in PHP

Classes in PHP can be defined with a syntax like:

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

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

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

Typically, you put the code for your class into a file and then use a line like:
```
require("MyClass.inc");
//or more likely
require_once("MyClass.inc");
```
The require function 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.

Constructors/Destructors

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

Private, Protected, Public

Member variables and member functions can be declared private, protected or public:
```
private var $myField;
protected function myMethod() { /* some code*/}
```
Methods without any declaration are the same as public.
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.

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.

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

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.

Referring to Parents, Final

Consider:

class A {function foo(){} }
class B extends A {function foo(){} }

Within B, self::foo() refers to the redefinition of foo given in (2). (Instance methods can also be called using $this->method())
Within B, parent::foo() refers to class A's version of foo.
To prevent a function from being overridden in a subclass you can use the keyword final.
```
class A {final function foo() {} /* can't override*/ }
```

Exceptions

PHP supports try catch blocks like Java.

try {} catch(MyException $e){} catch(Exception $ee){}

You can use the keyword throw to throw an exception

if($denom == 0){
    throw new Exception("divide by zero");
}

You can subclass Exception to create custom exceptions.

PHP: Databases and Classes

Outline