使用PHP和MySQL从多维关联数组插入到多个表中

我已经做了两天的大部分时间，但没有取得多大进展。我在这里和其他地方都找到了很多帮助，但我不太清楚如何让所有人一起打球。

我正在为我在网上找到的想要保存的食谱开发一个简单的数据库。

通过解析URL以获取配方详细信息，然后手动修剪、添加和编辑，来填充表单。每个食谱都有一些基本信息、一些配料和一套说明。它们进入三个不同的表，并且都通过"recipe_id"连接。

我遇到的问题是，我不知道如何从from and中获取数组：

• 将一些字段作为一行放入一个表中

• 将三部分成分作为几行放入另一张表中

• 最后将指令作为若干行放入第三表中。

这是表单中的$_POST：

array(47) { 
["title"]=> string(33) "Hot and Fruity Caribbean Coleslaw" 
["description"]=> string(11) "Lorem ipsum" 
["credits"]=> string(11) "Lorem ipsum" 
["url"]=> string(69) "http://thehappyfoodie.co.uk/recipes/hot-and-fruity-caribbean-coleslaw" 
["category"]=> string(1) "1" 
["cuisine"]=> string(1) "1" 
["yield"]=> string(10) "8 servings" 
["time_prep"]=> string(2) "10" 
["time_cook"]=> string(2) "10" 
["time_total"]=> string(2) "20"
["ingredient_quant_0"]=> string(0) "" 
["ingredient_uom_0"]=> string(1) "8" 
["ingredient_0"]=> string(17) "1/2 Savoy cabbage" 
["ingredient_quant_1"]=> string(0) "" 
["ingredient_uom_1"]=> string(2) "11" 
["ingredient_1"]=> string(17) "1/2 white cabbage" 
["ingredient_quant_2"]=> string(0) "" 
["ingredient_uom_2"]=> string(1) "-" 
["ingredient_2"]=> string(21) "1/2 red onion, peeled" 
["ingredient_quant_3"]=> string(0) "" 
["ingredient_uom_3"]=> string(1) "8" 
["ingredient_3"]=> string(15) "1/2 small mango" 
["ingredient_quant_4"]=> string(0) "" 
["ingredient_uom_4"]=> string(1) "-" 
["ingredient_4"]=> string(20) "3 tsp French mustard" 
["ingredient_quant_5"]=> string(0) "" 
["ingredient_uom_5"]=> string(1) "-" 
["ingredient_5"]=> string(19) "100ml cider vinegar" 
["ingredient_quant_6"]=> string(0) "" 
["ingredient_uom_6"]=> string(1) "-" 
["ingredient_6"]=> string(29) "8 tbsp soft light brown sugar" 
["ingredient_quant_7"]=> string(3) "100" 
["ingredient_uom_7"]=> string(2) "10" 
["ingredient_7"]=> string(15) "100ml olive oil" 
["ingredient_quant_8"]=> string(0) "" 
["ingredient_uom_8"]=> string(1) "-" 
["ingredient_8"]=> string(15) "Salt and pepper" 
["ingredient_quant_9"]=> string(1) "1" 
["ingredient_uom_9"]=> string(1) "9" 
["ingredient_9"]=> string(54) "1/2 tbsp X Hot Reggae Reggae Sauce or hot chilli sauce" 
["ingredient_quant_10"]=> string(0) "" 
["ingredient_uom_10"]=> string(1) "-" 
["ingredient_10"]=> string(48) "2 red chillies, seeded and cut into fine slivers" 
["instruction_0"]=> string(177) "Remove and discard the cabbage cores and finely shred the leaves. Slice the onion wafer-thin using a mandolin if you have one. Peel the mango and cut the flesh into matchsticks." 
["instruction_1"]=> string(265) "Put the mustard, vinegar and sugar in a jam jar. Screw on the lid and give it a good shake. Add the oil, salt, pepper and hot sauce. Shake again then poor into a large serving bowl. Toss in the cabbage, onion, mango and chillies and mix well. Taste for seasoning." 
["image"]=> string(127) "https://flockler.com/thumbs/sites/192/127_grillitlevi_hotandfruitycoleslaw-smaller_s1200x630_c1500x876_l0x482_q80_noupscale.jpg" 

然后，我将该数组传递给一个函数，并尝试将其分解为应该进入不同表的部分：

function addnewrecipe($postArray) {
    var_dump($postArray);

    // Test and clean user input
    $cleanedArray = array();
    foreach ($postArray as $postKey => $postValue) {
        $cleanValue = test_input($postArray[$postKey]);
        $cleanedArray[$postKey] = $cleanValue;
    }
    // Map form post names to those in the DB
    $mappingArray = array(
        'title' => 'name',
        'description' => 'description',
        'credits' => 'author',
        'url' => 'url',
        'category' => 'category_id',
        'cuisine' => 'cuisine_id',
        'yield' => 'yield',
        'time_prep' => 'preptime',
        'time_cook' => 'cooktime',
        'time_total' => 'totaltime',
        'image' => 'image'
    );
    // Create a final array with used form fields and clean input
    $finalArray = array();
    foreach($mappingArray as $formKey => $dbKey) {
        if(!empty($cleanedArray[$formKey])) {
            $finalArray[$dbKey] = $cleanedArray[$formKey];
        }
    }


    //Connect to DB
    $conn = connDB();
    // Begin transaction
    try {
        $conn->autocommit(FALSE); 
        $recipeKey = array_keys($finalArray);
        $recipeValue = array_values($finalArray);
        $query = "INSERT INTO recipes (" . implode(', ', $recipeKey) . ") " . "VALUES ('" . implode("', '", $recipeValue) . "')";
        //echo $query;
        $result = $conn->query($query);
        if ( !$result ) {
            $result->free();
            throw new Exception($conn->error);
        }
        $recipe_id = $conn->insert_id; // last auto_inc id from *this* connection
        //$query = "INSERT INTO ingredients (recipe_id,ingredient,uom_id,ingredient_quant) ";
        //$query .= "VALUES ('$recipe_id','$ingredient')";
        //$result = $conn->query($query);
        //if ( !$result ) {
        //  $result->free();
        //  throw new Exception($conn->error);
        //}
        $conn->commit();
        $conn->autocommit(TRUE); // i.e., end transaction
    }
    catch ( Exception $e ) {
        $conn->rollback(); 
        $conn->autocommit(TRUE); // i.e., end transaction   
    }
    $conn->close();

它对基本信息来说还可以，尽管我有一种感觉，我把它稍微复杂了一点。我首先检查输入中是否有不安全的内容，然后将表单中的键/列名重新映射到DB中的键和列名，并创建一个新的数组$finalArray，干净的结果省略了指令和成分。

然后，它进入一个标准的INSERT语句，我可以继续准备插入成分和说明。但这正是我困惑的地方。

每个食谱都有不同数量的配料。每个成分都有三个值，其中两个可以为NULL。它们将被上一个插入中的"recipe_id"引用，它的工作原理很好：$recipe_id = $conn->insert_id;，但我如何获得每个成分的三个值，并将它们作为一行插入？我试过

$ingredientArray = array();
    $i = 0;
    while ($i < 5) {
        if (isset($cleanedArray["ingredient_" . $i])) {
            $ingredientArray["ingredient_" . $i] = $cleanedArray["ingredient_" . $i];
        }
        if (isset($cleanedArray["uom_id_" . $i])) {
            $ingredientArray["uom_id_" . $i] = $cleanedArray["uom_id_" . $i];
        }
        if (isset($cleanedArray["ingredient_quant_" . $i])) {
            $ingredientArray["ingredient_quant_" . $i] = $cleanedArray["ingredient_quant_" . $i];
        }
     $i++;
     }
     //var_dump($ingredientArray);

我想，把它们弄出来，但不知道可能有多少会让这种方法变得相当可疑。

我觉得这不应该那么难。我有一个相当简单的数组，其中包含值，还有一个看似简单的任务，就是将数组拆分并插入到正确的表中。如有任何关于如何处理这一问题的帮助，我们将不胜感激！