how to make an INSERT of several countries


This situation has never occurred to me but there is always a first time.

The thing is that I have a field type select and I need to fill it with a list of countries, I already have the list but I have no idea how to do INSERT of this magnitude in MySQL.

In my list of countries there are some hundred or so or 200 countries and I want to execute that query in a single query because you can imagine how tedious it would be to insert them one by one XD.

I tried this but only the table was created, it did not insert:

$counry=mysqli_query($conexion,"CREATE TABLE 'apps_countries' (
   'id' int(11) NOT NULL auto_increment,
    'country_code' varchar(2) NOT NULL default '',
    'country_name' varchar(100) NOT NULL default '',
    PRIMARY KEY ('id')

       INSERT INTO 'apps_countries' VALUES (null, 'AF', 'Afghanistan');
       INSERT INTO 'apps_countries' VALUES (null, 'AL', 'Albania');
       INSERT INTO 'apps_countries' VALUES (null, 'DZ', 'Algeria');
//etc.... a houndred something countries remaining

Any help?


asked by andy gibbs 16.09.2018 в 01:26

2 answers


So your sentence so that it remains in a single INSERT should be as follows:

INSERT INTO app_countries 
(NULL, 'AF', 'Afganistán'),
(NULL, 'MX', 'México'),
(NULL, 'AR', 'Argentina'),
(NULL, 'SP', 'España');

That is, your query INSERT , you can pass multiple values to   insert only separated by commas and these same grouped between   parentheses, once you conluze it all you do is place ;   at the end as you can see in the example; in this way although you insert multiple values you will only execute a single INSERT

answered by 16.09.2018 / 01:32

There is another way to do it: by calling each column by its name.

By doing this we will have two advantages:

  • We will write a code where we will know at any time what we are inserting. Or what is the same, we gain clarity.
  • Since the column id is auto-incremental, we can omit it and at the same time save the NULL in the list of values, since the system will automatically assign each auto-incremental value.

The query would then be like this:

INSERT INTO app_countries 
    (country_code, country_name)
    ('AF', 'Afganistán'),
    ('AL', 'Albania'),
    ('DZ', 'Algeria') 
answered by 16.09.2018 в 18:53