SQL insert country names from table

Question

How can I insert country names from table wp_agent_adds into table wp_agent_target_countries when field wp_agent_adds.countries has x country names per field separated with a ',' comma.

Means this should be the result after the insert:

id = AUTO_INCREMENT
add_id = 2
meta_key = 'country'
meta_value = 'Netherlands'
id = AUTO_INCREMENT
add_id = 2
meta_key = 'country'
meta_value = 'Germany'
id = AUTO_INCREMENT
add_id = 2
meta_key = 'country'
meta_value = 'Polen'


See examples below.

Source Table wp_agent_adds.countries:

id  user_id countries
2   2       Netherlands,Germany,Polen
3   3       Netherlands
4   1       Netherlands,Belgium,Austria,Switzerland
7   100     Netherlands,Germany,Belgium

Target table:

wp_agent_target_countries (
  id int(12) NOT NULL AUTO_INCREMENT,
  add_id int(12) NOT NULL,
  meta_key varchar(64) NOT NULL,
  meta_value varchar(64) NOT NULL) ;

"field wp_agent_adds.countries has x country names per field separated with a ',' comma" - is there any chance of improving that so that it has (user_id, country) instead of (user_id, countries)? I.e. split out the countries into one per row? — Andrew Morton, Oct 10 '17 at 11:08
the situation is as described above. If there was a change to split each word it would not be a thread. then it is record by record updating. — jtermaat, Oct 10 '17 at 11:10
there are 3 possible ways to do it in mysql(afaik). 1) you can use cursor over countries and break each country and insert into target_countries. 2) create cursor to do same work as last point. 3) create temp table with all country name, apply join with it and insert it into target_countries. — surya singh, Oct 10 '17 at 11:16
@user118779 Sometimes it happens that a developer gets a chance to correct a mistake such as you have shown. It appears that you are unlucky and need to look up how to [MySQL: Split comma separated list into multiple rows](https://stackoverflow.com/q/3936088/1115360) and perhaps the "Related" articles on the same page. — Andrew Morton, Oct 10 '17 at 11:17
[https://gist.github.com/avoidwork/3749973] this is might also help — surya singh, Oct 10 '17 at 11:31

score 0 · Answer 1 · answered Oct 10 '17 at 11:50

MySQL does not provide any function to split a string. However, it is possible to do so with a recursive stored procedure.

CREATE PROCEDURE PROCESS_COUNTRIES(IN user_id INT UNSIGNED, IN countries VARCHAR(255))
BEGIN
  DECLARE comma_position INT UNSIGNED;
  DECLARE cur_country VARCHAR(255);
  DECLARE countries_left VARCHAR(255);
  SET max_sp_recursion_depth=10;

  SELECT LOCATE( ',', countries ) INTO comma_position;

  IF comma_position > 0 THEN
    SELECT SUBSTRING( countries, 1, comma_position-1 ) INTO cur_country;
    SELECT SUBSTRING( countries, comma_position+1) INTO countries_left;

    INSERT INTO wp_agent_target_countries( add_id,meta_key,meta_value) VALUES( user_id, 'country', cur_country);
    CALL PROCESS_COUNTRIES( user_id, countries_left);
  ELSE
    INSERT INTO wp_agent_target_countries( add_id,meta_key,meta_value) VALUES( user_id, 'country', countries);
  END IF;
END //

The procedure above recursively iterate over each country of the string and insert them into wp_agent_target_countries.

Note that calling this procedure is not necessarly easy, you may have to use a cursor if you want to call it on each entry of your table, which is bad performance.

You may consider managing this in your code, or change your table structure to have one entry by country in wp_agent_adds.

Tested in SQLFiddle: http://sqlfiddle.com/#!9/7e4872/1/0

M: I tested this in phpmyadmin without the creation of table be course they already exist. But I get error: SQL-query: CREATE PROCEDURE PROCESS_COUNTRIES( IN id INT UNSIGNED, IN countries VARCHAR( 255 ) ) BEGIN DECLARE comma_position INT UNSIGNED; MySQL meldt: Documentatie #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 — jtermaat, Oct 10 '17 at 12:13
This is because of the delimiter. The script I posted in example uses the default SQLfiddle delimiter "//", but in your script you have to add: "DELIMITER //" before the procedure, and "DELIMITER ;" after — Matthieu.V, Oct 11 '17 at 11:18
Thread is closed. PHP solution was just what I needed simpler and easier then SQL (in this case). Thanks for you ideas and time — jtermaat, Oct 11 '17 at 11:47

score 0 · Accepted Answer · answered Oct 10 '17 at 19:46

In PHP you can do it like this:

$query = "SELECT id, countries FROM wp_agent_adds ORDER BY id";
$result = $wpdb->get_results($query);

foreach($result as $row) {
$cntr = explode(',', $row->countries);      
foreach($cntr as $name){
    if(!empty($name) || $name !='' || !isset($name) ) {             
        $inserted = $wpdb->insert($wpdb->prefix.'agent_target_countries', 
            array( 
                'add_id'        => $row->id,
                'meta_key'      => 'country',
                'meta_value'    => $name
            ),array('%d','%s','%s'  
            ) 
        );
    }
    if($inserted){
        echo 'done';
    }
}
}

SQL insert country names from table

2 Answers2