Using a database from PHP pages.
The Pear database library MDB2 supercedes 'DB' & 'MDB' to provide a database access layer for PHP.
About this example
Precisium's Openplatform Unix-based PHP accounts are pre-configured with pear installed. You can install your own pear libraries if you wish - but the example below assumes only the default installation.
This example uses the sqlite database engine because it is a standalone file-based database which makes for a simple example. By changing the connection details, it could easily be modified to access other databases such as MySQL.
Here is an example of creating a database table, inserting records and then reading and displaying the records on the page.
<?php
//The first 3 lines are fairly specific to the openplatform hosting environment.
//They return the path to your file-based-database folder.
include("WEBPUB.php");
webpub_docroot();
$db_folder = $WEBPUB['DB']. '';
require_once "MDB2.php";
$dsn = "sqlite:///".$db_folder."/test.sqlite";
$db = MDB2::factory($dsn);
if (PEAR::isError($db)) {
die("Failed to connect to database (check permissions on db folder '".$db_folder."'). err:" . $db->getMessage());
}
$db->loadModule('Manager');
$db->loadModule('Extended'); //allows getOne etc.
$tables = $db->listTables();
if (PEAR::isError($tables)) {
die("<br/>Failed to get table list: ". $tables->getMessage());
}
echo "<h2>table count: ". count($tables) . "</h2>";
if (count($tables) == 0) {
$tabledef = array(
'id' => array(
'type' => 'integer',
'unsigned' => 1,
'notnull' => 1
),
'active' => array(
'type' => 'boolean',
'default' => false
),
'fname' => array(
'type' => 'text',
'length' => 60
),
'lname' => array(
'type' => 'text',
'length' => 60
)
);
$db->createTable('person', $tabledef);
$db->createSequence('person');
//primary key constraint for id
$definition = array(
'primary' => true,
'unique' => true,
'fields' => array(
'id' => array()
)
);
$db->createConstraint('person','recordid',$definition);
}
$count = $db->getOne("select count(id) from person");
if ($count == 0) {
$id = $db->nextID('person');
$sqlInsert = "insert into person (id,active,fname,lname) values($id,1,'fred','nerk')";
$result = $db->exec($sqlInsert);
if (PEAR::isError($result)) {
die ("Insert failed: " . $result->getMessage()."\n");
}
$id = $db->nextID('person');
$sqlInsert = "insert into person (id,active,fname,lname) values($id,1,'barney','rubbish')";
$result = $db->exec($sqlInsert);
if (PEAR::isError($result)) {
die ("Insert failed: " . $result->getMessage()."\n");
}
}
$query = "select fname,lname,active from person";
$result = $db->query($query);
if (!PEAR::isError($result)) {
echo "<ul>";
while (list($fname, $lname, $active) = $result->fetchRow(MDB2_FETCHMODE_ORDERED)) {
echo "<li>$fname $lname</li>";
}
echo "</ul>";
} else {
echo "<h3>No records found</h3>";
}
$db->disconnect();
?>