A Drupal 7 db_insert query (SQL INSERT statement)

Drupal 7 database FAQ: How do I use the Drupal dbquery function to perform a SQL INSERT? (Or, what is the Drupal 7 dbinsert syntax?)

I didn't type db_query in that question by mistake. I just spent 45 minutes trying to use it for a Drupal 7 SQL INSERT, which of course I've now learned doesn't work.

In short, if you're looking for a Drupal 7 db_insert example that shows how to perform a SQL INSERT, and also happens to show some SQL Timestamp fields, here's a 'submit' function I'm currently writing:

function projects_add_submit($form, &$form_state) {
  # use t() or check_plain() on these string fields
  $project = new stdClass();
  $project->user_id = get_user_id();
  $project->project_count_type = $form_state['values']['type'];
  $project->name = $form_state['values']['name'];
  $project->description = $form_state['values']['description'];
  $timestamp = date('Y-m-d G:i:s');

  # new drupal 7 style sql insert
  $id = db_insert('projects')
      ->fields(array(
          'user_id' => $project->user_id,
          'project_count_type' => $project->project_count_type,
          'name' => $project->name,
          'description' => $project->description,
          'last_updated' => $timestamp,
          'date_created' => $timestamp  # use REQUEST_TIME or time() here if you want the date as a long
  ))
  ->execute();
  watchdog('sleetmute', "NID  = " . $id);

  $form_state['redirect'] = 'projects';
}

Although I'm not done with my work, that function works properly. For more information related to that db_insert statement, here's the database table it works with:

mysql> desc projects;
+--------------------+------------------+------+-----+---------------------+-----------------------------+
| Field              | Type             | Null | Key | Default             | Extra                       |
+--------------------+------------------+------+-----+---------------------+-----------------------------+
| id                 | int(10) unsigned | NO   | PRI | NULL                | auto_increment              |
| user_id            | int(10) unsigned | NO   | MUL | NULL                |                             |
| project_count_type | varchar(11)      | NO   |     | NULL                |                             |
| name               | varchar(64)      | NO   |     | NULL                |                             |
| description        | text             | YES  |     | NULL                |                             |
| last_updated       | timestamp        | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| date_created       | timestamp        | NO   |     | 0000-00-00 00:00:00 |                             |
+--------------------+------------------+------+-----+---------------------+-----------------------------+

And here's the current Drupal 7 form definition it works with:

function projects_add($form, &$form_state) {
	$form['description'] = array(
    '#type' => 'item',
    '#title' => t('Add a new project count'),
	);
	# the values for the dropdown box
	$form['type_options'] = array(
	  '#type' => 'value',
	  '#value' => array( 'APPLICATION' => t('Application'),
											 'ENHANCEMENT' => t('Enhancement'),
											 'OTHER' => t('Other'))
	);
	$form['type'] = array(
    '#title' => t('Project Type'),
	  '#type' => 'select',
    '#description' => "Select the project count type.",
	  '#options' => $form['type_options']['#value'],
    '#weight' => 10,
	);
	$form['name'] = array(
    '#type' => 'textfield',
    '#title' => t('Name'),
    '#required' => TRUE,
    '#description' => "Give the project a unique name.",
    '#size' => 20,
    '#maxlength' => 20,
    '#weight' => 20,
	);
	# TODO fix this, should be a textarea
	$form['description'] = array(
    '#type' => 'textarea',
    '#title' => t('Notes/Description'),
    '#description' => "Any notes you'd like to add about this project.",
    '#weight' => 30,
	);
	$form['submit'] = array(
    '#type' => 'submit',
    '#value' => 'Save',
    '#weight' => 40,
	);
	return $form;
}

It just took me about an hour to work through several different issues here -- mostly a lack of good documentation -- so I hope this will be helpful to anyone else trying to use the new Drupal 7 db_insert function (and understand the correct syntax).

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.