Update dynamic fields on 'Edit'

[mimiaes]

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]

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]

 

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]

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]

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]

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]

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]

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

[mimiaes]

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]

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]

do you now have

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

and same for the other order detail fields?

[mimiaes]

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]

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

 

Post your current code

[mimiaes]

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]

 

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]

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]

 

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]

 

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]

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

[Barand]

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>