database operation in drupal 7

Posted by bput4all on November 1, 2012 · Leave a Comment 

there are now different Drupal functions for each query type:

• SELECT – use db_query
• INSERT – use db_insert
• UPDATE – use db_update
• DELETE – use db_delete

Note that you can also execute some of these queries with the drupal_write_record function, but I’ve been having problems getting that to work, so I’m using these functions instead.

Given that background, here is my Drupal 7 SQL cheat sheet, with SQL SELECT, INSERT, UPDATE, and DELETE examples.

Drupal 7 SQL SELECT example – one row returned

Here’s one way to execute a Drupal 7 SQL SELECT statement with the db_query function:

$q = "SELECT * FROM {projects} WHERE id = :id AND user_id = :uid";
$project = db_query($q, array(':id' => $id, ':uid' => $uid))
           ->fetchObject();

# returns an object with fields corresponding to the names
# of your database table, which you access like
# $project->name, $project->description, and so on.

Because I’m limiting this query by the ‘id’ field, I know I’m only going to get one record back. (In fact it may fail to find any records, but I’m trying to keep this simple.)

As a better example of how db_query() returns an object, here’s another example:

# make sure the user owns the project id
$q = "SELECT user_id FROM {projects} WHERE id = :project_id";
$result = db_query($q, array(':project_id' => $project_id))->fetchObject();
echo 'user_id is ' . $result->user_id;

As you can see, I treat $result as an object after the query, knowing that it will have a field named user_id, because that’s the name of the database table field I’m asking for in my query.

Drupal 7 SQL SELECT example – multiple rows returned

Here’s how you issue a Drupal 7 SQL SELECT query with db_query and handle the results when you are expecting many rows to be returned:

$q = "SELECT * FROM {projects} WHERE uid = :uid";
$result = db_query($q, array(':uid' => $uid));
foreach ($result as $row) {
  // loop through your result set, working with $row here
}

Drupal 7 SQL INSERT with db_insert

How to execute a SQL INSERT with db_insert:

$id = db_insert('projects')
    ->fields(array(
        'user_id' => $uid,
        'project_type' => $project_type,
        'name' => $name,
        'description' => $description,
        'last_updated' => $timestamp,
        'date_created' => $timestamp
    ))
    ->execute();

With a SQL INSERT like this, the db_insert function returns the value of the SERIAL (auto_increment) field, which is very nice.

Drupal 7 SQL UPDATE with db_update

How to execute a Drupal 7 SQL UPDATE with db_update:

db_update('projects')
	->fields(array(
	        'project_count_type' => $project_count_type,
	        'name' => $name,
	        'description' => $description,
	        'last_updated' => $timestamp,
	        )
	)
	->condition('id', $id)
	->execute();

Drupal 7 SQL DELETE with db_delete

How to execute a Drupal 7 SQL DELETE with db_delete:

db_delete('projects')
  ->condition('id', $id)
  ->execute();

Filed under drupal, php · Tagged with delete row in drupal 7, drupal 7 database quering, insert in dupal 7, update row in drupal 7