How can I update all row from database and show updated data without refresh?

Question

I have a table with some calculation, I can submit twice and refresh after inputting the data to get the result for the first row of data, the rest of the row has no result. Now I want to update automatically after entering the data. is it possible to do that? or can I update all row at once? anyone could help?

index.php

<script>
        window.onload = function() {

            $(".cal_amount").change(function() {
                var auto_array = {};

                //Step 1- On change use The closest() method to get the all input elements value of selected element row.
                form_data = $(this).closest('tr').find('input, select');

                //Step 2- On change Use map to store input elements value with name as key in the array.
                var myArray = $.map(form_data, function(element) {
                        auto_array[element.name] = element.value;
                        //return {name: element.name, value: element.value};
                });

    console.log(myArray);
    var pprice = $(this).closest('tr').find('.pprice');
    var price = $(this).closest('tr').find('.price');
    var total_cost = $(this).closest('tr').find('.total_cost');
    var pprice_total = $(this).closest('tr').find('.pprice_total');
    var supplement_rate = $(this).closest('tr').find('.supplement_rate');


    pprice_val = Math.round(auto_array['cost'] * auto_array['profit_rate'] * auto_array['currency_rate'] * auto_array['vat'] / auto_array['eurbuy']);
    price_val = Math.round(auto_array['cost'] * auto_array['profit_rate'] * auto_array['currency_rate'] * auto_array['vat'] / auto_array['eurbuy']) * auto_array['no_of_day'] * auto_array['qua'];
    total_cost_val = Math.round(auto_array['cost'] * auto_array['qua'] * auto_array['no_of_day'] * auto_array['vat'] * auto_array['currency_rate'] / auto_array['eurbuy']);
    pprice_total_val = Math.round(auto_array['cost'] * auto_array['vat'] * auto_array['profit_rate'] * auto_array['currency_rate'] *  auto_array['qua'] /  auto_array['eurbuy']);
    supplement_rate_val = Math.round(auto_array['supplement'] * auto_array['profit_rate'] * auto_array['no_of_day'] * auto_array['currency_rate'] / auto_array['eurbuy']);


    if(!isNaN(pprice_val) && pprice_val != 'Infinity') {
        pprice.val(pprice_val);
    }
    if(!isNaN(price_val) && price_val != 'Infinity') {
        price.val(price_val);
    }
    if(!isNaN(total_cost_val) && total_cost_val != 'Infinity') {
        total_cost.val(total_cost_val);
    }

    if(!isNaN(pprice_total_val) && pprice_total_val != 'Infinity') {
        pprice_total.val(pprice_total_val);
    }

    if(!isNaN(supplement_rate_val) && supplement_rate_val != 'Infinity') {
        supplement_rate.val(supplement_rate_val);
    }



      form_data = $(this).closest('tr').find('input,select').serialize();
            $.ajax({
                    data: {
                        action: 'update_price',
                        form_data: form_data,
                    },
                    url: 'updates_ok.php',
                    type: 'post',
                    beforeSend: function() {

                    },
                    success: function(data) {
                        if(data == 1){
                        alert('update sucessful')}
                    }
                });
        });


        };

        </script>

