Database Setup

Our CRUD module stores data in a simple countries table consisting of three columns: an auto-incrementing primary key, the country name, and a two-letter country code.

The SQL Schema

Create the table by running the following SQL statement:

CREATE TABLE countries (
    id int(11) NOT NULL,
    country_title varchar(255) NOT NULL,
    country_code varchar(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE countries
    ADD PRIMARY KEY (id);

ALTER TABLE countries
    MODIFY id int(11) NOT NULL AUTO_INCREMENT;

Three columns, one primary key, auto-increment on id. The country_code is a two-letter code such as "GB" or "FR". No unique constraint is placed on country_code - validation is handled at the application level if needed.

Seed Data

To make the module immediately useful, populate the table with a starter set of countries:

INSERT INTO countries (id, country_title, country_code) VALUES
(1, 'Australia', 'AU'),
(2, 'Belgium', 'BE'),
(3, 'Canada', 'CA'),
(4, 'Denmark', 'DK'),
(5, 'Finland', 'FI'),
(6, 'France', 'FR'),
(7, 'Germany', 'DE'),
(8, 'Ireland', 'IE'),
(9, 'Italy', 'IT'),
(10, 'Japan', 'JP'),
(11, 'Luxembourg', 'LU'),
(12, 'Monaco', 'MC'),
(13, 'Netherlands', 'NL'),
(14, 'New Zealand', 'NZ'),
(15, 'Norway', 'NO'),
(16, 'Portugal', 'PT'),
(17, 'Singapore', 'SG'),
(18, 'South Korea', 'KR'),
(19, 'Spain', 'ES'),
(20, 'Sweden', 'SE'),
(21, 'Switzerland', 'CH'),
(22, 'United Kingdom', 'GB'),
(23, 'United States', 'US');

You can also add your own countries through the admin interface once the module is running.

Running the SQL

You can run the SQL through phpMyAdmin, the MySQL command line, or any other database management tool. For the command line:

mysql -u root -p your_database_name < countries.sql

Replace your_database_name with the database configured in your Trongate project's config/database.php file.

Column Reference