Drupal 6: Creating database schema for a Drupal module and inserting database records
Here’s a quick guide on how to create database schema for your module. The first thing you should do is get acquainted with the Schema API documentation.
You’ll need to create a file in your module directory called: MYMODULE.install. In this file, you’ll define hook_schema, hook_install, hook_uninstall, etc. Here’s an example that defines 3 columns, the primary key, and a unique key.
<?php
function MYMODULE_schema() {
$schema = array();
$schema['MYTABLE'] = array(
'description' => t('MY TABLE DESCRIPTION'),
'fields' => array(
'MYCOLUMN1' => array(
'description' => t('MY UNIQUE IDENTIFIER'),
'type' => 'serial',
'unsigned' => true,
'not null' => true,
),
'MYVARCHAR' => array(
'description' => t('MY VAR CHAR'),
'type' => 'varchar',
'length' => 32,
'not null' => true,
),
'MYTIMESTAMP' => array(
'description' => t('MY TIMESTAMP'),
'type' => 'int',
'not null' => true,
),
),
'indexes' => array(
'MYVARCHAR' => array('MYVARCHAR'),
),
'primary key' => array('MYCOLUMN1'),
'unique keys' => array(
'MYCOLUMN1' => array('MYCOLUMN1'),
),
);
return $schema;
}
?>
A full list of data types can be found here. This documentation explains the structure of the schema array.
Now you can define how to install and uninstall the schema:
<?php
function MYMODULE_install() {
drupal_install_schema('MYMODULE');
}
function MYMODULE_uninstall() {
drupal_uninstall_schema('MYMODULE');
}
?>
More information on schema functions can be found here.
In your module code to write a database record you simply define an array/object containing your columns and the data you’d like to insert, and then use the function drupal_write_record:
<?php
$record = new StdClass();
$record->MYVARCHAR = 'BLAH';
$record->MYTIMESTAMP = strtotime('now');
drupal_write_record('MYTABLE', $record);
?>
More information on writing to the database can be found here.