Last updated January 25, 2011. Created by Jody Lynn on March 23, 2006.
Edited by RandallKent, bekasu, sepeck, Owen Barton. Log in to edit this page.
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.
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.