Insert list of US states into a taxonomy

Last updated February 15, 2013. Created by Jody Lynn on March 23, 2006.
Edited by chrisroane, RandallKent, bekasu, sepeck. Log in to edit this page.

Note: See below to do this in D7.

This SQL query will add a list of US states to a particular taxonomy. You will need to search for the string %vid and replace with the actual numeric vid of your vocabulary (which you can get from the URL on /admin/taxonomy. You may wish to delete the initial 'National' entry.

INSERT INTO term_data VALUES
('', %vid, 'National', '', 0), 
('', %vid, 'Alabama', '', 0), 
('', %vid, 'Alaska', '', 0), 
('', %vid, 'Arizona', '', 0), 
('', %vid, 'Arkansas', '', 0), 
('', %vid, 'California', '', 0), 
('', %vid, 'Colorado', '', 0), 
('', %vid, 'Connecticut', '', 0), 
('', %vid, 'Delaware', '', 0), 
('', %vid, 'Florida', '', 0), 
('', %vid, 'Georgia', '', 0), 
('', %vid, 'Hawaii', '', 0), 
('', %vid, 'Idaho', '', 0), 
('', %vid, 'Illinois', '', 0), 
('', %vid, 'Indiana', '', 0), 
('', %vid, 'Iowa', '', 0), 
('', %vid, 'Kansas', '', 0), 
('', %vid, 'Kentucky', '', 0), 
('', %vid, 'Louisiana', '', 0), 
('', %vid, 'Maine', '', 0), 
('', %vid, 'Maryland', '', 0), 
('', %vid, 'Massachusetts', '', 0), 
('', %vid, 'Michigan', '', 0), 
('', %vid, 'Minnesota', '', 0), 
('', %vid, 'Mississippi', '', 0), 
('', %vid, 'Missouri', '', 0), 
('', %vid, 'Montana', '', 0), 
('', %vid, 'Nebraska', '', 0), 
('', %vid, 'Nevada', '', 0), 
('', %vid, 'New Hampshire', '', 0), 
('', %vid, 'New Jersey', '', 0), 
('', %vid, 'New Mexico', '', 0), 
('', %vid, 'New York', '', 0), 
('', %vid, 'North Carolina', '', 0), 
('', %vid, 'North Dakota', '', 0), 
('', %vid, 'Ohio', '', 0), 
('', %vid, 'Oklahoma', '', 0), 
('', %vid, 'Oregon', '', 0), 
('', %vid, 'Pennsylvania', '', 0), 
('', %vid, 'Rhode Island', '', 0), 
('', %vid, 'South Carolina', '', 0), 
('', %vid, 'South Dakota', '', 0), 
('', %vid, 'Tennessee', '', 0), 
('', %vid, 'Texas', '', 0), 
('', %vid, 'Utah', '', 0), 
('', %vid, 'Vermont', '', 0), 
('', %vid, 'Virginia', '', 0), 
('', %vid, 'Washington', '', 0), 
('', %vid, 'West Virginia', '', 0), 
('', %vid, 'Wisconsin', '', 0), 
('', %vid, 'Wyoming', '', 0);

Running the query above will populate the terms; however, in Drupal 6.x you also need to add initial values to the term_hierarchy table for each new tid created. Assuming you did not have any terms associated with this vocabulary, you can use the code below, once again replacing %vid for the correct vocabulary id used above.

INSERT INTO `term_hierarchy` (`tid`,`parent`)
SELECT `tid`,0 FROM `term_data` t WHERE t.`vid` = %vid

If you're using Drupal 4.x, you will also need to update the term_data_tid value in the sequences table. In Drupal 6.x this is not necessary since term_data.tid auto increments.

Whew. Anyone want to make a php snippet to do all this? ;-)

Consider Helpers Module

helpers module has a function that makes a list of states, in addition it has helper functions to use these lists in forms.

Drupal 7

Here is the query to run in Drupal 7 to do this.

