loadModule()
method. Earlier in the tutorial, the Extended module was loaded like this:
$mdb2->loadModule('Extended');
After this call you have access to all the methods that the Extended module provides, such as all the get*()
methods. The methods are accessible through the extended
property of the $mdb2
instance:
$mdb2->extended->getAssoc($sql);
In addition to that, in PHP5, due to the object overloading functionality, you can access the methods directly as methods of the $mdb2
instance:
$mdb2->getAssoc($sql);
In this tutorial PHP5 was assumed, so all the calls to the module methods benefit from object overloading and are called using this short notation.
Yet another way to access the module's methods is by prefixing them with the short name of the module ex
(for "Extended"). This is also PHP5-only.
$mdb2->exGetAssoc($sql);
And finally, you can specify a custom property name to load the module into (works in both PHP 4 and 5):
$mdb2->loadModule('Extended', 'mine');
$mdb2->mine->getAssoc($sql);
The full list of currently available MDB2 modules is as follows (short access names given in brackets):
- Extended (ex)': You already have an idea of some of the methods available in the Extended module. This module is the only one unrelated to the different database drivers and its definition file (
Extended.php
) lies in the rootMDB2
directory, not in theDrivers
directory. This module is defined in theMDB2_Extended
class, which inherits theMDB2_Module_Common
class. - Datatype (dt)': Contains methods for manipulating and converting MDB2 data types and mapping them to types that are native to the underlying database.
- Manager (mg)': Contains methods for managing the database structure (schema), like creating, listing, or dropping databases, tables, indices, etc.
- Reverse (rv)': Methods for reverse engineering a database structure.
- Native (na)': Any methods that are native to the underlying database are placed here.
- Function (fc)': Contains wrappers for useful functions that are implemented differently in the different databases.
Let's see a few examples that use some of the modules.
Manager Module
Using the Manager module you have access to methods for managing your database schema. Let's see some of its methods in action.
Create a Database
Here's an example that will create a new blank database:
$mdb2->loadModule('Manager');
$mdb2->createDatabase('test_db');
Create a Table
You can use the Manager module to recreate the table people
that was used in the earlier examples in this article. This table had four fields:
-
id
: An unsigned integer primary key that cannot be null -
name
: A text field, likeVARCHAR(255)
in MySQL -
family
: Same type asname
-
birth_date
: A date field
To create this table you use the createTable()
method, to which you pass the table name and an array containing the table definition.
$definition = array ( 'id' => array ( 'type' => 'integer',
'unsigned' => 1,
'notnull' => 1,
'default' => 0,
),
'name' => array ( 'type' => 'text',
'length' => 255
),
'family' => array ( 'type' => 'text',
'length' => 255
),
'birth_date' => array ( 'type' => 'date'
)
);
$mdb2->createTable('people', $definition);
Alter Table
Let's say that after the table was created, you decide that 255 characters are too much for one name. In this case you'll need to set up a new definition array and call alterTable()
. The new definition array used for modifications is broken down into the following keys:
-
name
: New name for the table -
add
: New fields to be added -
remove
: Fields to be dropped -
rename
: Fields to rename -
change
: Fields to modify
Here's how to modify the name
field to store only a hundred characters:
$definition = array(
'change' => array(
'name' => array(
'definition' => array(
'length' => 100,
'type' => 'text',
)
),
)
);
$mdb2->alterTable('people', $definition, false);
If you set the third parameter of alterTable()
to true
, MDB2 will not execute the changes, but will only check if they are supported by your DBMS.
Constraints
The id
field was meant to be the primary key, but so far it isn't. For this purpose you can use the createConstraint()
method, which accepts the table name, the name we chose for the constraint, and the array containing the constraint definition.
$definition = array ( 'primary' => true,
'fields' => array ( 'id' => array())
);
$mdb2->createConstraint('people', 'myprimekey', $definition);
Note. Note that MySQL will ignore themyprimekey
name of the constraint, because it requires the primary key to always be namedPRIMARY
.
Now we can specify that the name plus the family name should be unique:
$definition = array('unique' => true,
'fields' => array('name' => array(),
'family' => array(),
)
);
$mdb2->createConstraint('people', 'unique_people', $definition);
On second thoughts, different people sometimes have the same names, so let's drop this constraint:
$mdb2->dropConstraint('people', 'unique_people');
Indices
If there will be a lot of SELECTs on the birth_date
field, you can speed them up by creating index on this field.
$definition = array('fields' => array('birth_date' => array(),) );
$mdb2->createIndex('people', 'dates', $definition);
Note that by default MDB2 will add a _idx
suffix to all your indices and constraints. If you want to modify this behavior, set the idxname_format
option:
$mdb2->setOption('idxname_format', '%s'); // no suffix
Listings
In the Manager module you have also a lot of methods to get information about a database, such as listDatabases(),
listUsers(),
listTables(),
listTableViews(),
and listTableFields().
Function Module
The Function module contains some methods to access common database functions, such as referring to the current timestamp, and concatenating or getting partial strings. If you want to access the current timestamp in your statements, you can use the now()
method and it will return you the means to get the timestamp in a way that is native for the currently underlying database system.
$mdb2->loadModule('Function');
echo $mdb2->now();
This will output CURRENT_TIMESTAMP
when using MySQL and datetime('now')
when using SQLite.
now()
accepts a string parameter (with values date
, time
, and timestamp
) that specifies if you want the current date, time, or both.
If you wish to concatenate strings in your statements in a database-agnostic way, you can use the concat()
method and pass an unlimited number of string parameters to it. For extracting substrings, you have the substring()
method. Here's an example that uses both methods:
$mdb2->loadModule('Function');
$sql = 'SELECT %s FROM people';
$first_initial = $mdb2->substring('name', 1, 1);
$dot = $mdb2->quote('.');
$all = $mdb2->concat($first_initial, $dot, 'family');
$sql = sprintf($sql, $all);
$data = $mdb2->queryCol($sql);
echo $sql;
print_r($data);
The print_r()
from this code will produce:
Array (
[0] => E.Vedder
[1] => M.McCready
[2] => S.Gossard
...
)
The echo
$sql;
line will print a different result, depending on the database driver you use. For MySQL it would be:
SELECT CONCAT(SUBSTRING(name FROM 1 FOR 1), '.', family) FROM people
Using the Oracle (oci8) driver, you'll get:
SELECT (SUBSTR(name, 1, 1) || '.' || family) FROM people
In this example only the first character from the value in the name
field was extracted. Then it was concatenated with the dot symbol and the full value of the family
field. Note that it was necessary to quote the dot in order for it to be treated as a string and not a field name.
Reverse Module
If you want to get information about the table people
that was used in the examples in this article, you can call the tableInfo()
method:
$mdb2->loadModule('Reverse');
$data = $mdb2->tableInfo('people');
If you print_r()
the result, you'll get something like:
Array( [0] => Array ( [table] => people
[name] => id
[type] => int
[length] => 11
[flags] => not_null primary_key
[mdb2type] => integer
)
[1] => Array ( [table] => people
[name] => name
[type] => char
[length] => 100
[flags] =>
[mdb2type] => text
)
...
)
The real magic about the tableInfo()
method is that it can return information on the fields based on a query result, not just a table. Let's say you have one more table phones
in your database that stores phone numbers of the people from the people
table and it looks like this:
id | person_id | Phone | |||
---|---|---|---|---|---|
1 | 1 | 555-666-7777 | |||
2 | 1 | 555-666-7788 |
You can execute a query that joins the two tables and when you get the result, you can pass it to tableInfo()
:
$mdb2->loadModule('Reverse');
$sql = 'SELECT phones.id, people.name, phones.phone ';
$sql.= ' FROM people ';
$sql.= ' LEFT JOIN phones ';
$sql.= ' ON people.id = phones.person_id ';
$result = $mdb2->query($sql);
$data = $mdb2->tableInfo($result);
Now if you print_r()
what tableInfo()
returns, you'll get an array that describes the three fields selected in the JOIN
statementid, name, and phone:
Array (
[0] => Array ( [table] => phones
[name] => id
[type] => int
[length] => 11
[flags] => primary_key
[mdb2type] => integer
)
[1] => Array ( [table] => people
[name] => name
[type] => char
[length] => 100
[flags] =>
[mdb2type] => text
)
[2] => Array ( [table] => phones
[name] => phone
[type] => char
[length] => 20
[flags] =>
[mdb2type] => text
)
)