问从PHP插入多个MySQL的问题
EN

Stack Overflow用户

提问于 2014-09-22 23:40:27

回答 4查看 66关注 0票数 0

我正在尝试用一个新的条目更新一张菜肴表，并将它与现有的食材表交叉参照。对于添加的每一道菜，用户需要在多行上分配现有的配料和所需的体积。提交时，应将餐点输入餐桌“菜肴”，并将指定的配料输入“DishIng”链接表。

我的桌子是这样摆的：

Table: "Dishes" Columns: DishID, DishName, Serves, etc...
Table: "DishIng" Columns: DishID, IngID, Volume
Table: "Ingredients" Columns: IngID, IngName, Packsize etc...

HTML：

<form action="Array.php" method="post">
<ul>
<li>DishID: <input type="text" name="DishID"></li>
<li>Name: <input type="text" name="DishName"></li>
<li>Catagory : <input type="text" name="DishCatID"></li>
<li>Serving: <input type="text" name="Serving"></li>
<li>SRP: <input type="text" name="SRP"></li>
<li>Method : <input type="text" name="Method"></li>
<li>Source : <input type="text" name="SourceID"></li>
<br>
<li>IngID: <input type="text" name="IngID"></li>
<li>Volume: <input type="text" name="Volume"></li>

<li>IngID: <input type="text" name="IngID"></li>
<li>Volume: <input type="text" name="Volume"></li>

<li>IngID: <input type="text" name="IngID"></li>
<li>Volume: <input type="text" name="Volume"></li>

</ul>
<input type="submit">
</form>

任何关于在HTML中动态添加一行成分的建议都是非常欢迎的。

PHP：

<?php

require_once('db_connect.php');

$DishID = mysqli_real_escape_string($con, $_POST['DishID']);
$DishName = mysqli_real_escape_string($con, $_POST['DishName']);
$DishCatID = mysqli_real_escape_string($con, $_POST['DishCatID']);
$Serving = mysqli_real_escape_string($con, $_POST['Serving']);
$SRP = mysqli_real_escape_string($con, $_POST['SRP']);
$Method = mysqli_real_escape_string($con, $_POST['Method']);
$SourceID = mysqli_real_escape_string($con, $_POST['SourceID']);
$IngID = mysqli_real_escape_string($con, $_POST['IngID']);
$Volume = mysqli_real_escape_string($con, $_POST['Volume']);

$array = array('$DishID', '$IngID', '$Volume');


$sql="INSERT INTO Dishes (DishID, DishName, DishCatID, Serving, SRP, Method, SourceID)
VALUES ('$DishID', '$DishName', '$DishCatID', '$Serving', '$SRP', '$Method', '$SourceID')";

$sql2 = "INSERT INTO DishIng (DishID, IngID, Volume) VALUES ('$DishID', '$IngID', '$Volume')";

$it = new ArrayIterator ( $array );

$cit = new CachingIterator ( $it );

foreach ($cit as $value)
{
  $sql2 .= "('".$cit->key()."','" .$cit->current()."')";

    if( $cit->hasNext() )
    {
        $sql2 .= ",";
    }
}

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "1 record added";


if (!mysqli_query($con,$sql2)) {
  die('Error: ' . mysqli_error($con));
}
echo "records added";


require_once('db_disconnect.php');
php?>

当前提交时，它只更新“Dishes”表，并给我以下消息：“1 record addedError:您的SQL语法出错；检查与MySQL服务器版本对应的手册，以获得正确的语法，以便在第1行使用”接近“(”0“、”$DishID“)、(”1“、”$IngID“)、(”2“、”$Volume“)。

php

html

mysql

媒体处理1元起

智能、强大、全面的多媒体数据处理服务，助您提升媒体质量、降低成本，媒体处理套餐低至1元

回答 4

Stack Overflow用户

回答已采纳

发布于 2014-09-23 03:26:32

您需要更改表单，以便对重复输入使用数组样式名称：

<form action="Array.php" method="post">
<ul>
<li>DishID: <input type="text" name="DishID"></li>
<li>Name: <input type="text" name="DishName"></li>
<li>Catagory : <input type="text" name="DishCatID"></li>
<li>Serving: <input type="text" name="Serving"></li>
<li>SRP: <input type="text" name="SRP"></li>
<li>Method : <input type="text" name="Method"></li>
<li>Source : <input type="text" name="SourceID"></li>
<br>
<li>IngID: <input type="text" name="IngID[]"></li>
<li>Volume: <input type="text" name="Volume[]"></li>

<li>IngID: <input type="text" name="IngID[]"></li>
<li>Volume: <input type="text" name="Volume[]"></li>

<li>IngID: <input type="text" name="IngID[]"></li>
<li>Volume: <input type="text" name="Volume[]"></li>

</ul>
<input type="submit">
</form>

那么PHP应该是：

$DishID = mysqli_real_escape_string($con, $_POST['DishID']);
$DishName = mysqli_real_escape_string($con, $_POST['DishName']);
$DishCatID = mysqli_real_escape_string($con, $_POST['DishCatID']);
$Serving = mysqli_real_escape_string($con, $_POST['Serving']);
$SRP = mysqli_real_escape_string($con, $_POST['SRP']);
$Method = mysqli_real_escape_string($con, $_POST['Method']);
$SourceID = mysqli_real_escape_string($con, $_POST['SourceID']);

$sql="INSERT INTO Dishes (DishID, DishName, DishCatID, Serving, SRP, Method, SourceID)
      VALUES ('$DishID', '$DishName', '$DishCatID', '$Serving', '$SRP', '$Method', '$SourceID')";
mysqli_query($con, $sql) or die(mysqli_error($con));

$values = array();
foreach ($_POST['IngID'] as $i => $ingID) {
    if (!empty($ingID)) {
        $ingID = mysqli_real_escape_string($con, $ingID);
        $volume = mysqli_real_escape_string($con, $_POST['Volume'][$i]);
        $values[] = "('$DishID', '$ingID', '$volume')";
    }
}
if (!empty($values)) {
    $sql2 = 'INSERT INTO DishIng (DishID, IngID, Volume) VALUES ' . implode(', ', $values);
    mysqli_query($con, $sql2) or die(mysqli_error($con));
}

票数 0

Stack Overflow用户

发布于 2014-09-22 23:47:20

对于$sql2，在foreach循环中添加的第一行不以逗号分隔。它也没有相同数量的字段(3和2)。

$sql2 = "INSERT INTO DishIng (DishID, IngID, Volume) VALUES ('$DishID', '$IngID', '$Volume')"; // 3 fields
...
$sql2 .= "('".$cit->key()."','" .$cit->current()."')"; // 2 fields