从PHP插入多个MySQL的问题

Question

I'm trying to update a table of dishes with a new entry and cross reference it to an existing table of ingredients. For each dish added, the user is required to assign existing ingredients and the volume required on multiple lines. On submission, the Dish should be entered into the table 'Dishes' and the assigned ingredients should be entered into the 'DishIng' linked tabled.

My tables are set like this:

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>

Any suggestions for dynamically adding a row of ingredients in HTML would be very welcome.

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?>

Currently on submit, it only updates the 'Dishes' table and gives me this message: '1 record addedError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('0','$DishID'),('1','$IngID'),('2','$Volume')' at line 1'

Answer 1

You need to change your form to use array-style names for the repeated inputs:

<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>

Then your PHP should be:

$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));
}