Automate VALUEs based on how many inputs the form

问题:

How do I make an automated query that adds another VALUES row based on how many inputs with the names: name, ingredients and price there are?

The form looks like this:

When you press new row there will be another 'New item' box showing up and I want the SQL to record how many rows there are. Each 'New Item' = new value for the query.

SQL:

if($_SERVER['REQUEST_METHOD'] == 'POST'){

$status_input = $stmt = $dbh ->prepare("
    INSERT INTO menues(
            restaurant_id,
            title,
            subtitle,
            name,
            ingredients,
            price,
            category,
            upload_date) 

    VALUES
    (:restaurant_id,:title, :subtitle, :name, :ingredients, :price, :category, NOW())
    (:restaurant_id,:title, :subtitle, :name, :ingredients, :price, :category, NOW())
    (:restaurant_id,:title, :subtitle, :name, :ingredients, :price, :category, NOW())
");
    $stmt->bindParam(":restaurant_id", $userdata[0]['user_id']);
    $stmt->bindParam(":title", $_POST['title']);
    $stmt->bindParam(":subtitle", $_POST['subtitle']);
    $stmt->bindParam(":name", $_POST['name']);
    $stmt->bindParam(":ingredients", $_POST['ingredients']);
    $stmt->bindParam(":price", $_POST['price']);
    $stmt->bindParam(":category", $userdata[0]['category']);
    $stmt->execute();
}

Restaurant_id, title, subtitle, category and upload_date should all be the same for each row.

jQuery and HTML:

$(document).ready( function() {
  $('.newmenu').hide();
  $('.zero h3 a').click(function() {
    $('.newmenu').slideToggle();
  });
  $('.newmenu > ul li p.add').click(function(){
    $('.newmenu > ul li.edit').before("<li class='newrow'><h6>New item</h6><div><p>Name:</p><input type='text' name='name' placeholder='name' /></div><div><p>Ingredients:</p><input type='text' name='ingredients' placeholder='ingredients' /></div><div><p>Price:</p><input type='text' name='price' placeholder='price' /></li>");
  });
});

<form method='post' action='newmenu.php' class='newmenu'>
  <table>
    <tr><td>Namn:</td><td><input type='text' name='title' placeholder='namn' /></td></tr>
    <tr><td>Undertext:</td><td><input type='text' name='subtitle' placeholder='namn' /></td></tr>
  </table>
  <ul>
    <li class='newrow'>
      <h6>New item</h6>
      <div>
        <p>Name:</p>
        <input type='text' name='name' placeholder='name' />
      </div>
      <div>
        <p>Ingredients:</p>
        <input type='text' name='ingredients' placeholder='ingredients' />
      </div>
      <div>
        <p>Price:</p>
        <input type='text' name='price' placeholder='price' />
      <div>
    </li>
    <li class="edit">
      <input type="submit" value="Submit">
      <p class="add">New row</p>
    </li>
  </ul>
</form>

回答1:

You could try this:

Change all input fields names to have [] at the end (In Jquery Code and HTML).

Now you're getting arrays returned.

Then you handle them like this:

$name = $_POST["food_name"];
$ingredient = $_POST["food_ingredient"];
$price = $_POST["food_price"];
$length = count($name);

// Starting the query
$query = "INSERT INTO menues(
              restaurant_id,
              title,
              subtitle,
              name,
              ingredients,
              price,
              category,
              upload_date) 
          VALUES";

// Looping through all the input rows
for($key=0;$key<$length;$key++){
    // Create each insert
    $query .= "(:id,:title,:subtitle,:name_".$key.",:ingredient_".$key.",:price_".$key.",:cat,NOW())";
    // Check if its the last row
    $query .= (($key +1 == $length)?"":","); 
}

// Now the query is done, next step is adding values to placeholders
$insert = $pdo->prepare($query);

