Helpful SQL Queries Commands For Drupal

Simplifying mass updates and changes to websites is one of the main reasons to use a database driven content management system like drupal.  While drupal has a variety of helpful tools for automating tasks there are many times where it is easier and some where it is your only option to make changes to the database directly.  Below is a list of mysql commands that we use occasionally to create changes and make updates to our sites.  They are here largely for reference, please do not use them without knowing what they mean and without a backup of your database.

  • General Search and Replace Function
  • UPDATE tablename SET tablefield = replace(tablefield,”findstring”,”replacestring”) WHERE type = “x”
  • Example:  Create mass changes in drupal node body fields

    UPDATE `node_revisions` SET `body` = replace(body,’texttofind’,’texttoreplacewith’)

 

  • Create a spreadsheet of your drupal users usernames, emails and uid (we use this at times to create email newsletter subscription lists as an example)
  • This should be done in phpmyadmin so you can easily export the results in a CSV file for import, say into Mailchimp…
  • Example:  Select the desired user information from users with a specific role
  • SELECT users.name, users.uid, users.mail FROM (users LEFT JOIN users_roles ON users.uid = users_roles.uid) WHERE users_roles.rid = “3” ORDER BY users.uid