<script>
        window.onload = function() {

            $(".day_record").change(function() {
                var auto_array = {};

                //Step 1- On change use The closest() method to get the all input elements value of selected element row.
                form_data = $(this).closest('tr').find('input, select');

                //Step 2- On change Use map to store input elements value with name as key in the array.
                var myArray = $.map(form_data, function(element) {
                        auto_array[element.name] = element.value;
                        //return {name: element.name, value: element.value};
                });

    console.log(myArray);
    var pprice = $(this).closest('tr').find('.pprice');
    var price = $(this).closest('tr').find('.price');
    var total_cost = $(this).closest('tr').find('.total_cost');
    var pprice_total = $(this).closest('tr').find('.pprice_total');
    var supplement_rate = $(this).closest('tr').find('.supplement_rate');


    pprice_val = Math.round(auto_array['cost'] * auto_array['profit_rate'] * auto_array['currency_rate'] * auto_array['vat'] / auto_array['eurbuy']);
    price_val = Math.round(auto_array['cost'] * auto_array['profit_rate'] * auto_array['currency_rate'] * auto_array['vat'] / auto_array['eurbuy']) * auto_array['no_of_day'] * auto_array['qua'];
    total_cost_val = Math.round(auto_array['cost'] * auto_array['qua'] * auto_array['no_of_day'] * auto_array['vat'] * auto_array['currency_rate'] / auto_array['eurbuy']);
    pprice_total_val = Math.round(auto_array['cost'] * auto_array['vat'] * auto_array['profit_rate'] * auto_array['currency_rate'] *  auto_array['qua'] /  auto_array['eurbuy']);
    supplement_rate_val = Math.round(auto_array['supplement'] * auto_array['profit_rate'] * auto_array['no_of_day'] * auto_array['currency_rate'] / auto_array['eurbuy']);


    if(!isNaN(pprice_val) && pprice_val != 'Infinity') {
        pprice.val(pprice_val);
    }
    if(!isNaN(price_val) && price_val != 'Infinity') {
        price.val(price_val);
    }
    if(!isNaN(total_cost_val) && total_cost_val != 'Infinity') {
        total_cost.val(total_cost_val);
    }

    if(!isNaN(pprice_total_val) && pprice_total_val != 'Infinity') {
        pprice_total.val(pprice_total_val);
    }

    if(!isNaN(supplement_rate_val) && supplement_rate_val != 'Infinity') {
        supplement_rate.val(supplement_rate_val);
    }



      form_data = $(this).closest('tr').find('input,select').serialize();
            $.ajax({
                    data: {
                        action: 'update_data',
                        form_data: form_data,
                    },
                    url: 'updates_ok.php',
                    type: 'post',
                    beforeSend: function() {

                    },
                    success: function(data) {
                        if(data == 1){
                        alert('update sucessful');}
                    }
                });
        });


        };

        </script>

update.php

<?php

if($_POST['action'] == 'update_price'){
//parse the serialize data
parse_str($_POST['form_data'], $my_form_data);

/*echo "<pre>";
print_r($my_form_data);*/


header("Content-Type: text/html; charset=utf-8");
include("connMysql.php");
$seldb = @mysql_select_db("phpmember");
if (!$seldb) die("cannot connect to the database！");

$id = $my_form_data['id']; 
$gp_name = $my_form_data['gp_name']; 
$price = $my_form_data['price'];
$cost = $my_form_data['cost']; 
$no_of_day = $my_form_data['no_of_day']; 
$total_cost = $my_form_data['total_cost']; 
$profit_rate = $my_form_data['profit_rate']; 
$currency_rate = $my_form_data['currency_rate']; 
$eurbuy = $my_form_data['eurbuy']; 
$pprice = $my_form_data['pprice'];
$qua = $my_form_data['qua']; 
$supplement = $my_form_data['supplement']; 
$supplement_rate = $my_form_data['supplement_rate']; 
$pprice_total = $my_form_data['pprice_total']; 
$vat = $my_form_data['vat'];    
$type = $my_form_data['type'];  
$supplier = $my_form_data['typeahead']; 


$sql= $query = $finalquery = $sqlresult = '';

if($cost){
$sql.="cost='$cost',";
}

if($profit_rate){
$sql.="profit_rate='$profit_rate',";
}

if($currency_rate){
$sql.="currency_rate='$currency_rate',";
}   

if($price){
$sql.="price='$price',";
}
if($pprice){
$sql.="pprice='$pprice',";
}

if($eurbuy){
$sql.="eurbuy='$eurbuy',";
}

if($no_of_day){
$sql.="no_of_day='$no_of_day',";
}

if($total_cost){
$sql.="total_cost='$total_cost',";
}

if($qua){
$sql.="qua='$qua',";
}

if($supplement){
$sql.="supplement='$supplement',";
}

if($supplement_rate){
$sql.="supplement_rate='$supplement_rate',";
}   

if($pprice_total){
$sql.="pprice_total='$pprice_total',";
}

if($vat){
$sql.="vat='$vat',";
}       


if($type){
$sql.="type='$type',";
}

if($supplier){
$sql.="supplier='$supplier',";
}       


$finalquery = rtrim($sql,',');
$query="UPDATE `gp_info` SET $finalquery where id=$id";

$sqlresult=mysql_query($query);
if($sqlresult){
$reback=1;
}else{
$reback=0;
}
echo $reback;
}

