如何使用PDO将html输入数组插入mysql [英] How to insert an html input array into mysql using PDO

问题描述

我有一个包含2个动态字段的HTML表单:

I have an HTML form that has 2 dynamic fields which are:

<input type="text"  name="ingredients[]" placeholder="Ingredients"/></div>
<input type="text"  name="quantity[]" placeholder="Quantity"/></div>

请看这里，这是表格中这些输入的db表应该如何显示的 铟11001 1KG 英制2 1001 2KG ing 3 1001 3KG

Look here this is how the db table should be for those inputs in the form Ing 1 1001 1KG Ing 2 1001 2KG ing 3 1001 3KG

现在检查图像，您会看到发生了什么

now check the image and you'll see what's happening

数据库配料表

我的php文件中有配方ID，我只想要一个PDO代码，可以帮助我连续插入成分"，数量"和"$ recipe_ID".

I have the recipe-ID in my php file I just want a PDO code that help me to INSERT "ingredients", "quantity" and "$recipe_ID" each one on a row.

<?php
header('Content-type: application/json');
require_once 'db/pdoconnect.php';
if($_POST)
    {

        $sth = $con->prepare("SELECT `recipeid` FROM Recipes ORDER BY `recipeid` DESC");
        $sth->execute();
        $previousid = $sth->fetchColumn();
        $offset=1;
        $generatingid=$previousid+$offset;
        $newid=$generatingid;
        //print("Last Id=$previousid\n");
        //print("New Id=$newid\n");

        $title = $_POST['title'];
        $preptime = $_POST['preptime'];
        $cocktime = $_POST['cocktime'];
        $level = $_POST['level'];
        $serving = $_POST['serving'];
        $recipetype = $_POST['recipetype'];
        $intro = $_POST['intro'];
        $details = $_POST['details'];
        $image = $_POST['image'];

        try
        {    

            $stmt = $con->prepare("INSERT INTO Recipes (recipeid,title,preptime,cocktime,level,serving,recipetype,intro,details,recipeimg) VALUES( :newid, :title, :preptime, :cocktime, :level, :serving, :recipetype, :intro, :details, :image)");
            $stmt->bindParam(":newid",$newid);
            $stmt->bindParam(":title",$title);
            $stmt->bindParam(":preptime",$preptime);
            $stmt->bindParam(":cocktime",$cocktime);
            $stmt->bindParam(":level",$level);
            $stmt->bindParam(":serving",$serving);
            $stmt->bindParam(":recipetype",$recipetype);
            $stmt->bindParam(":intro",$intro);
            $stmt->bindParam(":details",$details);
            $stmt->bindParam(":image",$image);

               if($stmt->execute()) { //check if main query has been executed 

    $sql = "INSERT INTO Ingredients VALUES";

    for($i = 1 ; $i <= count($_POST['ingredients']) ; $i++){
        $sql .= " (:recipeid" .$i. ", :ingredient" .$i. ", :quantity" .$i. "),"; 
    }

    // remove the last (,) from the $sql
    $sql = rtrim($sql, ',');
    $sth = $con->prepare($sql);

    // binding parameters 
    for($i = 1 ; $i <= count($_POST['ingredients']) ; $i++){
        $varIng = $_POST['ingredients'][$i];
        $varQnty = $_POST['quantity'][$i];

        $sth->bindParam(':recipeid' .$i , $newid , PDO::PARAM_STR);
        $sth->bindParam(':ingredient' .$i , $varIng , PDO::PARAM_STR);
        $sth->bindParam(':quantity' .$i , $varQnty , PDO::PARAM_STR);
    }  
              if ($sth->execute()) { 
              $response_array['status'] = 'success';  
                  }
              }//END OF FIRST IF STATEMENT

              else{
              $response_array['status'] = 'error';  
              }
               echo json_encode($response_array);   //SEND THE RESPONSE

               }//END OF TRY 

        catch(PDOException $e){
            echo $e->getMessage();
        }
    }

?>

推荐答案

假定表名称显示在您在数据库中的问题ingredient_quantity中发布的图像中，并且您说您已经拥有recipe-ID PHP小提琴

Assuming the table's name shown in the image you posted in your question ingredient_quantity in your database, and you said you already have the recipe-ID PHP Fiddle

<?php 
    $newid = 'A10';
    //considering this is your table shown in the picture
    $sql = "INSERT INTO ingredient_quantity VALUES";

    for($i = 1 ; $i <= count($_POST['ingredients']) ; $i++){
        $sql .= " (:newid" .$i. ", :ingredient" .$i. ", :quantity" .$i. "),"; 
    }

    // remove the last (,) from the $sql
    $sql = rtrim($sql, ',');
    $sth = $con->prepare($sql);

    // binding parameters 
    for($i = 1 ; $i <= count($_POST['ingredients']) ; $i++){
        $varIng = $_POST['ingredients'][$i];
        $varQnty = $_POST['quantity'][$i];
        $sth->bindParam(':newid' .$i , $newid , PDO::PARAM_STR);
        $sth->bindParam(':ingredient' .$i , $varIng , PDO::PARAM_STR);
        $sth->bindParam(':quantity' .$i , $varQnty , PDO::PARAM_STR);
    }
    $sth->execute();
?>

---

我有一个错误，因为ingredients[]数组从0而不是1开始，for循环的最后一个index将是undefined，因此请变通解决最后一个for循环如下所示:

for the above code I have an error as the ingredients[] array starts from 0 and not 1, the final index of the for loop will be undefined, so work around it make the last for loop like the following:

for($i = 0 ; $i < count($_POST['ingredients']) ; $i++){
        $varIng = $_POST['ingredients'][$i];
        $varQnty = $_POST['quantity'][$i];
        $j = $i + 1;
        $sth->bindParam(':newid' .$j , $newid , PDO::PARAM_STR);
        $sth->bindParam(':ingredient' .$j , $varIng , PDO::PARAM_STR);
        $sth->bindParam(':quantity' .$j , $varQnty , PDO::PARAM_STR);
    }

---

您可以尝试使用?占位符而不是像这样的 PHP Fiddle 2 :

---

EDIT 2: You may try doing it with ? placeholders instead of named placeholders like this PHP Fiddle 2:

//considering this is your table shown in the picture
    $sql = "INSERT INTO ingredient_quantity VALUES";

    for($i = 0 ; $i < count($_POST['ingredients']) ; $i++){
        $sql .= " ( ? , ? , ? ) , "; 
    }

    // remove the last (,) from the $sql
    $sql = rtrim($sql, ',');
    $sth = $con->prepare($sql);

    // binding parameters 
    $j = 1;
    for($i = 0 ; $i < count($_POST['ingredients']) ; $i++){
        $varIng = $_POST['ingredients'][$i];
        $varQnty = $_POST['quantity'][$i];
        $sth->bindValue( $j , $varIng);
        $sth->bindValue( $j + 1, $newid);
        $sth->bindValue( $j + 2, $varQnty);
        $j += 3;
    }
    $sth->execute();

这篇关于如何使用PDO将html输入数组插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！