// Create an array with matching placeholders
$param = array();
for($key=0;$key<$length;$key++){
    $param[":name_".$key] = $name[$key];
    $param[":ingredient_".$key] = $ingredient[$key];
    $param[":price_".$key] = $price[$key];
}   

// Add more parameters if wanted
// $param[":id"] = 1;

// Execute with parameters created above
$insert->execute($param);

回答2:

Try these changes. I added comments to the code.

Basically I added a number to the formfield names, so you can distinguis them. So the first name is name_0, then when you add a row with javascript the next name will be name_1. Then in the PHP you'll make a loop and check if there's a name_0, then in the next loop you'll check if there's a name_1 and so on. When it doesn't find any more rows it exits the loop using break;.

I haven't tested the code, but basically it should work. Try to implement it yourself instead of copy-paste. Read the comments and look at what I changed in the code.

Good luck.

HTML:

<!--
Make the first fields use row number 0
-->

<form method='post' action='newmenu.php' class='newmenu'>
  <table>
    <tr><td>Namn:</td><td><input type='text' name='title' placeholder='namn' /></td></tr>
    <tr><td>Undertext:</td><td><input type='text' name='subtitle' placeholder='namn' /></td></tr>
  </table>
  <ul>
    <li class='newrow'>
      <h6>New item</h6>
      <div>
        <p>Name:</p>
        <input type='text' name='name_0' placeholder='name' />
      </div>
      <div>
        <p>Ingredients:</p>
        <input type='text' name='ingredients_0' placeholder='ingredients' />
      </div>
      <div>
        <p>Price:</p>
        <input type='text' name='price_0' placeholder='price' />
      <div>
    </li>
    <li class="edit">
      <input type="submit" value="Submit">
      <p class="add">New row</p>
    </li>
  </ul>
</form>

jQuery:

$(document).ready( function() {

  $('.newmenu').hide();
  $('.zero h3 a').click(function() {
    $('.newmenu').slideToggle();
  });

  // dynamically added rows start at row 1
  var row_number = 1;

  $('.newmenu > ul li p.add').click(function(){

    // append the fields with the row number
    $('.newmenu > ul li.edit').before("<li class='newrow'><h6>New item</h6><div><p>Name:</p><input type='text' name='name_" + row_number + "' placeholder='name' /></div><div><p>Ingredients:</p><input type='text' name='ingredients_" + row_number + "' placeholder='ingredients' /></div><div><p>Price:</p><input type='text' name='price_" + row_number + "' placeholder='price' /></li>");

    // increment row number
    row_number++;

  });

});

PHP:

if($_SERVER['REQUEST_METHOD'] == 'POST'){

    // start at row 0
    $row_number = 0;

    while(true) {

        // check if there are still fields in this row offset
        if(isset($_POST['name_' + $row_number])) {

            $status_input = $stmt = $dbh ->prepare("
               INSERT INTO menues(
                  restaurant_id,
                  title,
                  subtitle,
                  name,
                  ingredients,
                  price,
                  category,
                  upload_date) 

              VALUES
                  (:restaurant_id,:title, :subtitle, :name, :ingredients, :price, :category, NOW())
                  (:restaurant_id,:title, :subtitle, :name, :ingredients, :price, :category, NOW())
                  (:restaurant_id,:title, :subtitle, :name, :ingredients, :price, :category, NOW())
             ");
             $stmt->bindParam(":restaurant_id", $userdata[0]['user_id']);
             $stmt->bindParam(":title", $_POST['title']);
             $stmt->bindParam(":subtitle", $_POST['subtitle']);
             $stmt->bindParam(":name", $_POST['name_' . $row_number]);
             $stmt->bindParam(":ingredients", $_POST['ingredients_' . $row_number]);
             $stmt->bindParam(":price", $_POST['price_' . $row_number]);
             $stmt->bindParam(":category", $userdata[0]['category']);
             $stmt->execute();

        }

        // increment row number
        $row_number++;

    }
    else {
        // if there are no rows anymore, exit the while loop
        break;
    }
}