jQuery UI Widgets Forums Grid jqxgrid delete selected rows with PHP, PDO&Mysql

This topic contains 11 replies, has 3 voices, and was last updated by  michal.husak 9 years ago.

Viewing 12 posts - 1 through 12 (of 12 total)
  • Author

  • ben25
    Participant

    I’m using jqwidget 3.7.0 and PHP 5.4.6. I want to delete all rows selected by the check-boxes. With this code I can only populate the grid and update my database. I’m really confused and don’t understand why it is not working…any suggestion?

    Javascript :

    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var data = {};
            var theme = 'darkblue';
            var source =
            {
                 datatype: "json",
                 datafields: [
                     { name: 'EmployeeID', type: 'string'},
                     { name: 'FirstName', type: 'string'},
                     { name: 'LastName', type: 'string'},
                     { name: 'Title', type: 'string'},
                     { name: 'Address', type: 'string'},
                     { name: 'City', type: 'string'},
                     { name: 'Country', type: 'string'}
                ],
                cache: false,
                id: 'EmployeeID',
                url: 'data_pdo.php',
                addrow: function (rowid, rowdata) {
                    // synchronize with the server - send insert command
                    var data = "insert=true&" + $.param(rowdata);
                       $.ajax({
                            dataType: 'json',
                            url: 'data_pdo.php',
                            data: data,
                            success: function (data, status, xhr) { commit(true); },
                            error: function(jqXHR, textStatus, errorThrown) { commit(false); }
                        });
                },
                deleterow: function (rowid) {
                    // synchronize with the server - send delete command
                    var data = "delete=true&EmployeeID=" + rowid;
                    $.ajax({
                        dataType: 'json',
                        url: 'data.php',
                        data: data,
                        success: function (data, status, xhr) { commit(true); },
                        error: function(jqXHR, textStatus, errorThrown) { commit(false); }
                    }); 
               },
                updaterow: function (rowid, rowdata) {
                    // synchronize with the server - send update command
                       var data = "update=true&" + $.param(rowdata);
                          $.ajax({
                            dataType: 'json',
                            url: 'data_pdo.php',
                            data: data,
                            success: function (data, status, xhr) { commit(true); },
                            error: function(jqXHR, textStatus, errorThrown) { commit(false); } 
                        });
                }
            };
    
            var dataAdapter = new $.jqx.dataAdapter(source);
    
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid({
                autowidth: true,
                autoheight: true,
                source: dataAdapter,
                theme: theme,
                editable: true,
                selectionmode: 'checkbox',
                altrows: true,
                sortable: true,
                showstatusbar: true,
                renderstatusbar: function (statusbar) {
                    // appends buttons to the status bar.
                    var container = $("<div style='overflow: hidden; position: relative; margin: 5px;'></div>");
                    var addButton = $("<div style='float: left; margin-left: 5px;'><img style='position: relative; margin-top: 2px;' src='images/add.png'/><span style='margin-left: 4px; position: relative; top: -3px;'>Add</span></div>");
                    var deleteButton = $("<div style='float: left; margin-left: 5px;'><img style='position: relative; margin-top: 2px;' src='images/close.png'/><span style='margin-left: 4px; position: relative; top: -3px;'>Delete</span></div>");
                    container.append(addButton);
                    container.append(deleteButton);
                    statusbar.append(container);
                    addButton.jqxButton({  width: 60, height: 20 });
                    deleteButton.jqxButton({  width: 85, height: 20 });
                    // add new row.
                    addButton.click(function (event) {
                        $("#jqxgrid").jqxGrid('addrow', null, datarow[0]);
                    });
                    // delete selected row.
                    deleteButton.click(function (event) {
                        var selectedrowindex = $("#jqxgrid").jqxGrid('getselectedrowindex');
                        var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
                        var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
                        $("#jqxgrid").jqxGrid('deleterow', id);
                    });
                },
                columns: [
                      { text: 'EmployeeID', editable: false, datafield: 'EmployeeID', width: 100 },
                      { text: 'First Name', columntype: 'dropdownlist', datafield: 'FirstName', width: 100 },
                      { text: 'Last Name', columntype: 'dropdownlist', datafield: 'LastName', width: 100 },
                      { text: 'Title', datafield: 'Title', width: 180 },
                      { text: 'Address', datafield: 'Address', width: 180 },
                      { text: 'City', datafield: 'City', width: 100 },
                      { text: 'Country', datafield: 'Country', width: 140 }
                  ]
            });             
        });
    </script>

    data_pdo.php:

    <?PHP
    #Include the connect.php file
    include('connect.inc.php');
    
    $pdo = new DB();
    $pdo = $pdo->connexion();
    
    $employees_pdo = $pdo->prepare("SELECT * FROM employees;");
    $employees_pdo->execute();
    
    if (isset($_GET['insert'])){
        // INSERT COMMAND 
        $insert_query = $pdo->prepare("INSERT INTO <code>employees</code>(<code>FirstName</code>, <code>LastName</code>, <code>Title</code>, <code>Address</code>, <code>City</code>, <code>Country</code>, <code>Notes</code>) VALUES ('".$_GET['FirstName']."','".$_GET['LastName']."','".$_GET['Title']."','".$_GET['Address']."','".$_GET['City']."','".$_GET['Country']."','".$_GET['Notes']."')");
        $result = $insert_query->execute();
        echo $result;
    }
    else if (isset($_GET['update'])){
        // UPDATE COMMAND 
        $update_query = $pdo->prepare("UPDATE <code>employees</code> SET <code>FirstName</code>='".$_GET['FirstName']."',
        <code>LastName</code>='".$_GET['LastName']."',
        <code>Title</code>='".$_GET['Title']."',
        <code>Address</code>='".$_GET['Address']."',
        <code>City</code>='".$_GET['City']."',
        <code>Country</code>='".$_GET['Country']."',
        <code>Notes</code>='".$_GET['Notes']."' WHERE <code>EmployeeID</code>='".$_GET['EmployeeID']."'");
         $result = $update_query->execute();
         echo $result;
    
    }
    else if (isset($_GET['delete'])){
        // DELETE COMMAND 
        $delete_query = $pdo->prepare("DELETE FROM employees WHERE <code>EmployeeID</code>='".$_GET['EmployeeID']."';");
        $result = $delete_query->execute();
        echo $result;
    }
    else{
        // SELECT COMMAND
        while ($row = $employees_pdo->fetch (PDO::FETCH_ASSOC)) {
            $employees[] = array(
            'EmployeeID' => $row['EmployeeID'],
            'FirstName' => $row['FirstName'],
            'LastName' => $row['LastName'],
            'Title' => $row['Title'],
            'Address' => $row['Address'],
            'City' => $row['City'],
            'Country' => $row['Country'],
            'Notes' => $row['Notes']
            );
        }
        echo json_encode($employees);   
    }
    ?>

    Peter Stoev
    Keymaster

    Hi ben25,

    “getselectedrowindex” returns the index of 1 selected row when single row selection mode is used. May be you have to use “getselectedrowindexes” which returns the indexes of all selected rows.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/


    ben25
    Participant

    Hi Peter

    Thank you for your answer I changed getselectedrowindex to getselectedrowindexes but it still doesn’t work.


    ben25
    Participant

    I tried this but it doesn’t help :

    deleteButton.click(function (event) {
     // get the indexes of the selected rows.
    var selectedrowindexes = $("#jqxgrid").jqxGrid('getselectedrowindexes');
    var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
    // begin update. Stops the Grid's rendering.
    $("#jqxgrid").jqxGrid('beginupdate');
    selectedrowindexes.sort();
    // delete the selected rows by using the 'deleterow' method of jqxGrid.
    for (var m = 0; m < selectedrowindexes.length; m++) {
    var selectedrowindex = selectedrowindexes[selectedrowindexes.length - m - 1];
    if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
    var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
    $("#jqxgrid").jqxGrid('deleterow', id);
    }
    }
    // end update. Resume the Grid's rendering.
    $("#jqxgrid").jqxGrid('endupdate');
    });

    Peter Stoev
    Keymaster

    Hi ben25,

    deleterow method call deletes a row from the Grid i.e from the client side. The Grid calls “updaterow” callback function of the source object. There you should make Ajax call to your PHP page. The deletion from the Database should be handled by your PHP code.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/


    ben25
    Participant

    Thanks Peter.
    Can you please give me one example of deleteButton.click(function (event) {delete all selected rows}
    I’m using selectionmode: ‘checkbox’


    Peter Stoev
    Keymaster

    Hi ben25,

    Unfortunately, we don’t have exactly such example. We have example for row deletion using a single row selection mode. You can find it in the demos section – Create, Remove, Update. For PHP, you can find it in the download package – phpdemos folder.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/


    ben25
    Participant

    Hi Peter

    Can you confirm me that I can use selectionmode checkbox and delete the selected row?

    My page is based on the example you told me. Where Im struggling is to combine selection mode checkbox and the deletebuttonclick.
    Im a bit lost honestly. I assume my PHP page is ok. I used the demo page in the download package and I just changed mysqlconnect and query to PDO.

    I tried already this:

    $("#deleteRows").click(function () {
       var rowIndexes = $('#jqxgrid').jqxGrid('getselectedrowindexes');
       var rowIds = new Array();
       for (var i = 0; i < rowIndexes.length; i++) {
           var currentId = $('#jqxgrid').jqxGrid('getrowid', rowIndexes[i]);
           rowIds.push(currentId);
       };
       $('#jqxgrid').jqxGrid('deleterow', rowIds);
       $('#jqxgrid').jqxGrid('clearselection');
    });

    And this one :

    deleteButton.click(function (event) {
       // get the indexes of the selected rows.
       var selectedrowindexes = $("#jqxgrid").jqxGrid('getselectedrowindexes');
       var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
       // begin update. Stops the Grid's rendering.
       $("#jqxgrid").jqxGrid('beginupdate');
       selectedrowindexes.sort();
       // delete the selected rows by using the 'deleterow' method of jqxGrid.
       for (var m = 0; m < selectedrowindexes.length; m++) {
          var selectedrowindex = selectedrowindexes[selectedrowindexes.length - m - 1];
          if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
             var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
             $("#jqxgrid").jqxGrid('deleterow', id);
          }
       }
       // end update. Resume the Grid's rendering.
       $("#jqxgrid").jqxGrid('endupdate');
    });

    But none of them are working.
    Must I used :

    $("#jqxgrid").bind('rowselect', function (event) {
    var selectedRowIndex = event.args.rowindex;
    });

    Peter Stoev
    Keymaster

    Hi ben25,

    You can use any selection mode and delete any row. These are not related.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    http://www.jqwidgets.com/


    ben25
    Participant

    Hi Peter

    Thanks, that is good news already.
    Unfortunately I don’t know how to do this. I tried many time but I didn’t figure out what is wrong in my code.

    Any help would be the welcome.
    I’m surprise how difficult it could be to delete multiple selected rows by a checkbox in a grid.


    michal.husak
    Participant

    Hi ben25,

    I know it’s quite late for the answer, but I’m struggled with the same problem and I can’t see any effort to solve this problem from the jQWidgets Team.

    Your code:

    
    deleteButton.click(function (event) {
       // get the indexes of the selected rows.
       var selectedrowindexes = $("#jqxgrid").jqxGrid('getselectedrowindexes');
       var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
       // begin update. Stops the Grid's rendering.
       $("#jqxgrid").jqxGrid('beginupdate');
       selectedrowindexes.sort();
       // delete the selected rows by using the 'deleterow' method of jqxGrid.
       for (var m = 0; m < selectedrowindexes.length; m++) {
          var selectedrowindex = selectedrowindexes[selectedrowindexes.length - m - 1];
          if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
             var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
             $("#jqxgrid").jqxGrid('deleterow', id);
          }
       }
       // end update. Resume the Grid's rendering.
       $("#jqxgrid").jqxGrid('endupdate');
    });
    

    is actually good. Only problem is that the array selectedrowindexes changes when you call $("#jqxgrid").jqxGrid('deleterow', id);. So you need to create a copy of the $("#jqxgrid").jqxGrid('getselectedrowindexes'); and save it to the selectedrowindexes variable.

    Best regards

    Michal


    michal.husak
    Participant

    I’ve found out that this also doesn’t work for some special cases of selected rows.

    But this code seems to work:

    deleteButton.click(function (event) {
       // get the indexes of the selected rows.
       var selectedrowindexes = $("#jqxgrid").jqxGrid('getselectedrowindexes');
       var rowscount = $("#jqxgrid").jqxGrid('getdatainformation').rowscount;
       var arrayOfSelectedIds = [];
       // begin update. Stops the Grid's rendering.
       $("#jqxgrid").jqxGrid('beginupdate');
       selectedrowindexes.sort();
       // delete the selected rows by using the 'deleterow' method of jqxGrid.
       for (var m = 0; m < selectedrowindexes.length; m++) {
          var selectedrowindex = selectedrowindexes[selectedrowindexes.length - m - 1];
          if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
             var id = $("#jqxgrid").jqxGrid('getrowid', selectedrowindex);
             arrayOfSelectedIds.push(id);
          }
       }
       $("#jqxgrid").jqxGrid('deleterow', arrayOfSelectedIds);
       // end update. Resume the Grid's rendering.
       $("#jqxgrid").jqxGrid('endupdate');
    });
    
Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.