how to make an INSERT of several countries

Question

how to make an INSERT of several countries

Navigation

#1 by (2 votes)
#2 by (2 votes)

4

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')
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;");

    $insert_country=mysqli_query($conexion,"
       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?

Thanks!

mysql php insert

asked by andy gibbs 16.09.2018 в 01:26

source

2 answers

2

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)
VALUES
    ('AF', 'Afganistán'),
    ('AL', 'Albania'),
    ('DZ', 'Algeria') 
    ...

answered by 16.09.2018 в 18:53

Advantages JQuery versus Javascript Learning promises, does not execute sequentially

score 2 · Accepted Answer

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

INSERT INTO app_countries 
VALUES
(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