如何使用mysqli在数据库中插入动态多维数组

Question

I have the following array:

Array
(
    [step1] => 3
    [step2] => Array
        (
            [1] => Array
                (
                    [type] => 2
                    [price] => 312.5
                )

            [0] => Array
                (
                    [type] => 1
                    [price] => 51.5
                )

        )

    [step3] => Array
        (
            [first_name] => Test
            [last_name] => Test
        )

    [step4] => Some answer
)

Which I would like to Insert in a database. Step2 can have as many items as the user wants. As you might have noticed there are differences between the 2 arrays of step2. Also step3 can have a few more fields. This is all user dependent.

The reason it is dynamic is because a user can manage form input fields in a dashboard.

I tried to do a foreach loop for each step to insert but this didn't work really well. What I would like to do is the following: Have a query which is dynamic so each field can be inserted in the database. I think the easiest way to do this is to do a foreach loop on step2 so each item has the other values as well. My question is how can I insert all the data from this multidimensional array in a database with mysqli (OOP).

Also checked a few other questions like this like: Inserting data into MySQL from a multidimensional array in php, insert multiple rows via a php array into mysql, insert php array into mysql But these don't help me. So implode() is not going to help me. Also serialize is not going to work since each field has its own field in the database. (When a user edits the form in the dashboard fields in the database also get changed)

Thanks in advance

Answer 1

I ended up creating a second table to house all the order details. The other table I edited so it can hold all the user data. The orders table also has an userId to link.

I then created a function to insert all the user data:

function insertUserData($array, $table) {

   $tbl_fields = $this->tableFields($table); 

    $query = "INSERT INTO `user_orders` ("; // Query
    foreach($array as $field => $value) {  
       if(array_key_exists($field, $tbl_fields)) {
            $query .= "`$field`, ";
       }
    }

    $query .= ") VALUES ("; // Add to query
    foreach($array as $field => $value) {  
       if(array_key_exists($field, $tbl_fields)) {
            $query .= "'$value', ";
       }
    }

    $query .=")"; // End 
    //echo $query; //Query output

    if($db->mysqli->query($query)) {
        $insert_id = $db->mysqli->insert_id;
    } else {
        echo 'Something went wrong';
    }
    return $insert_id;
}

And for step 2 I could do the following:

foreach($data['stap2'] as $key => $array) {
        $query = "INSERT INTO `orders` (";
        foreach($array as $field => $value) {
            if(array_key_exists($field, $table)) {
                $query .= "`$field`, ";
            }
        } 

        $query .= "`order_id`) VALUES (";

        foreach($array as $field => $value) {
            if(array_key_exists($field, $table)) {
                $query .= "'$value', ";
            }
        }
        $query .= "'$insert_id')";
        echo $query.'<br>';
    }

I check each field to the fields from the table. If a they are equal add them to the query. This way I create a dynamic query. This solved the problem for me.