Update dynamic fields on 'Edit'

mimiaes · August 12, 2015

Hello, everybody!

I hope you can help me.

I have a dynamic form (with dynamically adding rows).
This form has 2 normal input fields which send data to table 'orcamen'
and 4 dynamic fields which are stored in table 'tbl_orderdetail'.
I have already achieved to retrieve the stored data from both tables when I click the edit button.
Also, both normal fields are udating values in table 'orcamen'.

My problem - How can I update the dynamic fields?

This is my edit page that fetches correctly the values from both tables:

<?php
mysql_connect("localhost","root");
mysql_select_db("alpha");

$id = $_GET["id"];
settype($id, "integer");

$resultado=mysql_query("SELECT * FROM orcamen WHERE id = $id");
$orcrow=mysql_fetch_object($resultado);

//This is the table that gets dynamic fields
$query = mysql_query( "SELECT * FROM tbl_orderdetail WHERE order_id=$id");

mysql_close();
?>

And here is the html code of the dynamic rows:

<form method="post" action="salva_orc.php">
<input type="hidden" name="id" id="id" value="<?php echo $id;?>" />

<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 ($result = mysql_fetch_object($query)){
?>
<tr>
<td width="2%" class="no">1</td>
<td width="10%"><input type="text" class="form-control quantity" name="quantity[]" value="<?php echo $result->quantity ?>"></td>
<td width="60%"><input type="text" class="form-control productname" name="productname[]" value="<?php echo $result->product_name ?>"></td>
<td width="8%"><input type="text" class="form-control price" name="price[]" value="<?php echo $result->price ?>"></td>
<td width="4%"><input type="text" class="form-control discount" name="discount[]" value="<?php echo $result->discount ?>"></td>
<td width="10%"><input type="text" class="form-control amount" name="amount[]" value="<?php echo $result->amount ?>"></td>
<td width="6%"><a href="#" class="remove">Excluir</td>
</tr>
<?php } ?>
</tbody>
<input type="submit" class="btn btn-primary" name="update" id="update" value="Salvar">
</form>

And now the update file that is called from the upper form. The first part is updating correctly, but I have no clue on how to update the dynamic fields into table 'tbl_orderdetail'.

<?php
@ini_set('display_errors', '1');
error_reporting(E_ALL);

mysql_connect("localhost", "root", "");
mysql_select_db("alpha");

$razao = $_POST["razao"];
$local = $_POST["local"];
$condicao = $_POST["condicao"];
$estado = $_POST["estado"];
$material = $_POST["material"];
$obs = $_POST["obs"];
$id = $_POST["id"];
mysql_query ("UPDATE orcamen SET razao='$razao' , local='$local' , condicao='$condicao' , estado='$estado' , material='$material' , obs='$obs' WHERE id=$id");

mysql_close();
header("Location: consulta_orc.php");

?>

Barand · August 12, 2015

Use the order detail record ids as the array indexes for the field names

E.G.

name="quantity[$det_id]"
name="productname[$det_id]"

Then you can use those ids to update the associated detail records

foreach ($_POST['quantity'] as $ord_det_id => $quantity) {
    $productname = $_POST['productname'][$ord_det_id];
    ...
    $sql = "UPDATE tbl_orderdetail SET productname='$productname', quantity=$quantity, ... WHERE order_detail_id = $ord_det_id";
}

mimiaes · August 12, 2015

Use the order detail record ids as the array indexes for the field names

E.G.
name="quantity[$det_id]"
name="productname[$det_id]"
Then you can use those ids to update the associated detail records
foreach ($_POST['quantity'] as $ord_det_id => $quantity) {
    $productname = $_POST['productname'][$ord_det_id];
    ...
    $sql = "UPDATE tbl_orderdetail SET productname='$productname', quantity=$quantity, ... WHERE order_detail_id = $ord_det_id";
}

Thank you for your help, Barand.

But for me, as a total noob, I have some stupid questions now...

What do I put in the place of 'name'?

And will I use '$det_id' for all of them?

name="quantity[$det_id]"

name="productname[$det_id]"

And where does '$ord_det_id' come from?

So sorry...

Barand · August 12, 2015

What do I put in the place of 'name'?

At the moment you have name='productname[]'. I am suggesting you change that to name='productname[$det_id]' where $det_id is the id of the order detail record. Do this for all fields to tie them to the same record.

