jQuery UI Widgets › Forums › Grid › jqxgrid delete selected rows with PHP, PDO&Mysql
Tagged: datagrid, grid, grid mysql php pdo, javascript grid, jquery grid, jquery grid control, jqwidgets grid
This topic contains 11 replies, has 3 voices, and was last updated by michal.husak 9 years ago.
-
Author
-
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); } ?>
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 StoevjQWidgets Team
http://www.jqwidgets.com/Hi Peter
Thank you for your answer I changed getselectedrowindex to getselectedrowindexes but it still doesn’t work.
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'); });
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 StoevjQWidgets Team
http://www.jqwidgets.com/Thanks Peter.
Can you please give me one example of deleteButton.click(function (event) {delete all selected rows}
I’m using selectionmode: ‘checkbox’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 StoevjQWidgets Team
http://www.jqwidgets.com/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; });
Hi ben25,
You can use any selection mode and delete any row. These are not related.
Best Regards,
Peter StoevjQWidgets Team
http://www.jqwidgets.com/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.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
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'); });
-
AuthorPosts
You must be logged in to reply to this topic.