How can I store values from a nested/multidimensional array in a MySQL database?

Question

My array looks like this:

Array  
(  
[0] => Array
    (
        [created_at] => Sat Jun 25 21:22:20 +0000 2011
        [text] => i'm eating apple
        [sender] => Array
            (
                [name] => mark o
                [created_at] => Wed May 28 18:21:03 +0000 2008
            )
        [recipient] => Array
            (
                [created_at] => Mon Jun 21 19:48:50 +0000 2010
                [screen_name] => playassassin
            )
        [sender_screen_name] => mark
        [recipient_screen_name] => james
    )

[1] => Array
    (
        [created_at] => Mon Jun 20 10:52:37 +0000 2011
        [text] => My bday in 5 minutes
        [sender] => Array
            (
                [name] => mark o
                [created_at] => Wed May 28 18:21:03 +0000 2008
            )
        [recipient] => Array
            (
                [created_at] => Mon Jun 21 19:48:50 +0000 2010
                [screen_name] => james
            )
        [sender_screen_name] => mark
        [recipient_screen_name] => james
    )  
)

This is a simplified version of a direct message feed from the Twitter API. I will be requesting the latest DMs every X minutes using PHP and the twitter-async library, and then I want to store parts of the array in a database table. Each DM should be stored in its own row.
The table the data will be stored in:

CREATE TABLE `dms` (
    `postid` INT(12) NOT NULL,
    `text` VARCHAR(140) NOT NULL COLLATE 'utf8_unicode_ci',
    `sender` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
    `sender_id` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
    `date_created` DATETIME NOT NULL
)

I really don't know how to go about doing this and would greatly appreciate some help.

If it simplifies things at all, I only need to pull values from the second layer in. Also, I don't know how many rows I'll be adding.

I've been messing around, and this code almost does what I need:

foreach ( $adms as $dm ) {

    foreach ( $dm as $key => $value ) {
        $q = "INSERT INTO dms SET text = '{$value}'";
        mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
  }

}

Of course, that just stores each second level value in a new row in the "text" column. If you can help me complete it to do what I've asked, I'll mark your answer.

Maybe this similar question will help someone devise an answer: [link](http://stackoverflow.com/questions/4485255/insert-unknown-number-of-rows-into-mysql-using-php) — David, Jun 28 '11 at 09:17
Maybe I can express what I think I need in this way: For each first level array, I want to add a row to the table with particular values from the second level arrays until all of the first level arrays have been processed. — David, Jun 28 '11 at 09:25

score 2 · Accepted Answer · edited May 23 '15 at 06:26

2

Well...you are only entering one value.

Try something like this: -

foreach ( $adms as $dm ) {
    $q = "INSERT INTO dms(text, sender, sender_id, date_created) values('".$dm["text"]."', '".$dm["sender_screen_name"]."', '".$dm["sender"]["name"]."', now())";
    mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
}

edited May 23 '15 at 06:26

Josua Marcel C

3,122
6
45
87

answered Jun 28 '11 at 10:18

M. Faraz

412
1
6
13

Thank you. I actually just figured that out on my own, amazingly, but you deserve the credit. – David Jun 28 '11 at 10:45

score 1 · Answer 2 · answered Jun 28 '11 at 09:01

Define a parent_postid, so insert the parent, get it's id and insert sub records with that id in the parent_postid

CREATE TABLE `dms` (
    `postid` INT(12) NOT NULL,
    `parent_postid` INT(12) NOT NULL,
    `text` VARCHAR(140) NOT NULL COLLATE 'utf8_unicode_ci',
    `sender` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
    `sender_id` VARCHAR(20) NOT NULL COLLATE 'utf8_unicode_ci',
    `date_created` DATETIME NOT NULL
)

this can be done to many n-levels... and each sub can be a parent and so on...

score 0 · Answer 3 · answered Jun 28 '11 at 09:03

0

What you want to achieve is also called shredding, e.g. xml shredding. The basic idea is to imagine your array as a tree. Once you have the information as a tree, you can store every node in a linear table. Along with every node (i.e. contained info) you store its parent/child relationship and whatever you need to restore your initial structure.

answered Jun 28 '11 at 09:03

paweloque

18,466
26
80
136

I don't care about retaining the structure. I just want to store a few elements together in the database. I think a foreach loop might be the way to go, but I'm uncertain and wouldn't know how to write it. – David Jun 28 '11 at 09:06

How can I store values from a nested/multidimensional array in a MySQL database?

3 Answers3