And where does '$ord_det_id' come from?

That will be the key of the POSTed data

foreach ($_POST['quantity'] as $ord_det_id => $quantity) {

mimiaes · August 12, 2015

At the moment you have name='productname[]'. I am suggesting you change that to name='productname[$det_id]' where $det_id is the id of the order detail record. Do this for all fields to tie them to the same record.

That will be the key of the POSTed data

foreach ($_POST['quantity'] as $ord_det_id => $quantity) {

Thank you again for the kind explanation. Now I changed my html code to:

<?php

                    while ($result = mysql_fetch_object($query)){

                ?>

                    <tr>

                        <td width="2%" class="no">1</td>

                        <td width="10%"><input type="text" class="form-control quantity" name="quantity[$order_id]" value="<?php echo $result->quantity ?>"></td>

                        <td width="60%"><input type="text" class="form-control productname" name="productname[$order_id]" value="<?php echo $result->product_name ?>"></td>

                        <td width="8%"><input type="text" class="form-control price" name="price[$order_id]" value="<?php echo $result->price ?>"></td>

                        <td width="4%"><input type="text" class="form-control discount" name="discount[$order_id]" value="<?php echo $result->discount ?>"></td>

                        <td width="10%"><input type="text" class="form-control amount" name="amount[$order_id]" value="<?php echo $result->amount ?>"></td>

                        <td width="6%"><a href="#" class="remove">Excluir</td>

                    </tr>

                <?php } ?>

and the update php file to:

foreach ($_POST['quantity'] as $ord_order_id => $quantity) {
    $productname = $_POST['productname'][$ord_order_id];
    $price = $_POST['price'][$ord_order_id];
    $discount = $_POST['discount'][$ord_order_id];
    $amount = $_POST['amount'][$ord_order_id];
    
    $sql = "UPDATE tbl_orderdetail SET productname='$productname', quantity='$quantity', price='$price', discount='$discount', amount='$amount' WHERE order_id = $ord_det_id";
}

Unfortunately, nothing happens. Still not updating.

Barand · August 12, 2015

NOT the order_id. That would give the fields for every detail record the same id. You want the id from the order detail records

mimiaes · August 12, 2015

NOT the order_id. That would give the fields for every detail record the same id. You want the id from the order detail records

That was what I understood from your explanation.

These are my table structures (in the attachment)

The orcamen.id saves as the same number as tbl_orderdetail.order_id

Edited August 12, 2015 by mimiaes

Barand · August 12, 2015

You need to be using tbl_orderdetail.id as the indexes for the field names

mimiaes · August 12, 2015

You need to be using tbl_orderdetail.id as the indexes for the field names

Barand, it isn't working.

Thank you so much for your time. I'll keep trying.

akphidelt2007 · August 12, 2015

Where does $order_id come from? Is it set above the script you showed. All I see is $id = $_GET['id']. Should it be $result->order_id? And when you have you problems with POSTS, print_r($_POST) will help you figure out what is actually getting captured, then you can debug the script at each point to see where the connection is being broken.

Barand · August 12, 2015

do you now have

name="quantity[$result->id]"

and same for the other order detail fields?

mimiaes · August 12, 2015

You need to be using tbl_orderdetail.id as the indexes for the field names

It's in the edit page:

//This is the table that gets dynamic fields 
$query = mysql_query( "SELECT * FROM tbl_orderdetail WHERE order_id=$id");

Can I edit my first post to add the code tags?

Barand · August 12, 2015

The time-limit for editing the post will have expired by now.

Post your current code

mimiaes · August 12, 2015

The time-limit for editing the post will have expired by now.

Post your current code

Top of the edit page which retrieves stored values:

<?php

mysql_connect("localhost","root");
mysql_select_db("alpha");



$id = $_GET["id"];
settype($id, "integer");

        $resultado=mysql_query("SELECT * FROM orcamen WHERE id = $id");
        
        $orcrow=mysql_fetch_object($resultado);
               
$query = mysql_query( "SELECT * FROM tbl_orderdetail WHERE order_id=$id");

mysql_close();
?>

The htm part from the same page:

<tbody id="orderdetail" class="detail">
                <?php
                    while ($result = mysql_fetch_object($query)){
                ?>
                    <tr>
                        <td width="2%" class="no">1</td>
                        <td width="10%"><input type="text" class="form-control quantity" name="quantity[$result->order_id]" value="<?php echo $result->quantity ?>"></td>
                        <td width="60%"><input type="text" class="form-control productname" name="productname[$result->order_id]" value="<?php echo $result->product_name ?>"></td>
                        <td width="8%"><input type="text" class="form-control price" name="price[$result->order_id]" value="<?php echo $result->price ?>"></td>
                        <td width="4%"><input type="text" class="form-control discount" name="discount[$result->order_id]" value="<?php echo $result->discount ?>"></td>
                        <td width="10%"><input type="text" class="form-control amount" name="amount[$result->order_id]" value="<?php echo $result->amount ?>"></td>
                        <td width="6%"><a href="#" class="remove">Excluir</td>
                    </tr>
                <?php } ?>    
                </tbody>
 
//the dynamic part
<script type="text/javascript">
        $(function(){
            $('#add').click(function(){
            addnewrow();
            });
            $('body').delegate('.remove','click',function(){
                $(this).parent().parent().remove();
            });
            $('.detail').delegate('.quantity,.price,.discount','keyup',function(){
                var tr = $(this).parent().parent();
                var qty = tr.find('.quantity').val();
                var price = tr.find('.price').val();
                var dis = tr.find('.discount').val();
                var amt = (qty * price) - (qty * price * dis)/100;
                tr.find('.amount').val(amt);
                total();
            });
        });
        
        function total()
        {
        var t = 0;
        $('.amount').each(function(i,e)
        {
            var amt = $(this).val()-0;
            t += amt;
        });
        $('.total').html(t);
        }
        
        function addnewrow()
        {
            var n = ($('.detail tr').length-0)+1;
            var tr = '<tr>'+
                        '<td class="no">' + n + '</td>'+
                        '<td><input type="text" class="form-control quantity" name="quantity[]"></td>'+
                        '<td><input type="text" class="form-control productname" name="productname[]"></td>'+
                        '<td><input type="text" class="form-control price" name="price[]"></td>'+
                        '<td><input type="text" class="form-control discount" name="discount[]"></td>'+
                        '<td><input type="text" class="form-control amount" name="amount[]"></td>'+
                        '<td><a href="#" class="remove">Excluir</td>'+
                    '</tr>';
            $('.detail').append(tr);        
        }
    </script>

The php script that is called i the form:

<?php
@ini_set('display_errors', '1');
error_reporting(E_ALL);

mysql_connect("localhost", "root", "");
mysql_select_db("alpha");


    $razao     = $_POST["razao"];
    $local         = $_POST["local"];
    $condicao     = $_POST["condicao"];
    $estado         = $_POST["estado"];
    $material         = $_POST["material"];
    $obs         = $_POST["obs"];
    $id         = $_POST["id"];
    mysql_query ("UPDATE orcamen SET razao='$razao' , local='$local' , condicao='$condicao' , estado='$estado' , material='$material' , obs='$obs' WHERE id=$id");
    
        
    foreach ($_POST['quantity'] as $ord_det_id => $quantity) {
    $productname = $_POST['productname'][$ord_det_id];
    $price = $_POST['price'][$ord_det_id];
    $discount = $_POST['discount'][$ord_det_id];
    $amount = $_POST['amount'][$ord_det_id];
    
    $sql = "UPDATE tbl_orderdetail SET productname='$productname', quantity='$quantity', price='$price', discount='$discount', amount='$amount' WHERE order_id = $ord_det_id";
}
    
 
    
mysql_close();
header("Location: consulta_orc.php");

?>

Barand · August 12, 2015

name="quantity[$result->order_id]"

No, No, No!

For the last time (I'll type it slowly), NOT the order id

You need the id of the individual order detail record

name="quantity[$result->id]"

akphidelt2007 · August 12, 2015

Unless there is more to the code you aren't showing. How is the form submitted? Also...

<a href="#" class="remove">Excluir</td>

The <a> needs to be closed both in the html and the javascript that adds a new row.

mimiaes · August 13, 2015

No, No, No!

For the last time (I'll type it slowly), NOT the order id

You need the id of the individual order detail record
name="quantity[$result->id]"

Thank you for writing it slowly, but I tried everything and still nothing happens.

mimiaes · August 13, 2015

No, No, No!

For the last time (I'll type it slowly), NOT the order id

You need the id of the individual order detail record
name="quantity[$result->id]"

I've got a step further now.

The code is saving the edit, but not inserting more than 1 row in the database.

Only the last value is being saved. I know I have to use something like

for($i = 0 ;$i < count($_POST['quantity']);$i++)

But I can't figure out how.

foreach ($_POST['quantity'] as $ord_det_id => $quantity) {
    $product_name = $_POST['product_name'][$ord_det_id];
    $price = $_POST['price'][$ord_det_id];
    $discount = $_POST['discount'][$ord_det_id];
    $amount = $_POST['amount'][$ord_det_id];
    
    mysql_query ("UPDATE tbl_orderdetail SET product_name='$product_name', quantity='$quantity', price='$price', discount='$discount', amount='$amount' WHERE order_id = $id");
}

mac_gyver · August 13, 2015

the reason your code currently isn't working is because you are not echoing the $result->id in the various form name= .... attributes

Barand · August 13, 2015

try

<?php

$id = $_GET["id"];
settype($id, "integer");

$resultado=mysql_query("SELECT * FROM orcamen WHERE id = $id");

$orcrow=mysql_fetch_object($resultado);
       
$query = mysql_query( "SELECT * FROM tbl_orderdetail WHERE order_id=$id");

?>
<html>
<head>
<title>Sample</title>
<script type='text/javascript' src='jquery-1.10.2.js'></script>
<script type="text/javascript">
        $(function(){
            $('#add').click(function(){
            addnewrow();
            });
            $('body').delegate('.remove','click',function(){
                $(this).parent().parent().remove();
            });
            $('.detail').delegate('.quantity,.price,.discount','keyup',function(){
                var tr = $(this).parent().parent();
                var qty = tr.find('.quantity').val();
                var price = tr.find('.price').val();
                var dis = tr.find('.discount').val();
                var amt = (qty * price) - (qty * price * dis)/100;
                tr.find('.amount').val(amt);
                total();
            });
        });
        
        function total()
        {
        var t = 0;
        $('.amount').each(function(i,e)
        {
            var amt = $(this).val()-0;
            t += amt;
        });
        $('.total').html(t);
        }
        
        function addnewrow()
        {
            var n = ($('.detail tr').length-0)+1;
            var tr = '<tr>'+
                        '<td class="no">' + n + '</td>'+
                        '<td><input type="text" class="form-control quantity" name="quantity[]"></td>'+
                        '<td><input type="text" class="form-control productname" name="productname[]"></td>'+
                        '<td><input type="text" class="form-control price" name="price[]"></td>'+
                        '<td><input type="text" class="form-control discount" name="discount[]"></td>'+
                        '<td><input type="text" class="form-control amount" name="amount[]"></td>'+
                        '<td><a href="#" class="remove">Excluir</td>'+
                    '</tr>';
            $('.detail').append(tr);        
        }
</script>

</head>
<body>
<form method="post" action="salva_orc.php">
<input type="hidden" name="id" id="id" value="<?php echo $id;?>" />

<table border='1'>
<thead>
<tr>
    <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>
</tr>
</thead>
<tbody id="orderdetail" class="detail">
<?php
    while ($result = mysql_fetch_object($query)){
    echo <<<ROW
    <tr>
        <td width="2%" class="no">1</td>
        <td width="10%"><input type="text" class="form-control quantity" name="quantity[$result->id]" value="$result->quantity"></td>
        <td width="60%"><input type="text" class="form-control productname" name="productname[$result->id]" value="$result->product_name"></td>
        <td width="8%"><input type="text" class="form-control price" name="price[$result->id]" value="$result->price"></td>
        <td width="4%"><input type="text" class="form-control discount" name="discount[$result->id]" value="$result->discount"></td>
        <td width="10%"><input type="text" class="form-control amount" name="amount[$result->id]" value="$result->amount"></td>
        <td width="6%"><a href="#" class="remove">Excluir</a></td>
    </tr>
ROW;
    } 
?>    
</tbody>
</table>
<input type='submit' name='btnSubmit' value='Submit'>
</form>
</body>
</html>

Sign In

Update dynamic fields on 'Edit'

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information