Database Connection

2.4.1 Simple Database Access

The Harmoni database wrapper, handled by the DBHandler class, provides access to SQL databases. Supported databases include: MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and through OKI's SQL OSID, which, as of the time of this writing, is not yet complete.

You might wonder (if you've looked at some code or the PHPDoc) why using such a seemingly round-about approach to database access is useful. For those of you that aim to connect to only one specific type of database, these wrappers may not be so useful. However, in order to target the largest audience in the internet community, it is important to keep in mind that people are different; specifically, some use MySQL, some PostgreSQL, and so on. The Harmoni DBHandler allows you to use identical PHP code to select, insert, update and delete rows from databases, even if they are of different kinds. This way, no matter if the person that downloads your program uses Oracle or MySQL, your code WILL WORK.

So, without further ado, let's look at some simple database access scripts.

:: Connecting/Disconnecting ::

Example


$dbHandler =& Services::getService("DBHandler");

$dbIndex = $dbHandler->addDatabase(
        new PostgreSQLDatabase(
            "my.host.com",
            "myDatabaseName",
            "myUser",
            "secret")
        );
        
$dbHandler->connect($dbIndex);
// you could also use $dbHandler->pConnect($dbIndex);
// for a persistent connection.

if ($dbHandler->isConnected($dbIndex)) print "We're connected!";
else print "Doh.";

$dbHandler->disconnect($dbIndex);

The above code does nothing more than connect to a Postgres database and then disconnect. Amazing.

:: Select Query ::

Now, let's fetch some data from a table named "table1".

Example


$query =& new SelectQuery;

$query->addTable("table1");
$query->addColumn("column1");
$query->addColumn("column2", "anAlias"); // fetch column2 AS anAlias
$query->addWhere("column1 = 'jones'");

// execute the query!
$result =& $dbHandler->query($query, $dbIndex);

print "Our query has ". $result->getNumberOfRows() . "rows!\n";

while ($result->hasMoreRows()) {
    $array = $result->getCurrentRow();
    
    print "Result row: column1 = " . $array['column1'] . ", column2 AS anAlias: " .
                        $array['anAlias'] . "\n";
    
    $result->advanceRow();
}

The above might output something like:

Our query has 3 rows!
Result row: column1 = jones, anAlias = fred
Result row: column1 = jones, anAlias = george
Result row: column1 = jones, anAlias = franky

The SQL query could look something like:

SELECT column1, column2 AS anAlias
        FROM table1
        WHERE column1 = 'jones'

This is a very simple example. The SelectQuery can do a whole lot more. Take a look at the PHPDoc or later chapters for more information.

:: Insert Query ::

Example


$query =& new InsertQuery;
$query->setTable("table1");

$query->setColumns(
    array(
        "column1",
        "column2")
    );

$query->addRowOfValues(
    array(
        "jones",
        "margy")
    );
$query->addRowOfValues(
    array(
        "jameson",
        "bobby")
    );

$result =& $dbHandler->query($query, $dbIndex);

if ($result) {
    print "Success! We inserted " . $query->getNumberOfRows() . " rows into the database!\n";
} else {
    print "Ergh. Something happened.\n";
}

Under MySQL, the query would look like:

INSERT
    INTO table1
    (column1, column2)
    VALUES ("jones", "margy")
    VALUES ("jameson", "bobby")

:: Update Query ::

Example


$query =& new UpdateQuery;
$query->setTable("table1");
$query->setColumns(array("column1"));
$query->setValues(array("yodle"));
$query->setWhere("column1='jones'");

$result =& $dbHandler->query($query,$dbIndex);

print "There were " . $result->getNumberOfRows() . " members of the jones family.";

The "jones" family just changed their last name to "yodle". Don't ask me. Here's the query under MySQL:

UPDATE
    table1
    SET column1 = "yodle"
    WHERE column1 = "jones"

:: Delete Query ::

Example


$query =& new DeleteQuery;
$query->setTable("table1");
$query->setWhere("column1='yodle'");

$result =& $dbHandler->query($query,$dbIndex);

print "The yodle family just lost " . $result->getNumberOfRows() . " members.";

Bad new... the "yodle" family just choked. Away they go. Here's the SQL:

DELETE
    FROM table1
    WHERE column1 = "yodle"

So, now you know how to do the basics and essentials to use databases. And no more writing SQL for you SQL-phobic folks out there!