| Subject: |
Create table and insert all 50 states into database with one query |
| Level: |
Beginner |
| Code Lines: |
55 |
| Requirements: |
PHP web server, MySQL server, phpMyAdmin |
Creating forms with state locations? Use this query to save you time from having to input all 50 states. Run the following query in your MySQL manager (like phpMyAdmin):
CREATE TABLE `state` (
`stateid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`abbr` VARCHAR( 100 ) NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL
) TYPE = MYISAM ;
INSERT INTO state (abbr, name) VALUES ('AL', 'Alabama');
INSERT INTO state (abbr, name) VALUES ('AK', 'Alaska');
INSERT INTO state (abbr, name) VALUES ('AZ', 'Arizona');
INSERT INTO state (abbr, name) VALUES ('AR', 'Arkansas');
INSERT INTO state (abbr, name) VALUES ('CA', 'California');
INSERT INTO state (abbr, name) VALUES ('CO', 'Colorado');
INSERT INTO state (abbr, name) VALUES ('CT', 'Connecticut');
INSERT INTO state (abbr, name) VALUES ('DE', 'Delaware');
INSERT INTO state (abbr, name) VALUES ('FL', 'Florida');
INSERT INTO state (abbr, name) VALUES ('GA', 'Georgia');
INSERT INTO state (abbr, name) VALUES ('HI', 'Hawaii');
INSERT INTO state (abbr, name) VALUES ('ID', 'Idaho');
INSERT INTO state (abbr, name) VALUES ('IL', 'Illinois');
INSERT INTO state (abbr, name) VALUES ('IN', 'Indiana');
INSERT INTO state (abbr, name) VALUES ('IA', 'Iowa');
INSERT INTO state (abbr, name) VALUES ('KS', 'Kansas');
INSERT INTO state (abbr, name) VALUES ('KY', 'Kentucky');
INSERT INTO state (abbr, name) VALUES ('LA', 'Louisiana');
INSERT INTO state (abbr, name) VALUES ('ME', 'Maine');
INSERT INTO state (abbr, name) VALUES ('MD', 'Maryland');
INSERT INTO state (abbr, name) VALUES ('MA', 'Massachusetts');
INSERT INTO state (abbr, name) VALUES ('MI', 'Michigan');
INSERT INTO state (abbr, name) VALUES ('MN', 'Minnesota');
INSERT INTO state (abbr, name) VALUES ('MS', 'Mississippi');
INSERT INTO state (abbr, name) VALUES ('MO', 'Missouri');
INSERT INTO state (abbr, name) VALUES ('MT', 'Montana');
INSERT INTO state (abbr, name) VALUES ('NE', 'Nebraska');
INSERT INTO state (abbr, name) VALUES ('NV', 'Nevada');
INSERT INTO state (abbr, name) VALUES ('NH', 'New Hampshire');
INSERT INTO state (abbr, name) VALUES ('NJ', 'New Jersey');
INSERT INTO state (abbr, name) VALUES ('NM', 'New Mexico');
INSERT INTO state (abbr, name) VALUES ('NY', 'New York');
INSERT INTO state (abbr, name) VALUES ('NC', 'North Carolina');
INSERT INTO state (abbr, name) VALUES ('ND', 'North Dakota');
INSERT INTO state (abbr, name) VALUES ('OH', 'Ohio');
INSERT INTO state (abbr, name) VALUES ('OK', 'Oklahoma');
INSERT INTO state (abbr, name) VALUES ('OR', 'Oregon');
INSERT INTO state (abbr, name) VALUES ('PA', 'Pennsylvania');
INSERT INTO state (abbr, name) VALUES ('RI', 'Rhode Island');
INSERT INTO state (abbr, name) VALUES ('SC', 'South Carolina');
INSERT INTO state (abbr, name) VALUES ('SD', 'South Dakota');
INSERT INTO state (abbr, name) VALUES ('TN', 'Tennessee');
INSERT INTO state (abbr, name) VALUES ('TX', 'Texas');
INSERT INTO state (abbr, name) VALUES ('UT', 'Utah');
INSERT INTO state (abbr, name) VALUES ('VT', 'Vermont');
INSERT INTO state (abbr, name) VALUES ('VA', 'Virginia');
INSERT INTO state (abbr, name) VALUES ('WA', 'Washington');
INSERT INTO state (abbr, name) VALUES ('WV', 'West Virginia');
INSERT INTO state (abbr, name) VALUES ('WI', 'Wisconsin');
INSERT INTO state (abbr, name) VALUES ('WY', 'Wyoming');
This script does the following steps:
1. The table name state will be created.
2. The three fields are created:
stateid - as an integer field, it's also the primary key of the table
abbr - as a char field, storing the states two letter abbreviation
name - as a char field, storing the name of the state
3. Data for 50 states are inserted into the table.
|
