html dynamic form, pass multiple values through post and insert mysql

Question

i have this database

and this form, dynamically generated from the database

<table border="1" cellspacing="0" cellpadding="6">
      <tr bgcolor="#CCCCCC">
        <td><strong>product id</strong></td>
        <td><strong>product name</strong></td>
        <td><strong>product price</strong></td>
        <td><strong>quantity</strong></td>
      </tr>

    <form method="post" action="insert.php">

    <?php

        $query = $dbh->query('SELECT * FROM products');
        $results = $query->fetchAll(PDO::FETCH_ASSOC);

        foreach ($results as $row)
        {

    ?>

    <tr>
        <td><?php echo $row['product_id']; ?></td>
        <td><?php echo $row['product_name']; ?></td>
        <td><?php echo $row['product_price']; ?></td>
        <td><input name="quantity" type="text" value="0"></td>
    </tr>
    </form>

    <?php } ?>

    </table>
    <br>
    <input type="submit" value="Add Records">

the quantity in the form is a textbox so i can modify it. I would like to enter the quantities and the pressing the button to insert the values in the order_products table (including the quantity).

1) How can i pass ALL the quantities and product_id (and the rest) to the next page through post? (until now i know how to pass single values)

2) is there a better way to achieve it?

3) the insert statements should be in the same page or the page where i get the post vars?

db scheme https://i.stack.imgur.com/oqdOy.jpg

thanks

Rob

Answer 1

1) First, you have to wrap your <input>s inside a single form (move your </form> tag after your <input type="submit" value="Add Records">, the way you have it now closes the <form> tag at first iteration) and submit it via HTTP POST method. Then, based on your schema, the only field you'll have to insert aside of quantity is product_id, which value you can assign inside a hidden field, like:

<?php
echo "<input type='hidden' name='pid_$row['product_id']' value='$row['product_id']'>";
echo $row['product_id'];
?>

Notice that you can still echo the value itself for viewing purposes. You also have to generate your quantity <input> field name property dynamically, otherwise $_POST will overwrite values when their keys are the same.

<?php
echo "<input type='text' name='pid-qtd_$row['product_id']'>";
?>

2) It depends on your development priorities. There are some frameworks out there that might simplify your process. I'd recommend you to keep all your DB queries and connection data within a DB helper class and require it wherever you need it.

3) Since you're using PDO, I assume you have an OOP design, which implies in doing that at your DB helper class or such. The page receiving the HTTP request must require your helper and deal with the $_POST parsing to parameters to its query methods. Don't forget to prepare your statements and parameterizing your queries.

Answer 2

I suggest putting the product_id in a hidden form element like this:

<tr>
    <input type='hidden' name='product_id' value='<?php echo $row['product_id']; ?>'/>

    <td><?php echo $row['product_id']; ?></td>
    <td><?php echo $row['product_name']; ?></td>
    <td><?php echo $row['product_price']; ?></td>
    <td><input name="quantity" type="text" value="0"></td>
</tr>

This will send the product_id with your quantity and you can use it in your insert statement.

The only problem with this is if you have more than one row, there will be more than one hidden element for product_id, etc. Ways to overcome this include differentiating them by appending an incrementing number on the hidden element's name, e.g.:

$i = 1;
foreach ($results as $row)
{
    $product_id_name = 'product_id_'.$i;
    $quantity_name = 'quantity_'.$i;

    ... echo your table row, using $product_id_name in the hidden element, and $quantity_name in your text input

    $i++;
}

Then in your inserting code you have to look for all the items in $_POST whose keys start with "quantity_", and if they are non-zero, get the integer NNN after the key prefix "quantity_", and get the corresponding product_id_NNN value to do your insert.

Answer 3

Using hidden element you can post your data to second page. Using counter variable you can add dynamic form element and post it into second page.

<form method="post" action="test2.php">
<table border="1" cellspacing="0" cellpadding="6">
    <tr bgcolor="#CCCCCC">
        <td><strong>product id</strong></td>
        <td><strong>product name</strong></td>
        <td><strong>product price</strong></td>
        <td><strong>quantity</strong></td>
    </tr>

    <?php
    $query = $con->query('SELECT * FROM product'); //your query goes here
    $results = $query->fetchAll(PDO::FETCH_ASSOC);
    $i=0; //counter variable
    foreach ($results as $row)
    {
        ?>
        <tr>
            <td>
                <?php echo $row['prod_id']; ?>
                <input type="hidden" name="prod_id<?php echo $i; ?>" value="<?php echo $row['prod_id']; ?>" />
            </td>
            <td>
                <?php echo $row['prodname']; ?>
                <input type="hidden" name="name<?php echo $i; ?>" value="<?php echo $row['prodname']; ?>" />
            </td>
            <td>
                <?php echo $row['price']; ?>
                <input type="hidden" name="price<?php echo $i; ?>" value="<?php echo $row['price']; ?>" />
            </td>
            <td><input name="quantity<?php echo $i; ?>" type="text" value="0"></td>
        </tr>
        <?php 
        $i++; //increment counter variable
    } 
    ?>
    <input type="hidden" name="rows" id="rows" value="<?php echo $i; ?>" />
</table>
<br>
<input type="submit" value="Add Records">
</form>

Your insert page code goes here....

for($i=0;$i<=$_POST['rows'];$i++)
{
    $prodid = $_POST['prod_id'.$i];
    $pname = $_POST['name'.$i];
    $pprice = $_POST['price'.$i];

    $con ->exec("insert into product(prod_id,prodname,price)values('$prodid', '$pname','$pprice' )" );

}