Loop over multidimensional array and execute INSERT queries for each pair of subarray of values

Question

I'm looking for a practical way to create a new database record for every 2 content array records with the value of date of the previous sub-array. As for your question: content will always have 24 records (0-23) which if divided should be 12 chunks.

[
    [
        'date' => '2018-05-23',
        'content' => [
            [
                'AAAAAA',
                'BBBBBB',
                'CCCCCC',
                'DDDDDD',
                'EEEEEE',
                'FFFFFF',
                'GGGGGG',
                'HHHHHH',
                'IIIIII',
                'JJJJJJ',
                //...
            ],
        ],
    ],
    [
        'date' => '2018-05-22',
        'content' => [
            [
                'KKKKKK',
                'LLLLLL',
                'MMMMMM',
                'NNNNNN',
                //...
            ],
        ],
    ],
];

I need to be able to create a foreach loop that will create a database record from 3 variables.

foreach #1:

$date = $array['content']['date']; //2018-05-23
$headline = $array['content'][0][$key]; // AAAAAA
$content = $array['content'][0][$key]; // BBBBBB

foreach #2:

$date = $array['content']['date']; //2018-05-23
$headline = $array['content'][0][$key]; // CCCCCC
$content = $array['content'][0][$key]; // DDDDDD

Until it finishes with the first sub-array and then goes to the second array:

foreach #6:

$date = $array['content']['date']; //2018-05-22
$headline = $array['content'][0][$key]; // KKKKKK
$content = $array['content'][0][$key]; // LLLLLL

I've been trying to group the arrays with array_chunk with no success and then I tried to write a small fix to order the array properly with this:

if ($x <= 10) {
        if ( $a < 2 ) {
            $a++;
        } else {
            $x++;
            $a = 1;
        }
    $res[$i]['content'][$x][] = ltrim($text);
} else {
    $res[$i]['content'][$x][] = ltrim($text);
    $x = 0;
}

Result:

[date] => 2018-05-23
            [content] => Array
                (
                    [0] => Array
                        (
                            [0] => AAAAAA
                            [1] => BBBBBB
                        )

                    [1] => Array
                        (
                            [0] => CCCCCC
                            [1] => DDDDDD
                        )

                    [2] => Array
                        (
                            [0] => EEEEEE
                            [1] => FFFFFF
                        )

                    [3] => Array
                        (
                            [0] => GGGGGG
                            [1] => HHHHHH
                        )

Which worked for the first array but all the other arrays lost order and were not categorized properly.

Any ideas how this can be created?

Answer 1

I'm not sure how your DB structure looks like because you didn't mention, but you can do something like that:

<?php
$array = [
    [
        "date" => "date",
        "content" => [["A", "B", "C"]]
    ],
    [
        "date" => "date",
        "content" => [["E", "F", "G", "H"]]
    ],
];

$sql = "INSERT INTO table (content, date) VALUES ";

foreach ($array as $key => $item) {
    $content = $item["content"][0];
    for ($i = 0; $i < count($content); $i += 2) {
        $letters = $content[$i];

        if (isset($content[$i + 1])) {
            $letters .= $content[$i + 1];
        }

        $sql .= "('$letters', '$item[date]'),";
    }
}

$sql = rtrim($sql, ",") . ";";

echo $sql;

Will output:

INSERT INTO table (content, date) VALUES ('AB', 'date'),('C', 'date'),('EF', 'date'),('GH', 'date');