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

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开发者_StackOverflow 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.

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));
}

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

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.