Live Editable Table with jQuery, PHP and MySQL-CodePudding

I try to make a Live HTML table edit or inline table edit. The goal is that users can edit HTML table value directly by clicking on table cells. I use jQuery plugin Tabledit that provides AJAX enabled in place editing for HTML table cells.

How the table works:

selecting stock and month and show only those datas in the table "this part is working"
updating table data by clicking on it and write in the new data "not working for all columns"

Problem:

Only the <td><?php echo $row['barcode'];?></td> and <td><?php echo $row['productname'];?></td> can be updated and <td><?php echo $row['Tmonth'];?></td> is not updating.

I think the problem is with the live_edit.php but I dont know how to solv it that the Tmonth is updating also.

MySQL data table:

azon	barcode	productname	unit	stockmax	stockmin	area	stock	ean	1	2	3	4	5	6	7	8	9	10	11	12
1	er23	name	l	10	5	w123	100	123235345	0	0	0	0	0	0	0	0	0	0	0	0

index.php:

    include 'settings.php';

    $title = 'Leltározás';
    
    require_once 'components/htmltop.php';

    require_once 'components/navbar.php';

    ?>
<script type="text/javascript" src="dist/jquery.tabledit.js"></script>
    <div  ><br>

                    <form action="" method="GET">
                        <div >

                            <div >
                                <div >
                                <label>Kereső</label>
                                    <input  id="input" type="text"> 
                                </div>
                            </div>
                            <div >
                                <div >
                                    <label>Raktár</label>
                                    <select type="text" name="stock"  required>
                                        <option disabled selected value> -- válasz -- </option>
                                        <option value="Festék">Festék</option>
                                        <option value="Beépülő">Beépülő</option>
                                        <option value="Csomagoló">Csomagoló</option>
                                        <option value="E.Temetkezési">E.Temetkezési</option>
                                        <option value="Koporsó">Koporsó</option>
                                        <option value="Gyártási">Gyártási</option>
                                        <option value="Léc">Léc</option>
                                        <option value="Bélés">Bélés</option>
                                        <option value="Vásárolt Koporsó">Vásárolt Koporsó</option>
                                        <option value="Brikett">Brikett</option>
                                        <option value="Karbantartás">Karbantartás</option>
                                        <option value="Munkavédelem">Munkavédelem</option>
                                        <option value="Rönktér">Rönktér</option>
                                        <option value="Máglyatér">Máglyatér</option>
                                        <option value="Szárítók">Szárítók</option>
                                        <option value="Szárazfa tároló">Szárazfa tároló</option>   
                                        <option value="Fűrész">Fűrész</option>    
                                    </select>
                                </div>
                            </div>
                            <div >
                                <div >
                                    <label>Leltár Hónap</label>
                                    <select type="text" name="Lmonth"  required>
                                        <option disabled selected value> -- válasz -- </option>
                                        <option value="1">Január</option>
                                        <option value="2">Február</option>
                                        <option value="3">Március</option>
                                        <option value="4">Április</option>
                                        <option value="5">Május</option>
                                        <option value="6">Június</option>
                                        <option value="7">Július</option>
                                        <option value="8">Augusztus</option>
                                        <option value="9">Szeptember</option>
                                        <option value="10">Október</option>
                                        <option value="11">November</option>
                                        <option value="12">December</option>
                                    </select>
                                </div>
                            </div>

                            <div >
                                <div ><br>
                                    <button type="submit" >Szűrés</button>
                                </div><br>
                            </div>
                        </div>
                    </form>

        <div >
            <table id="data_table" >
                <thead >
                    <th>Azon</th>
                    <th>Azonosító</th>
                    <th>Megnevezés</th>
                    <th>Leltár</th>
                </thead>
                <tbody id="table">
                    <?php
    
                if(isset($_GET['stock']) && isset($_GET['Lmonth']) )
                    {

                        $stock = $_GET['stock'];
                        $Lmonth = $_GET['Lmonth'];

                        $sql_workers = 
                        "SELECT s.$Lmonth AS Tmonth, s.stockmin, s.stockmax, s.azon, s.barcode, s.productname,coalesce(si.total, 0) 
                        AS be, coalesce(so.total, 0) 
                        AS ki, coalesce(si.total, 0) - coalesce(so.total, 0) 
                        AS balance, s.unit 
                        FROM stock_list s 
                        LEFT JOIN (select barcode, sum(amount) as total
                        from be
                        group by barcode) si on si.barcode = s.barcode
                        LEFT JOIN (select barcode, sum(amount) as total
                        from ki
                        group by barcode) so on so.barcode = s.barcode
                        WHERE area='$stock'
                        ORDER BY barcode";

                        $result_workers = mysqli_query($conn, $sql_workers);

                        while($row = mysqli_fetch_assoc($result_workers)) {

                          
                            ?>
                            <tr id="<?php echo $row['azon'];?>">
                                <td><?php echo $row['azon'];?></td>
                                <td><?php echo $row['barcode'];?></td>
                                <td><?php echo $row['productname'];?></td>
                                <td><?php echo $row['Tmonth'];?></td>
                            </tr>
                            <?php
                        }
                    }
                    ?>
                </tbody>
            </table>                 
    </div>
