mysql inserting data to another

Question

I have following mysql db table structure:

Table_1:

I need to insert the values (post_id and its corresponding geo_lat and geo_lon into another table as below)

Table_2:

This is what I have so far:

$query = "INSERT INTO table_2(post_id,geo_lat,geo_lon)
          SELECT post_id,meta_key IN(geo_lat),meta_key IN(geo_lon) //????
          FROM table_1";
$wpdb->query($query);

Of course it is wrong and I am bit stuck to how to do it (pretty new to php).

Could someone help me with this?

Thanks.

Answer 1

You can use a GROUP BY and MAX together to "rotate" these values:

INSERT INTO table_2 (Post_id, Geo_lat, Geo_lon)
     SELECT post_id,
            MAX(IF(meta_key='geo_lat',meta_value,NULL)),          
            MAX(IF(meta_key='geo_lon',meta_value,NULL))
       FROM table_1
   GROUP BY post_id

It is kind of self-explanatory, but if you want to understand better what this query does you can experiment with a simpler SELECT statement:

SELECT post_id,
       IF(meta_key='geo_lat',meta_value,NULL) lat,
       IF(meta_key='geo_lon',meta_value,NULL) lon
  FROM table_1

What this does is to return a three column set, first is the ID, second column (named lat here) will bring the meta_value only if meta_key in the row is geo_lat, otherwise NULL. Third column is the same, but for geo_lon. It will generate a set like:

+---------+---------+---------+
| post_id | lat     | lon     |
+---------+---------+---------+
| 1       | 12.400  | NULL    |
| 1       | NULL    | 123.000 |
| 2       | 234.200 | NULL    |
| 2       | NULL    | 4.200   |
+---------+---------+---------+

Then, with the use of GROUP_BY rows will be grouped when the ID is the same, and the values used on lat and lon will be decided by MAX. Since NULL is always "smaller", only non-null values will be selected effectifelly joining the rows into:

  SELECT post_id,
         MAX(IF(meta_key='geo_lat',meta_value,NULL)) lat,
         MAX(IF(meta_key='geo_lon',meta_value,NULL)) lon
    FROM table_1
GROUP BY post_id

+---------+---------+---------+
| post_id | lat     | lon     |
+---------+---------+---------+
| 1       | 12.400  | 123.000 |
| 2       | 234.200 | 4.200   |
+---------+---------+---------+