Inserting both Key and value of a multi-dimensional array into mysql table

Question

I have an array that looks like this

Array
(
    [0] => Array
        (
            [post_id] => 5410
            [Issue] => 201
            [volume] => 2
            [pages] => 105-9
            [authors] => Onger, M., Jaluth, K.
            [publication_date] => 17 January 2016
            [journals] => Nature Medicine
            [link_to_pubmed] => http://www.ncbi.nlm.nih.gov/pubmed/1
        )
    [1] => Array
        (
            [post_id] => 5411
            [Issue] => 301
            [volume] => 7
            [pages] => 32-9
            [authors] => Onger, M., Jaluth, K.
            [publication_date] => March 30
            [journals] => Lancet
            [link_to_pubmed] => http://www.ncbi.nlm.nih.gov/pubmed/2
        )
        )

I have been cracking my head the whole day trying to figure out how i can insert both the key and the value to get something that looks like this

+---------+---------+-------------------+-----------------------+
| meta_id | post_id | meta_key          | meta_value            |
+---------+---------+-------------------+-----------------------+
|  190143 |    5410 |Issue              | 201                   |
|  190141 |    5410 |volume             | 2                     |
|  190140 |    5410 |pages              | 105-109               |
|  190139 |    5410 |authors            | Onger, M., Jaluth, K. |
|  190144 |    5410 |publication_date   | 17 January 2016       |
|  190136 |    5410 |journals           | Nature Medicine       |
|  190135 |    5410 |link_to_pubmed     |                       |
|         |         |                   |                       |
+---------+---------+-------------------+-----------------------+

The code i have used is very similar as the one described here and can be seen below

    $metadata = implode(', ', array_shift($publications));
foreach($publications as $publication){
    foreach ($publication as $key => $metadata){
        $key = array_keys($publication);
        //$publication[$key];
        }
 $new_metadata[] = "(" . implode(', ', $publication) . ")";

}

how do i get the key and values?

Couple of `foreach`es with an INSERT nested in them, simplez. What specific part are you stuck with? — Jonnix, Jun 13 '16 at 13:22
@splash58 i have edited the post/question for a snapshot of the code — tapeli, Jun 13 '16 at 13:50

splash58 · Accepted Answer · 2016-06-13T14:50:22.200

2

If meta_id is an autoincrement field, then

foreach($publications as $publication) {
    // Get post_id and remive it from array
    $post_id = $publication['post_id'];
    unset($publication['post_id']);
    foreach ($publication as $key => $metadata) {
       // Below a query. I only print it, you need to execute it
       echo $query = 'insert into table (post_id, meta_key, meta_value) 
                      values ($post_id, "' . $key . '",  "' . $metadata . '")';
        }
}

edited Jun 13 '16 at 14:50

answered Jun 13 '16 at 14:25

splash58

26,043
3
22
34

brilliant stuff @splash58. Thats what i was looking for.I never thought of a solution in the same way you thought.....Thanks! – tapeli Jun 13 '16 at 16:33

score 0 · Answer 2 · answered Jun 13 '16 at 13:28

0

You can't do it with 1 query. You have to run the loop of the array, and in each line run INSERT mysql.

answered Jun 13 '16 at 13:28

troiweb

1
2

Inserting both Key and value of a multi-dimensional array into mysql table

2 Answers2