</div>

<script type="text/javascript" src="custom_table_edit.js"></script>

<script>
$(document).ready(function(){
  $("#input").on("keyup", function() {
    var value = $(this).val().toLowerCase();
    $("#table tr").filter(function() {
      $(this).toggle($(this).text().toLowerCase().indexOf(value) > -1)
    });
  });
});
</script>
<?php
    require_once 'components/htmlbottom.php';
?>

live_edit.php:

include_once("settings.php");

$input = filter_input_array(INPUT_POST);

if ($input['action'] == 'edit') {   
    $update_field='';
    if(isset($input['barcode'])) {
        $update_field.= "barcode='".$input['barcode']."'";
    } else if(isset($input['productname'])) {
        $update_field.= "productname='".$input['productname']."'";
    } else if(isset($input['Tmonth'])) {
        $update_field.= "Tmonth='".$input['Tmonth']."'";
    }   
    if($update_field && $input['azon']) {
        $sql_query = "UPDATE stock_list SET $update_field WHERE azon='" . $input['azon'] . "'"; 
        mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));     
    }
}

custom_table_edit.js:

    $('#data_table').Tabledit({
        deleteButton: false,
        editButton: false,          
        columns: {
          identifier: [0, 'azon'],                    
          editable: [[1, 'barcode'], [2, 'productname'], [3, 'Tmonth']]
        },
        hideIdentifier: true,
        url: 'live_edit.php'        
    });
}); ```

CodePudding user response：

In your HTML-Form you use Lmonth for the input field name, but in your live_edit.php file you try to read the month value using Tmonth.

Change the line:

$update_field.= "Tmonth='".$input['Tmonth']."'";

$update_field.= "Tmonth='".$input['Lmonth']."'";

CodePudding user response：

Change this live_edit.php file as below

include_once("settings.php");

$input = filter_input_array(INPUT_POST);

if ($input['action'] == 'edit') {   
    $update_field='';
    if(isset($input['barcode'])) {
        $update_field.= "barcode='".$input['barcode']."'";
    } else if(isset($input['productname'])) {
        $update_field.= "productname='".$input['productname']."'";
    } else if(isset($input['Tmonth'])) {
        $update_field.= "Tmonth='".$input['Tmonth']."'";
    }   
    if($update_field && $input['azon']) {
        $sql_query = "UPDATE stock_list SET $update_field WHERE azon='" . $input['azon'] . "'"; 
        mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));     
    }
}

include_once("settings.php");

$input = filter_input_array(INPUT_POST);

if ($input['action'] == 'edit') {   
    $update_field='';
    if(isset($input['barcode'])) {
        $update_field.= "barcode='".$input['barcode']."'";
    } 
    if(isset($input['productname'])) {
        $update_field.= "productname='".$input['productname']."'";
    } 
    if(isset($input['Tmonth'])) {
        $update_field.= "Tmonth='".$input['Tmonth']."'";
    }   
    if($update_field && $input['azon']) {
        $sql_query = "UPDATE stock_list SET $update_field WHERE azon='" . $input['azon'] . "'"; 
        mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));     
    }
}