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` = %vidIf 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
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
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
I added the queries for this to work in Drupal 7.
-------
MontanaProgrammer.com
The Brick Factory
Correction to D7 2nd Query
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.