if($_POST['action'] == 'update_data'){
//parse the serialize data
parse_str($_POST['form_data'], $my_form_data);

/*echo "<pre>";
print_r($my_form_data);*/

$gp_name = $my_form_data['gp_name']; 
$date = $my_form_data['date']; 
$day = $my_form_data['day']; 
$day_week = $my_form_data['day_week'];  
$country = $my_form_data['country'];    
$city = $my_form_data['city']; 
$pax = $my_form_data['pax'];    
$profit_rate = $my_form_data['profit_rate']; 

$sql= $query = $finalquery = $sqlresult = '';


if($date){
$sql.="date='$date',";
}   

if($day){
$sql.="day='$day',";
}

if($day_week){
$sql.="day_week='$day_week',";
}       

if($country){
$sql.="country='$country',";
}

if($city){
$sql.="city='$city',";
}       

if($pax){
$sql.="pax='$pax',";
}

if($profit_rate){
$sql.="profit_rate='$profit_rate',";
}   

$finalquery = rtrim($sql,',');
$query="UPDATE `gp_info` SET $finalquery where gp_name='$gp_name' AND         date='$date' AND day='$day'";
$sqlresult=mysql_query($query);
if($sqlresult){
$reback=1;
}else{
    $reback=0;
}
echo $reback;
}
?>

I have try to search on the internet, but I cannot find relevant information which suitable for my situation, hope someone could help me.

In table (_editable_table) there are only one row or more then one? — Shivendra Singh, Jun 11 '19 at 09:32
There are several tables, each with a lot of row of data, I only list one table as for example. I realize that I can use onBlur in input to update the data, but it only can apply for the first row... — henrik, Jun 11 '19 at 10:01

Shivendra Singh · Accepted Answer · 2019-06-15T07:22:05.870

Step 1- On blur/change use The closest() method to get the all input elements value of selected element row. https://api.jquery.com/closest/

Step 2- Use map to store input elements value with name as key in the array. http://api.jquery.com/jQuery.map/

Step 3- Get the required value from the array and calculate.

Step 4- If the calculated value is not NaN and Infinity, add that value in the corresponding field.

Step 5- In update.php file, On blur/change of input field update the column in the table which value is not null.

update.php file.

    <?php

if($_POST['action'] == 'update_price'){
//parse the serialize data
parse_str($_POST['form_data'], $my_form_data);

/*echo "<pre>";
print_r($my_form_data);*/


header("Content-Type: text/html; charset=utf-8");
include("connMysql.php");
$seldb = @mysql_select_db("phpmember");
if (!$seldb) die("cannot connect to the database！");

$id = $my_form_data['id']; 
$gp_name = $my_form_data['gp_name']; 
$cost = $my_form_data['cost']; 
$profit_rate = $my_form_data['profit_rate']; 
$currency_rate = $my_form_data['currency_rate']; 
$eurbuy = $my_form_data['eurbuy']; 
$pprice = $my_form_data['pprice']; 

$sql= $query = $finalquery = $sqlresult = '';

if($cost){
    $sql.="cost='$cost',";
}

if($profit_rate){
    $sql.="profit_rate='$profit_rate',";
}

if($currency_rate){
    $sql.="currency_rate='$currency_rate',";
}

if($pprice){
    $sql.="pprice='$pprice',";
}

if($eurbuy){
$sql.="eurbuy='$eurbuy',";
}

$finalquery = rtrim($sql,',');
$query="UPDATE `gp_info` SET $finalquery where id=$id";

$sqlresult=mysql_query($query);
if($sqlresult){
    $reback=1;
}else{
    $reback=0;
}
echo $reback;
}

?>

HTML code.

