Authors:
Manuel Lemos (mlemos@acm.org)
Lukas Smith (smith@backendmedia.com)
Version control: @(#) $Id: tutorial.html,v 1.8 2003/06/01 12:54:16 lsmith Exp $
MDB is a package that allows the developers to write database applications in PHP that are independent of the DBMS (DataBase Management System).
The steps that developers have to take to write MDB based applications are identical to those that are taken while using the set of native commands that are used to talk to each DBMS directly. The main advantage of MDB is that developers only need to learn and use one set of commands to implement applications that may run with many different DBMS.
The goal of this tutorial is to introduce the basic steps that beginners have to take to start developing database applications with MDB. For more in depth information, please refer to the MDB documentation manual.
Before start using MDB there are a few installation requirements that developers need to know and understand.
PHP is usually run from a Web server, mostly as a server module, but it can also run as CGI program. When PHP is built as CGI program a standalone executable program file is generated. This CGI program can also be used to execute PHP scripts from a command line interface shell.
Although it is possible do the same using PHP as Web server module, it is recommended that you use the CGI program to run the PHP scripts to install or setup databases using MDB.
The required version of PHP is 4.0.4pl1
PHP has several runtime options that are meant to ease the developers' work. The option named magic_quotes_runtime is meant to automatically escape special characters in text literal values before executing SQL queries.
MDB is already able to escape special characters of query text values using the methods getTextValue() and setParamText(). If either any of these methods is used and the magic_quotes_runtime option is On, then some characters may end up being escaped twice.
To avoid duplicated character escaping, please disable the option magic_quotes_runtime and also magic_quotes_sybase by setting them to the value Off in your PHP configuration by for instance having these lines in your php.ini file:
magic_quotes_runtime = Off
magic_quotes_sybase = Off
MDB is made of several PHP files that need to be included from your scripts, depending on what you need to do with MDB. Here follows the list of the most important files and when they should be included in your scripts.
Most other files are included on demand by MDB.This is the main API file. It should be included every time you need to interface with a database using MDB.
This is the database definition management class file. It should be included every time you need to read or change the definition of a database. It will automatically include the MDB.php file you.
Before anything else, you need to design and install a database schema that will hold the information that is needed by the application being developed.
MDB simplifies this step greatly because it lets the developers design their database schemas in a DBMS independent manner. All you need to do is to write a text file in a custom XML format that describes the tables and fields that you want your database to have.
The database schema description XML format is fully described in the MDB manual, but an example often explains more than an exhaustive manual. Therefore, lets look into the following commented example:
<?xml version="1.0" encoding="ISO-8859-1" ?>
This is a typical header line that all sorts of XML files must have.
<database>
This is the MDB start tag. All MDB XML files must start with this tag.
<name>test</name>
<create>1</create>
These are the database main properties. The name property is required. The create property is not required but since we want MDB to create the database for us when it is installed for the first time, this property has to be set to 1.
<table>
<name>users</name>
Table definitions may have two sections beside its properties: declaration and initialization. The declaration section is required because it must contain the declaration of all the table fields and any indexes you need. The initialization section is not required but it may be used if you need that the table be created and have already added some initial entries.
<declaration>
<field> <name>user_id</name> <type>integer</type> <default>0</default> <notnull>1</notnull> </field>
<field> <name>user_name</name> <type>text</type> </field>
<field> <name>password</name> <type>text</type> </field>
<field> <name>reminder</name> <type>text</type> </field>
<field> <name>name</name> <type>text</type> </field>
<field> <name>email</name> <type>text</type> </field>
The table declaration section must describe one or more fields. The definition of each field must contain at least the name and the type properties. Most common field types are integer and text, but others like date and timestamp are also frequently used.
<index>
<name>users_id_index</name>
<unique>1</unique>
<field> <name>user_id</name> </field>
</index>
The use of indexes is not required but applications always use them to speed up database access. Indexes are usually created on fields that make part of the search clauses of the most important queries that a database application runs.
The criteria that should be used to determine on which fields the indexes should be added is highly dependent on what the database application is meant for. So, orienting index creation decisions is beyond the scope of this tutorial.
Anyway, usually there are fields that are declared as primary or foreign keys for the table. MDB does not provide support for declaring table keys because not all DBMS support them. However, there is support for creating unique indexes on individual fields which in practice may work as if such fields were declared as primary keys.
Fields on which is created an index may not have NULL entries. Therefore, MDB schema description parser requires that such field should be declared with the notnull constraint property. It also requires that the default property be defined with a non-NULL value like in the case of the user_id field in the example above.
</declaration>
</table>
<sequence>
<name>user_id</name>
<start>1</start>
<on> <table>users</table> <field>user_id</field> </on>
</sequence>
Sequence objects are very useful when you need to generate integer values that are guaranteed to be unique every time you need a new identifier number to insert a new entry table.
With some DBMS developers often use fields declared with the auto_increment property to achieve a similar effect. MDB does not provide support for that property because not all DBMS support them.
Database application developers are recommended to use MDB sequences in the place of auto_increment fields. An advantage of using sequences is that you may use the sequence values to insert in other tables that you may need to also add related entries.
Sequences are standalone database schema objects. However, you should hint the database manager by specifying on which table field the sequence values are meant to be used. This may help to determine an appropriate sequence start value if you add the sequence to the database later than you have added the table field with which it should be synchronized.
<table>
<name>groups</name>
<declaration>
<field> <name>name</name> <type>text</type> </field>
<field> <name>owner_id</name> <type>text</type> </field>
<field> <name>description</name> <type>text</type> </field>
</declaration>
</table>
<table>
<name>group_users</name>
<declaration>
<field> <name>group_id</name> <type>text</type> </field>
<field> <name>user_id</name> <type>text</type> </field>
</declaration>
</table>
Other tables and sequences may be declared as much as your database application may need.
</database>
MDB XML files must end with a tag that matches the start tag.
Once you have defined your database schema description file, it is time to install it your DBMS server. Using the MDB manager class, this procedure becomes very simple.
Lets suppose you have written and saved your database schema to a file named MyDatabase.schema. To install the database for the first time, for instance in a MySQL server, all you need to do is to write a script that creates MDB manager class object and call its updateDatabase method. Then use PHP as standalone executable program to execute the script that should look like this:
<?php
require_once('MDB.php');
MDB::loadFile("Manager");
Include the necessary files.
$schema_file="MyDatabase.schema";
This is the definition of the name of your database schema file.
$variables=array();
If your database schema definition needs any install time variable values, you need to define them here. In the example schema above we have not used any variables. So, we actually do not need to define an array for the variables.
$manager=new MDB_manager;
Create the MDB manager class object.
$dbinfo = array(
"phptype"=>"mysql",
"username"=>"mysql_user_name",
"password"=>"mysql_user_password"
);
$manager->connect($dsn, $options);
To setup a connection to the database server you need to pass atleast a DSN in the same format as for MDB with some DBMS specific arguments that are passed to the MDB manager class connect method. The phptype argument will make the database interface class automatically include the respective database driver class that must be located in the current directory or else its path must be spceified with the IncludePath argument.
$success = $manager->updateDatabase($schema_file, $schema_file.".before");
Call the MDB manager class object updateDatabase passing the schema file name and if any the schema variables array.
The second parameter is of special importance. It defines the name of the file to which the schema file currently being installed will be copied after a successful installation procedure.
The copied file will be used later when you want to update the schema of your database. Do not delete this file or else the database manager will not be able to determine what is the schema that was installed.
if(MDB::isError($success))
echo "Error: ".$success->getMessage()."\n";
If the install procedure failed, display the error to figure what went wrong.
if(count($manager->warnings)>0)
echo "WARNING:\n",implode($manager->getWarnings(),"!\n"),"\n";
?>
Even when the install procedure was successfully completed there might have been some issues that you should be warned.
MDB is meant for interfacing with DBMS using SQL. The database application developers are free to construct and execute the SQL queries that their applications may need to send and retrieve information from the database server.
The first thing you need to do to access to a database is to setup a connection with the DBMS server within your application scripts.
A database connection is set by calling the factory &MDB::connect(). This factory does not start a database connection right away. It just sets a few parameters that the DBMS driver needs to know how to communicate with the database server you want. Usually the database connection is only effectively established later when you execute the first query of each of your scripts.
The &MDB::connect() factory takes as argument an associative array with connection options. The array entry phptype is required and must contain the designation of the driver that will handle the calls to interface with the type of DBMS that you talk to. For instance, if you want to connect to a MySQL DBMS server, set the phptype entry to mysql.
Other arguments array entry may also have to be specified depending on the driver type that you have chosen. Check the MDB manual to know the designation of the types of other supported DBMS and the respective additional arguments.
The second argument of the &MDB::connect() factory takes a reference of a variable that will be used to store a database access handle value. This handle value is important because it has to be passed to all MDB methods that are needed to access the database.
The &MDB::connect() factory may fail for inconsistency of the argument values that you may have specified. In case of failure it will return a non-empty string that describes the error that made it fail. Make sure you always verify this return value at least when you are debugging your database applications.
Here follows an example of usage of the &MDB::connect() factory:
<?php
require_once("MDB.php");
Include the necessary files.
$mdb=&MDB::connect((array(
"phptype"=>"mysql",
"username"=>"mysql_user_name",
"password"=>"mysql_user_password"
));
if (MDB::isError($mdb)) {
die ($mdb->getMessage());
}
The MDB::connect() creates an object of the driver class to access the database and initializes some variables.
$mdb->setDatabase("test");
?>
Before you can access to a database server you usually need to specify the name of the database you want to access using the setDatabase method.
MDB provides two ways of constructing and executing database queries: direct queries and prepared queries.
Direct queries are those that are executed simply by passing the SQL statement to the DBMS. To execute a direct query use the method query(). This method takes a database access handle and the SQL query statement as arguments. Here follows an example:
$query="SELECT name,password FROM users";
$result=$mdb->query($query);
The return value is either a result set or an MDB error object. If MDB::isError($result) is TRUE then the query failed. For SELECT queries the return value is a result handle. It should be used to retrieve the query results.
Query statements often contain constant values like in following example:
$query="SELECT name,password FROM users WHERE user_name='admin'";
$result=$mdb->query($query);
You may build queries like this by manually inserting constant values in the query statement text string. However, different DBMS may require that constant values be represented in different manners.
For instance, text constant values may need to escape special characters like '. Date constant values may be represented as integers rather than date text strings because the underlying DBMS does not support date data types natively.
Fortunately, MDB provides a set of conversion methods that avoid having you to pass constant values in a format that is suitable for the type of DBMS you are talking too. These methods call the respective driver methods to do any representation conversion that may be needed.
This way you may develop highly portable database applications without having to worry with the different types of constant value conversions that have to be performed when interfacing with different DBMS.
For each data type supported by MDB there is a constant value conversion method, except for integer constants because these are not likely to be represented differently between DBMS. Here is a complete list of constant values conversion methods:
Using the constant value conversion methods for the execution of the query mentioned above, it would be rewritten like this:
$query="SELECT name,password FROM users WHERE user_name=".$mdb->getTextValue("admin");
$result=$mdb->query($query);
Converting constant values before executing queries requires a little extra effort, but considering the flexibility that is gained from the extra portability of your application code, it is an effort that it is worthy and it is thoroughly recommended to you make it.
Prepared queries are SQL statements that have to be prepared before being executed. Executing a prepared query takes less time than executing a non-prepared query because prepared queries have already been parsed by the time they are executed.
Using prepared queries to execute them only once in a script does not take less time than just using direct queries. Prepared queries are recommended when you need to execute a query more than once in the same script.
Prepared queries may take arguments. When a prepared query is executed argument values are inserted in special spots in the query statement marked as with ?. Consider for instance this example that uses a direct query:
$query="SELECT name,password FROM users WHERE user_name=".$mdb->getTextValue("admin");
$result=$mdb->query($query);
It may be rewritten using a prepared query like this:
$query="SELECT name,password FROM users WHERE user_name=?";
$prepared_query=$mdb->prepareQuery($query);
Get a prepared query handle.
if(!MDB::isError($prepared_query))
Make sure the query was prepared without error.
{
$mdb->setParamText($prepared_query,1,"admin");
Set the first query argument with the text constant value admin.
$result=$mdb->executeQuery($prepared_query);
}
Execute the query. From here on handling query results is the same as for direct queries.
Prepared queries may have many arguments, all identified by the ? mark. Each one has an order number starting from 1.
There is one method to set prepared query arguments for each data type supported by MDB. In the example setParamText() was used to set a text argument. setParamInteger() would have to be used if the argument was an integer, and so on. If the argument is meant to be set to NULL, use the method setParamNull().
Prepared queries may be executed multiple times in the same script and you may change all or part of the argument values before executing them each time.
After you have executed all the times you need a prepared query in a script, you have to free the resources that it implicitly allocates by calling the method freePreparedQuery().
Despite the use of prepared queries is mostly meant for scripts where you need to execute the same query more than once, you may find it cleaner to also use them even when they are only executed once, as you only start with constant query strings and constant data value conversion is transparently handled for you by the setParam() like methods.
A successfully executed SELECT returns a value that should be used as result handle. This value must be passed to every MDB method that you may need to call to access the results return by the queries that your database applications execute.
A result handle is just a number that serves as a reference to a result set returned by the DBMS to the application as response to the query that was executed.
A result set is a sort of table with columns and rows filled with result data. You can retrieve the results using the fetch*() the family of methods (like fetchRow()).
Despite the rows of a result set are not always made available to the database application all at once, MDB lets you request result data by specify the number of the respective result set row. Result set row numbers start from 0.
Here is simple example of a retrieving the results of a row of a query:
$result=$mdb->query("SELECT name,email FROM users");
if(!MDB::isError($result))
{
$data=$mdb->fetchRow($result);
$name=$data[0];
$email=$data[1];
}
else
$echo=$result->getMessage();
The fetch*() method retrieves data just as it is returned by the DBMS. The data representation format for each type of field may vary from DBMS to DBMS.
To avoid the problem of having to handle data representation format differences in your database application, MDB provides a set of methods that fetch the result data and already convert it to a single representation format defined for each supported data type.
This simplifies database application development a lot and promotes code portability when using different DBMS.
Not all types of result data need to be converted. For instance, text and integer result data do not need to be converted. You can either use convertResult($value, $type) to convert result data. Optionally you can pass an array to query() and prepareQuery() that contains the types in the result set. Then the type conversion is handled automatically for you.
When you are done with the result set of a query, you should make sure that any resources implicitly allocated for it are freed. Freeing allocated resources is important because it reduces the use of memory that a script needs to run by allowing that memory may be reused.
Even if your script exits right after you are done with the result of a query, you should always explicitly free query results because the end of a script may not imply that the resources are freed automatically then. This is particularly true if you are using persistent database connections when running PHP as Web server module.
With MDB you should use the freeResult() method and it may be as simple as follows:
$mdb->freeResult($result);
Large object fields, also known as LOBs (BLOBs/CLOBs), need to have a special treatment. The amount of data stored in this type of fields may be so large that it would require too much memory to be stored and retrieved all at once with single method calls like with other types of fields.
MDB provides a separate set of API methods to deal with large object fields. These methods let applications deal with the values of this type of fields by splitting them in smaller chunks of data.
Large object fields may be created like any other type of field, that is declaring them in schema description files as large object fields.
There are two types of large object fields: character fields and binary fields. Character fields could be used when you only intend to store ASCII text on them. If you intend to store other types of data, use binary fields instead.
Character large object fields should declared of being of the type clob. Binary large object fields should declared of being of the type blob. Here follows an example of declaration of a table with a character and a binary large object fields:
<table>
<name>files</name>
<declaration>
<field> <name>id</name> <type>integer</type> </field>
<field> <name>document</name> <type>clob</type> </field>
<field> <name>picture</name> <type>blob</type> </field>
</declaration>
</table>
With MDB, storing data in large object fields can only be done by executing INSERT or UPDATE prepared queries. The large object values are passed to the database as parameters of the prepared queries. The methods setParamClob and setParamBlob should be used to specify large object values as parameters of the prepared query.
Instead of specifying the large object parameters explicitly by their data values, you need to pass references to objects of special handler classes that know how to retrieve the data to store in the large object fields.
The handler classes may be able to retrieve data from strings of defined programmatically like any other type of field, but may also retrieve data from files.
Here follows an example of how to execute a query that inserts data defined programmatically into a large object character field:
if(($prepared_query=$mdb->prepareQuery("INSERT INTO files (id,document,picture) VALUES (1,?,NULL)")))
{
$character_lob=array(
"Error"=>"",
"Data"=>"a lot of character data"
);
if(!MDB::isError($clob=$mdb->createLob($character_lob)))
{
$mdb->setParamClob($prepared_query,1,$clob,"document");
if(!MDB::isError($error = $mdb->executeQuery($prepared_query))
echo $error->getMessage();
$mdb->destroyLob($clob);
}
else
echo $clob->getMessage();
$mdb->freePreparedQuery($prepared_query);
}
else
echo $prepared_query->getMessage();
Here follows an example of how to execute a query that updates a large object binary field with data retrieved from a file. Make sure that only one row is affected by the query because some DBMS are not able to update values of large object fields in more than one row per query.
if(!MDB::isError($prepared_query=$mdb->prepareQuery(,"UPDATE files SET picture=? WHERE id=1")))
{
$binary_lob=array(
"Error"=>"",
"FileName"=>"my_image.gif"
);
if(!MDB::isError($blob=$mdb->createLob($binary_lob)))
{
$mdb->setParamBlob($prepared_query,1,$blob,"picture");
if(MDB::isError($error = $mdb->executeQuery($prepared_query)))
$mdb->destroyLOB($blob);
echo $error->getMessage();
}
else
echo $blob->getMessage();
$mdb->freePreparedQuery($prepared_query);
}
else
echo $prepared_query->getMessage();
Retriving data from large object fields is done by executing normal SELECT queries.
The methods fetchClob and fetchBlobt return a value that identifies a large object handler class object. The data may be retrieved using the method readLob. Alternatively, the handler object identifier may be passed to another handler class object that will read the large object result value and process it in a useful way, like for instance storing the data in a file.
Here follows an example of how to select a character large object field to output its data:
if(($result=$mdb->query("SELECT document FROM files WHERE id=1")))
{
if($mdb->endOfResult($result))
echo "No rows where returned.\n";
else
{
$clob=$mdb->fetchClob($result,0,"document");
if(!MDB::isError($clob))
{
while(!$mdb->endOfLOB($clob))
{
if(MDB::isError($error = $mdb->readLob($clob,$data,8000)<0))
{
echo $error->getMessage();
break;
}
echo $data;
}
$mdb->destroyLob($clob);
}
else
echo $clob->getMessage();
}
$mdb->freeResult($result);
}
else
echo $result->getMessage();
Here follows an example of how to select a binary large object field and output its data to a file.
if(($result=$mdb->query("SELECT picture FROM files WHERE id=1")))
{
if($mdb->endOfResult($result))
echo "No rows where returned.\n";
else
{
$binary_lob=array(
"Type"=>"outputfile",
"Result"=>$result,
"Row"=>0,
"Field"=>"picture",
"Binary"=>1,
"Error"=>"",
"FileName"=>"my_image.gif"
);
if(!MDB::isError($blob=$mdb->createLOB($binary_lob)))
{
if(MDB::isError($error = $mdb->readLob($blob,$data,0)<0))
{
echo $error->getMessage();
}
$mdb->destroyLob($blob);
}
else
echo $blob->getMessage();
}
$mdb->freeResult($result);
}
else
echo $result->getMessage();
If for some reason you decide that you need to update the schema of your database, the procedure to install your schema changes is as simple as install a database schema for the first time.
In fact all you need to do after you edit and change your schema file is to execute the same script like that you used to initially install the database.
The database manager class will look for the copy of the previously installed schema file. Both the newer and the previous installed schema descriptions are parsed and compared to build the list of changes.
Then it will attempt to install the requested changes without affecting the data that was stored in the database after the database was installed for the first time.
Some DBMS MDB drivers are not able to implement all sorts of changes. If you asked for any changes that the are not possible to install at once, the updateDatabase method will fail without affecting the database.
The attempt to change the database is safe at this level but it is always safer to make a backup of your database data before installing any changes as for some unexpected reason the DBMS server might fail.