INSERT INTO taxonomy_term_data VALUES
('', %vid, 'Alabama', '', NULL, 0), 
('', %vid, 'Alaska', '', NULL, 0), 
('', %vid, 'Arizona', '', NULL, 0), 
('', %vid, 'Arkansas', '', NULL, 0), 
('', %vid, 'California', '', NULL, 0), 
('', %vid, 'Colorado', '', NULL, 0), 
('', %vid, 'Connecticut', '', NULL, 0), 
('', %vid, 'Delaware', '', NULL, 0), 
('', %vid, 'Florida', '', NULL, 0), 
('', %vid, 'Georgia', '', NULL, 0), 
('', %vid, 'Hawaii', '', NULL, 0), 
('', %vid, 'Idaho', '', NULL, 0), 
('', %vid, 'Illinois', '', NULL, 0), 
('', %vid, 'Indiana', '', NULL, 0), 
('', %vid, 'Iowa', '', NULL, 0), 
('', %vid, 'Kansas', '', NULL, 0), 
('', %vid, 'Kentucky', '', NULL, 0), 
('', %vid, 'Louisiana', '', NULL, 0), 
('', %vid, 'Maine', '', NULL, 0), 
('', %vid, 'Maryland', '', NULL, 0), 
('', %vid, 'Massachusetts', '', NULL, 0), 
('', %vid, 'Michigan', '', NULL, 0), 
('', %vid, 'Minnesota', '', NULL, 0), 
('', %vid, 'Mississippi', '', NULL, 0), 
('', %vid, 'Missouri', '', NULL, 0), 
('', %vid, 'Montana', '', NULL, 0), 
('', %vid, 'Nebraska', '', NULL, 0), 
('', %vid, 'Nevada', '', NULL, 0), 
('', %vid, 'New Hampshire', '', NULL, 0), 
('', %vid, 'New Jersey', '', NULL, 0), 
('', %vid, 'New Mexico', '', NULL, 0), 
('', %vid, 'New York', '', NULL, 0), 
('', %vid, 'North Carolina', '', NULL, 0), 
('', %vid, 'North Dakota', '', NULL, 0), 
('', %vid, 'Ohio', '', NULL, 0), 
('', %vid, 'Oklahoma', '', NULL, 0), 
('', %vid, 'Oregon', '', NULL, 0), 
('', %vid, 'Pennsylvania', '', NULL, 0), 
('', %vid, 'Rhode Island', '', NULL, 0), 
('', %vid, 'South Carolina', '', NULL, 0), 
('', %vid, 'South Dakota', '', NULL, 0), 
('', %vid, 'Tennessee', '', NULL, 0), 
('', %vid, 'Texas', '', NULL, 0), 
('', %vid, 'Utah', '', NULL, 0), 
('', %vid, 'Vermont', '', NULL, 0), 
('', %vid, 'Virginia', '', NULL, 0), 
('', %vid, 'Washington', '', NULL, 0), 
('', %vid, 'West Virginia', '', NULL, 0), 
('', %vid, 'Wisconsin', '', NULL, 0), 
('', %vid, 'Wyoming', '', NULL, 0);

You will then want to run this code (otherwise no terms will show up under that vocabulary):

INSERT INTO `taxonomy_term_hierarchy` (`tid`,`parent`)
SELECT `tid`,0 FROM `term_data` t WHERE t.`vid` = %vid;

Comments

Drupal 6: I did it by

Posted by superxain on October 25, 2010 at 5:54am

Drupal 6: I did it by importing csv file containing state names with taxonomy_csv. For the states.csv and more details, see here: http://drupal.org/node/564250

My blog ( drupal and more):
http://www.o-learn.com

Extra step required for Drupal 6

Posted by lobo235 on December 3, 2010 at 9:23pm

Thank you very much for sharing the query above. After running that query on my Drupal 6 site when I went to the list terms page I didn't see any of the new terms but they were in the database. After a little digging around I was able to find out that the `term_hierarchy` table also needs some entries in order for the terms to show up in list terms. Here is the query I used to accomplish this. As with the query above, be sure to replace %vid with the vid of your States taxonomy vocabulary.

INSERT INTO `term_hierarchy` (`tid`,`parent`)
SELECT `tid`,0 FROM `term_data` t WHERE t.`vid` = %vid

This new query does an insert into the `term_hierarchy` table using the newly created rows from the original query. Apparently Drupal 6 needs to know that all the terms are directly under the parent (0). I hope this helps someone else out.

D7: Correct Query Code

Posted by chrisroane on February 15, 2013 at 9:23pm

I added the queries for this to work in Drupal 7.

-------
MontanaProgrammer.com
The Brick Factory

Correction to D7 2nd Query

Posted by lhugg on April 3, 2013 at 10:10pm

For Drupal 7, the 2nd query needs to be:
INSERT INTO `taxonomy_term_hierarchy` (`tid`,`parent`)
SELECT `tid`,0 FROM `taxonomy_term_data` t WHERE t.`vid` = %vid;

NOTE: the table in D7 is called 'taxonomy_term_data', not term_data.