<script>
            window.onload = function() {

                $(".cal_amount").change(function() {
                    var auto_array = {};

                    //Step 1- On change use The closest() method to get the all input elements value of selected element row.
                    form_data = $(this).closest('tr').find('input, select');

                    //Step 2- On change Use map to store input elements value with name as key in the array.
                    var myArray = $.map(form_data, function(element) {
                            auto_array[element.name] = element.value;
                            //return {name: element.name, value: element.value};
                    });


                console.log(myArray);
                var pprice = $(this).closest('tr').find('.pprice');
                var price = $(this).closest('tr').find('.price');
                var total_cost = $(this).closest('tr').find('.total_cost');
                var pprice_total = $(this).closest('tr').find('.pprice_total');
                var supplement_vat = $(this).closest('tr').find('.supplement_vat');

                //Step 3-  Get the required value from the array and calculate.
                pprice_val = auto_array['cost'] * auto_array['profit_rate'] * auto_array['currency_rate'] / auto_array['eurbuy'];
                price_val = auto_array['cost'] * auto_array['profit_rate'] * auto_array['qua'] * auto_array['no_of_day'] * auto_array['currency_rate'] / auto_array['eurbuy'];
                total_cost_val = auto_array['cost'] * auto_array['qua'] * auto_array['no_of_day'] * auto_array['vat'] * auto_array['currency_rate'] / auto_array['eurbuy'];
                pprice_total_val = auto_array['cost'] * auto_array['vat'] * auto_array['profit_rate'] * auto_array['currency_rate'] *  auto_array['qua'] /  auto_array['eurbuy'];
                supplement_vat_val = auto_array['supplement_rate'] * auto_array['profit_rate'] * auto_array['no_of_day'] * auto_array['currency_rate'] / auto_array['eurbuy'];

                //Step 4 - If the calculated value is not NaN and Infinity, add that value in the corresponding field.
                if(!isNaN(pprice_val) && pprice_val != 'Infinity') {
                    pprice.val(pprice_val);
                }
                if(!isNaN(price_val) && price_val != 'Infinity') {
                    price.val(price_val);
                }
                if(!isNaN(total_cost_val) && total_cost_val != 'Infinity') {
                    total_cost.val(total_cost_val);
                }

                if(!isNaN(pprice_total_val) && pprice_total_val != 'Infinity') {
                    pprice_total.val(pprice_total_val);
                }

                if(!isNaN(supplement_vat_val) && supplement_vat_val != 'Infinity') {
                    supplement_vat.val(supplement_vat_val);
                }



                form_data = $(this).closest('tr').find('input, select').serialize();
                $.ajax({
                        data: {
                            action: 'update_price',
                            form_data: form_data,
                        },
                        url: 'ajax.php',
                        type: 'post',
                        beforeSend: function() {

                        },
                        success: function(data) {
                            if(data == 1){
                                alert('update sucessful');
                            }
                        }
                    });
            });


            };

            </script>


           <?php foreach($queryRecords as $res) :?>
            <tr>
            <input name="id" type="hidden" class="id" value="10">
            <input name="gp_name" type="hidden" class="gp_name">
            <td><input name="no_of_day" type="text" class="cal_amount no_of_day"></td>
            <td><input name="qua" type="text" class="cal_amount qua"></td>
            <td><input name="cost" type="text" class="cal_amount cost"></td>
            <td><input name="profit_rate" type="text" class="cal_amount profit_rate"></td>

           <td>
    <select name="currency_rate" class="cal_amount currency_rate">
                <option selected value=""><?php echo $res["currency_rate"];?></option>
                <option value="<?php echo $nok;?>">[NOK] <?php echo $nok;?></option>
                <option value="<?php echo $dkk;?>">[DKK] <?php echo $dkk;?></option>
                <option value="<?php echo $isk;?>">[ISK] <?php echo $isk;?> 
    </option>
            </select>
        </td>

            <td><input name="eurbuy" type="text" class="cal_amount eurbuy"></td>
            <td><input name="pprice" type="text" class="cal_amount pprice"></td>
            <td><input name="price" type="text" class="cal_amount price"></td>

            <!-- add supplement_rate in form -->
            <td><input name="supplement_rate" type="text" class="cal_amount supplement_rate"/></td>


            <td class="editable-col" col-index='17'>test</td>
            <td><input name="supplement_vat" type="text" class="cal_amount supplement_vat"></td>

            </tr>
            <?php endforeach;?>
                            </tbody>
                            </table>

