How to insert dynamic multidimensional array in database with 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

What does your mysql table structure look like? If you dont need to run queries on these fields, just serialize the array and save that in a single field — Steve, Sep 08 '14 at 14:27
O yes, thanks for reminding me of serialize. It is not going to help me but it reminds me that I need to edit the question — SuperDJ, Sep 08 '14 at 14:29
Have you considered making an Object for each array and defining an insert() method for each Object that would contain the proper query and insert it to the database. — winternights83, Sep 08 '14 at 14:38
@nismoracerx I haven't considered that so I'll give that a try thanks — SuperDJ, Sep 08 '14 at 14:39
If you want any more help you will need to explain your database structure — Steve, Sep 08 '14 at 14:41
@user574632 the database structure is not complicated. It just has a table with id followed by all the form input fields. The same as the keys in the array shown. Except for the few missing at step3 everything is in the array for now. — SuperDJ, Sep 08 '14 at 14:45
@SuperDJ I dont understand, how can hierarchical data be represented in a single table? I mean how can `step 2` be saved into a single field without serialization? — Steve, Sep 08 '14 at 14:56
I dont think we are getting anywhere here, but one last attempt. If the table has a field called 'type' what data is saved in it? 1? 2? 1,2? your array has multiple duplicate keys, as it is multidimentional. How can you save them if the table simply has a field for with the key name? — Steve, Sep 08 '14 at 15:02
Is this multiple forms put together? What is the nature of this form? What do the arrays in step 2 represent and how many different types is there? Try to explain your site more you'll get more help. — winternights83, Sep 08 '14 at 15:09
@nismoracerx It is just one form but it is done in 4 different steps. step2 has 2 differenct options. 1 is a ticket and 1 is a giftcard — SuperDJ, Sep 08 '14 at 18:02
and is there a user table or are you trying to fit this all into 1 table because thats not gonna work — winternights83, Sep 08 '14 at 19:15
@nismoracerx yes type is to distinguish between ticket and giftcard. There is a users table but only for admins. But if you have an idea on how I could make it work with more tables I'll make some more tables — SuperDJ, Sep 09 '14 at 06:41
"Step2 can have as many items as the user wants" - You can't add them in a single row then (unless serialize/json). Either move these fields into second table (items with common id to join) or duplicate data in other fields (like a join result). — shudder, Sep 09 '14 at 10:49
I just solved the problem myself. as soon as I'm able too I'll post the answer — SuperDJ, Sep 09 '14 at 13:44

score 0 · Accepted Answer · answered Sep 10 '14 at 11:19

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.

How to insert dynamic multidimensional array in database with mysqli

1 Answers1

Linked