Add new rows on update

Question

I have a form with dynamic rows.
I can add rows and save them all to database.
I can also edit the form and update the values to database.
The problem is, I can't add new rows on update.

My Table is:

TABLE `tbl_orderdetail` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) DEFAULT NULL,
`product_name` varchar(255) DEFAULT NULL,
`quantity` varchar(255) DEFAULT NULL,
`price` varchar(255) DEFAULT NULL,
`discount` int(11) DEFAULT NULL,
`amount` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)

This is the page that creates the invoice, where I can add as many rows as I want.
Notice input type="hidden" name="order_id"...

$id = mysql_insert_id();
for($i = 0 ;$i < count($_POST['product_name']);$i++)
{
    mysql_query("INSERT INTO tbl_orderdetail 
                SET order_id        = '{$id}', 
                    product_name    = '{$_POST['product_name'][$i]}', 
                    quantity        = '{$_POST['quantity'][$i]}', 
                    price           = '{$_POST['price'][$i]}', 
                    discount        = '{$_POST['discount'][$i]}', 
                    amount          = '{$_POST['amount'][$i]}'
                ");
}

<tbody id="orderdetail" class="detail">
    <tr>
    <input type="hidden" name="order_id" >
    <td width="2%" class="no">1</td>
    <td width="10%"><input type="text" class="form-control quantity" name="quantity[]"></td>
    <td width="60%"><input type="text" class="form-control product_name" name="product_name[]"></td>
    <td width="8%"><input type="text" class="form-control price" name="price[]"></td>
    <td width="4%"><input type="text" class="form-control discount" name="discount[]"></td>
    <td width="10%"><input type="text" class="form-control amount" name="amount[]"></td>
    <td width="6%"><a href="#" class="remove">Excluir</td>
    </tr>
</tbody>

This is the edit page. Notice I believe this should be "order_id", but I need it to be "id" because of the update.

<form   method="post" action="">    
  <div class="box-body">
    <table class="table table-bordered table-hover">
        <?php
          $sql = "SELECT * FROM tbl_orderdetail WHERE order_id=$id";
          $result = mysql_query($sql);
          $count=mysql_num_rows($result);
        ?>

    <thead>
        <th>No</th>
        <th>Qtde</th>
        <th>Descrição</th>
        <th>Unitário</th>
        <th>Desc.(%)</th>
        <th>Valor</th>
        <th><input type="button" value="+" id="add" class="btn btn-primary"></th>
    </thead>

    <tbody id="orderdetail" class="detail">

            <?php
               while ($rows = mysql_fetch_array($result)){
            ?>

    <tr>
    <input type="hidden" name="id[]" value="<?php echo $rows['id']; ?>" >
    <td width="2%" class="no">1</td>
    <td width="10%"><input type="text" id="quantity" class="form-control quantity" name="quantity[]" value="<?php echo $rows['quantity']; ?>"></td>
    <td width="60%"><input type="text" id="product_name" class="form-control product_name" name="product_name[]" value="<?php echo $rows['product_name']; ?>"></td>
    <td width="8%"><input type="text" id="price" class="form-control price" name="price[]" value="<?php echo $rows['price']; ?>"></td>
    <td width="4%"><input type="text" id="discount" class="form-control discount" name="discount[]" value="<?php echo $rows['discount']; ?>"></td>
    <td width="10%"><input type="text" id="amount" class="form-control amount" name="amount[]" value="<?php echo $rows['amount']; ?>"></td>
    <td width="6%"><a href="#" class="remove">Excluir</td>
    </tr>

    <?php 
    } 
    ?>  
    </tbody>
    </table>
        <input type="submit" class="btn btn-primary" name="update" id="update" value="Salvar">
    </form>  

<?php
if (isset($_POST['update'])) {

$size = count($_POST['quantity']);
$size = count($_POST['product_name']);
$size = count($_POST['price']);
$size = count($_POST['discount']);
$size = count($_POST['amount']);

$i = 0;
while ($i<$size) {
$quantity= $_POST['quantity'][$i];
$product_name= $_POST['product_name'][$i];
$price= $_POST['price'][$i];
$discount= $_POST['discount'][$i];
$amount= $_POST['amount'][$i];
$id= $_POST['id'][$i];


$query = ("UPDATE tbl_orderdetail SET product_name='$product_name', quantity='$quantity', price='$price', discount='$discount', amount='$amount' WHERE id = '$id'");

mysql_query($query) or die ("Error in query: $query");
++$i;

}
header( "Location: consulta_orc.php");
mysql_close();
}
?>

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Sep 01 '15 at 16:22
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Sep 01 '15 at 16:22
Thank you both. I'm learning from online tutorials and there are much more mysql than mysqli tutorials. — Mimi spo, Sep 01 '15 at 16:26
@SatenderK, as it is an invoice, I might have to add a row when I need to update. — Mimi spo, Sep 01 '15 at 16:27
Just Check [URL](https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html) — Satender K, Sep 01 '15 at 16:30
this doesn't help you `mysql_query($query) or die ("Error in query: $query");` this does `mysql_query($query) or die(mysql_error());` and get the real error, if any. — Funk Forty Niner, Sep 01 '15 at 16:32
I found that my issue is the UPDATE tbl_orderdetail .. WHERE order_id = $id I'm not inserting new rows. I'm updating the old ids. But I don't know how to solve this. — Mimi spo, Sep 01 '15 at 21:22

score 0 · Answer 1 · answered Sep 01 '15 at 17:21

Your query will only updates rows where id exists in the table. You need to perform a search by id, if id does exist, execute update else insert a new row.

$id = 1; 
$stmt = $pdo -> prepare('select id from table where id = ?');
$stmt -> execute(array($id));
$rows = $stmt -> fetch(PDO::FETCH_ASSOC);

if (count($rows) > 0) {
   echo 'update row';
} else {
   echo 'insert row';
}

Hope this help, it might need few changes to adapt to your scenario, but should give you an idea how to tackle your problem.

Erik Flitman · Answer 2 · 2015-09-01T16:31:42.443

-1

You may want to look into using insert on duplicate key update....

this will insert a new row if no orderid is given but will update the existing row if the orderid exists with the statement. For more information, see the following link: https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

edited Sep 01 '15 at 16:31

answered Sep 01 '15 at 16:26

Erik Flitman

163
4

Add new rows on update

2 Answers2