Thanks, but is it possible if I use onBlur function for the table so it can be updated automatically after entering data instead of submit each row? — henrik, Jun 11 '19 at 16:09
Hi, I have update the code, actually I have 4 more calculation in the table, when I add those calculation, it doesn't work, can you check if any code was wrong? — henrik, Jun 12 '19 at 08:07
I update the HTML code please have a look. add the supplement_rate in form. — Shivendra Singh, Jun 12 '19 at 09:33
hi, I have update my code, please see the last part, I want to add SELECT code in the table, it can display what I want, but it doesn't store in the database after I chose, can you have a look? — henrik, Jun 15 '19 at 00:06
I have follow your instruction, but don't know why, it seems cannot save in database — henrik, Jun 15 '19 at 05:39
Use updated jQuery which I have added. For select I modified the form data. After that in Please check update query in update.php. — Shivendra Singh, Jun 15 '19 at 06:19
I updated my code, could take a look of it? since I cannot see which part have problem — henrik, Jun 15 '19 at 06:41
Thank you, I am trying to figure out what the problem is. In fact, each value works fine and is saved in the database when the value is changed, except that the SELECT form cannot be saved in the database... — henrik, Jun 15 '19 at 07:26
Yes I got it. I update my jquery code please check. At the time of sending the ajax request modified the form data form_data = $(this).closest('tr').find('input, select').serialize(); . Before i was forgot to add 'select' so it was not saving. If it's working please update me. — Shivendra Singh, Jun 15 '19 at 07:27
hi, there is the other question if you have time, Thanks https://stackoverflow.com/questions/56616790/duplicate-value-in-typeahead — henrik, Jun 16 '19 at 06:59
hi, sorry for bother you again, there is the other question which regarding to this answer. I am not sure if this code is suitable for my situation, can you take a look? https://stackoverflow.com/questions/56671627/how-to-use-onchange-to-update-mysql-automatically-when-change-the-value — henrik, Jun 19 '19 at 15:53

Kevin Grosgojat · Answer 2 · 2019-06-11T08:51:02.153

You should use PUT or POST instead of GET to update datas.

PUT vs. POST in REST

To post datas the code below will post datas to the defined url and show an alert with the server response.

const http = new XMLHttpRequest();
const url = 'update.php';
const params = "id="+id+"&cost="+cost+"&profit_rate="+profit_rate+"&pprice="+pprice+"&currency_rate="+currency_rate+"&eurbuy="+eurbuy;
http.open('POST', url, true);
// Set header of the request
http.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');

http.onreadystatechange = function() {//Call a function when the state changes.
    // state 4 = ready, 200 status code = Success
    if(http.readyState == 4 && http.status == 200) {
        alert(http.responseText);
    }
}
http.send(params);

More about http status codes https://developer.mozilla.org/fr/docs/Web/HTTP/Status

In your update.php use $_POST or $_PUT to retrieve datas instead of $_GET.

score 0 · Answer 3 · answered Jun 11 '19 at 09:18

yes this will be easy if you are building the table using javascript instead of rendering it from backend using PHP in

xhr.onreadystatechange = function () {
  if(xhr.readyState === 4 && xhr.status === 200) {
    // build the row html like this 
    row = ''
    row += "<td><input name="cost" type="text" id="cost" value="+ cost+"></td>"
    // do the same for each column  
    // here get the DOM element of the table body and append the new row to it
    $('#_editable_table').append(row)
    //by inserting the form new rows to it using javascript 
  }
}

you are using GET instead of POST to create data
you are using JQUERY in index.php but uses low-level javascript API to send the ajax request in index.js

I think you are in your beginning steps learning ajax and php I think you need to have an organized course because there is much obsolete code here as an example mysql_query is deprecated i can suggest some good free articles and youtube channels to learn more if you like

How can I update all row from database and show updated data without refresh?

3 Answers3

Linked