1

I have a table which fetches data from a server and can be edited too. When I update a few data fields, it updates perfectly, but if I try to update lots of data fields together then most of the fields fails to update.

I think SQL is a lot slower than PHP and PHP is not waiting for the SQL query to finish which causes code to assume it didn't work and hence shows an error. I also tried sleep(), but even after sleep(2); it still misses some values if I make lots of changes. This is my jQuery code:

$(document).ready(function(){
    $("#edit").click(function(){
        $("td").attr('contenteditable', 'true');
    });
    var old_message;
    $("td").click(function(){
        old_message = $(this).text();
    });

    $("td").blur(function(){
        var new_message = $(this).text();
        if(old_message!=new_message)
        {
            $(this).addClass("changed");
            old_message = null;
            new_message = null;
        }
    });

    $("#submit").click(function(){
        $(".changed").each(function(){
            var row_num = $(this).siblings().eq(0).text();
            var col_name = $(this).attr("id");
            var value = $(this).text();
            var current = $(this);
            $.post("../php/update_table.php",
                {
                    id:row_num,
                    cn:col_name,
                    data:value
                },
                function(response,status){
                    if(response=="updated")
                    {
                        $(current).removeClass("changed");
                    }
                    else
                    {
                        alert("Failed");
                    }
                });
        });
        location.reload(true);
    });
});

This is my update.php:

<?php
    if(isset($_POST['id']) && isset($_POST['cn']) && isset($_POST['data']))
    {
        $id = $_POST['id'];
        $col_name = $_POST['cn'];
        $data = $_POST['data'];
        include "mysql_login.php";
        $mysql_database = "bus_database";
        $connect = mysqli_connect($host, $mysql_user, $mysql_pass, $mysql_database) or die("Couldn't Connect To The Database");
    $time = false;
    if($col_name=='a1_a1t' || $col_name=='a2_a2t' || $col_name=='d1_d1t' || $col_name=='d2_d2t')
    {
        $t_data = substr($data,0,-2);
        $tz_data = substr($data, 9);
        $t = substr($col_name,0, 2);
        $tz = substr($col_name, 3);
        $time = true;
    }
    if($time)
    {
        if($t_data == '' || $tz_data == '')
        {
            $t_data = "NULL";
            $tz_data = "NULL";
            $result = mysqli_query($connect, "UPDATE buses SET $t=$t_data, $tz=$tz_data WHERE id='$id'");
        }
        else
        {
            $result = mysqli_query($connect, "UPDATE buses SET $t='$t_data', $tz='$tz_data' WHERE id='$id'");
        }

        if($result)
        {
            echo("updated");
        }
        else
        {
            echo("failed");
        }
    }
    else if($col_name == 'route' || $col_name == 'bus_num')
    {
        $result = mysqli_query($connect, "UPDATE buses SET $col_name='$data' WHERE id='$id'");
        if($result)
        {
            echo("updated");
        }
        else
        {
            echo("failed");
        }
    }
    mysqli_close($connect);
    }
    else
    {
        echo("Failed");
    }
?>
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Fateslayer
  • 91
  • 7
  • 1
    Consult these following links http://php.net/manual/en/mysqli.error.php and http://php.net/manual/en/function.error-reporting.php and apply that to your code. – Funk Forty Niner Feb 29 '16 at 12:57
  • 1
    you quoted possible strings in one query, but not the other. – Funk Forty Niner Feb 29 '16 at 12:58
  • 1
    possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) – Funk Forty Niner Feb 29 '16 at 12:58
  • 1
    `location.reload` should be in the callback or don't use an async POST (though in this case what's the point of using **A**JAX) – apokryfos Feb 29 '16 at 12:59
  • 1
    *"it didn't Worked and hence shows error."* - Being what *exactly?* Or, are we left to talk amongst ourselves here and debug the code *for you?* – Funk Forty Niner Feb 29 '16 at 13:02
  • thanks @apokryfos for your advice, i am using location.reload() now. Sorry i couldn't express myself clearly Fred -ii- – Fateslayer Mar 01 '16 at 08:41
  • @Fateslayer The point of my comment was that you're reloading the location before you're sure the AJAX request was completed so it may be possible to get the site to reload before the update operation was complete. – apokryfos Mar 01 '16 at 08:50

1 Answers1

1

I was using version 5 of XAAMP which caused the problem. Version 7 fixed everything.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Fateslayer
  • 91
  • 7