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 — David
– David, Commented Jun 28, 2011 at 9: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
– David, Commented Jun 28, 2011 at 9:25

Josua M C · Accepted Answer · 2015-05-23 06:26:47Z

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, 2015 at 6:26

Josua M C

3,1566 gold badges47 silver badges89 bronze badges

answered Jun 28, 2011 at 10:18

M. Faraz

4121 gold badge6 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Thank you. I actually just figured that out on my own, amazingly, but you deserve the credit.

Brian · Accepted Answer · 2011-06-28 09:01:18Z

1

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...

answered Jun 28, 2011 at 9:01

Brian

8,6212 gold badges27 silver badges32 bronze badges

Comments

paweloque · Accepted Answer · 2011-06-28 09:03:22Z

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, 2011 at 9:03

paweloque

19k27 gold badges87 silver badges138 bronze badges

1 Comment

David Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related