jqxgrid sql delete multiple rows - Angular, Vue, React, Web Components, Javascript, HTML5 Widgets

Tagged: grid, javascript grid, jquery grid, jqwidgets grid

This topic contains 2 replies, has 2 voices, and was last updated by Peter Stoev 7 years, 11 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)

Author

jqxgrid sql delete multiple rows Posts
January 1, 2017 at 11:46 pm jqxgrid sql delete multiple rows #90227

dan123
Participant

Hi I have been looking at this example link of php sql http://www.jqwidgets.com/jquery-widgets-documentation/documentation/phpintegration/php-server-side-grid-crud.htm

I want to know how to delete multiple rows, I did the same example link above and I cant seem to delete multiple rows on checkbox selection.

Please help

January 1, 2017 at 11:57 pm jqxgrid sql delete multiple rows #90228

dan123
Participant

Here is my code: For some reason I can delete the selected rows, but its not updating in the mysql. When I refresh the records that I deleted are there again

data.php

<?php
#Include the connect.php file
include (‘connect.php’);
// Connect to the database
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno())
{
printf(“Connect failed: %s\n”, mysqli_connect_error());
exit();
}
// get data and store in a json array
$query = “SELECT EmployeeID, FirstName, LastName, Title, Address, City, Country, Notes FROM employees”;
if (isset($_GET[‘insert’]))
{
// INSERT COMMAND
$query = “INSERT INTO employees(FirstName, LastName, Title, Address, City, Country, Notes) VALUES (?,?,?,?,?,?,?)”;
$result = $mysqli->prepare($query);
$result->bind_param(‘sssssss’, $_GET[‘FirstName’], $_GET[‘LastName’], $_GET[‘Title’], $_GET[‘Address’], $_GET[‘City’], $_GET[‘Country’], $_GET[‘Notes’]);
$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
// printf (“New Record has id %d.\n”, $mysqli->insert_id);
echo $res;
}
else if (isset($_GET[‘update’]))
{
// UPDATE COMMAND
$query = “UPDATE employees SET FirstName=?, LastName=?, Title=?, Address=?, City=?, Country=?, Notes=? WHERE EmployeeID=?”;
$result = $mysqli->prepare($query);
$result->bind_param(‘sssssssi’, $_GET[‘FirstName’], $_GET[‘LastName’], $_GET[‘Title’], $_GET[‘Address’], $_GET[‘City’], $_GET[‘Country’], $_GET[‘Notes’], $_GET[‘EmployeeID’]);
$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
// printf (“Updated Record has id %d.\n”, $_GET[‘EmployeeID’]);
echo $res;
}
else if (isset($_GET[‘delete’]))
{
// DELETE COMMAND
$query = “DELETE FROM employees WHERE EmployeeID=?”;
$result = $mysqli->prepare($query);
$result->bind_param(‘i’, $_GET[‘EmployeeID’]);
$res = $result->execute() or trigger_error($result->error, E_USER_ERROR);
// printf (“Deleted Record has id %d.\n”, $_GET[‘EmployeeID’]);
echo $res;
}
else
{
// SELECT COMMAND
$result = $mysqli->prepare($query);
$result->execute();
/* bind result variables */
$result->bind_result($EmployeeID, $FirstName, $LastName, $Title, $Address, $City, $Country, $Notes);
/* fetch values */
while ($result->fetch())
{
$employees[] = array(
‘EmployeeID’ => $EmployeeID,
‘FirstName’ => $FirstName,
‘LastName’ => $LastName,
‘Title’ => $Title,
‘Address’ => $Address,
‘City’ => $City,
‘Country’ => $Country,
‘Notes’ => $Notes
);
}
echo json_encode($employees);
}
$result->close();
$mysqli->close();
/* close connection */
?>

Php code:
<html lang=”en”>
<head>
<link rel=”stylesheet” href=”jqwidgets/styles/jqx.base.css” type=”text/css”>
<link rel=”stylesheet” href=”jqwidgets/styles/jqx.ar.css” type=”text/css”>
<script type=”text/javascript” src=”scripts/jquery-1.11.1.min.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxcore.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdata.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxtabs.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxbuttons.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxscrollbar.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxmenu.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.selection.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.sort.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.filter.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxcheckbox.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.columnsresize.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.edit.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.columnsreorder.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.pager.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxexpander.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxlistbox.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxcombobox.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdropdownlist.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxinput.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxsplitter.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxpanel.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqx-all.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxtooltip.js”></script>
<script type=”text/javascript” src=”scripts/demos.js”></script>
<script type=”text/javascript”>
$(document).ready(function () {
// prepare the data
var data = {};
var theme = ‘jqx-ar’;
var firstNames = [“Nancy”, “Andrew”, “Janet”, “Margaret”, “Steven”, “Michael”, “Robert”, “Laura”, “Anne”];
var lastNames = [“Davolio”, “Fuller”, “Leverling”, “Peacock”, “Buchanan”, “Suyama”, “King”, “Callahan”, “Dodsworth”];
var titles = [“Sales Representative”, “Vice President, Sales”, “Sales Representative”, “Sales Representative”, “Sales Manager”, “Sales Representative”, “Sales Representative”, “Inside Sales Coordinator”, “Sales Representative”];
var address = [“507 – 20th Ave. E. Apt. 2A”, “908 W. Capital Way”, “722 Moss Bay Blvd.”, “4110 Old Redmond Rd.”, “14 Garrett Hill”, “Coventry House”, “Miner Rd.”, “Edgeham Hollow”, “Winchester Way”, “4726 – 11th Ave. N.E.”, “7 Houndstooth Rd.”];
var city = [“Seattle”, “Tacoma”, “Kirkland”, “Redmond”, “London”, “London”, “London”, “Seattle”, “London”];
var country = [“USA”, “USA”, “USA”, “USA”, “UK”, “UK”, “UK”, “USA”, “UK”];
var generaterow = function (id) {
var row = {};
var firtnameindex = Math.floor(Math.random() * firstNames.length);
var lastnameindex = Math.floor(Math.random() * lastNames.length);
var k = firtnameindex;
row[“EmployeeID”] = id;
row[“FirstName”] = firstNames[firtnameindex];
row[“LastName”] = lastNames[lastnameindex];
row[“Title”] = titles[k];
row[“Address”] = address[k];
row[“City”] = city[k];
row[“Country”] = country[k];
row[“available”];
row[“Notes”] = row[“FirstName”] + ‘ received a BA in computer science from the University of Washington’;
return row;
}
for (var i = 0; i < 10; i++) {
var row = generaterow(i);
data[i] = row;
}
var source =
{
datatype: “json”,
cache: false,
datafields: [
{ name: ‘EmployeeID’ },
{ name: ‘FirstName’ },
{ name: ‘LastName’ },
{ name: ‘Title’ },
{ name: ‘Address’ },
{ name: ‘City’ },
{ name: ‘Country’ },
{ name: ‘Notes’ },
{ name: ‘available’ }
],
id: ‘EmployeeID’,
url: ‘data.php’,
addrow: function (rowid, rowdata, position, commit) {
// synchronize with the server – send insert command
var data = “insert=true&” + $.param(rowdata);
$.ajax({
dataType: ‘json’,
url: ‘data.php’,
data: data,
cache: false,
success: function (data, status, xhr) {
// insert command is executed.
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
commit(false);
}
});
},
deleterow: function (rowid, commit) {
// synchronize with the server – send delete command
var data = “delete=true&” + $.param({ EmployeeID: rowid });
$.ajax({
dataType: ‘json’,
url: ‘data.php’,
cache: false,
data: data,
success: function (data, status, xhr) {
// delete command is executed.
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
commit(false);
}
});
},
updaterow: function (rowid, rowdata, commit) {
// synchronize with the server – send update command
var data = “update=true&” + $.param(rowdata);
$.ajax({
dataType: ‘json’,
url: ‘data.php’,
cache: false,
data: data,
success: function (data, status, xhr) {
// update command is executed.
commit(true);
},
error: function (jqXHR, textStatus, errorThrown) {
commit(false);
}
});
}
};
var dataAdapter = new $.jqx.dataAdapter(source);
// initialize jqxGrid
var local_length = source.length;
var columnCheckBox = null;
var updatingCheckState = false;
$(“#jqxgrid”).jqxGrid(
{
source: dataAdapter,
//keyboardnavigation: false,
filterable: true,
sortable: true,
editable: true,
showfilterrow: true,
altrows: true,
autoheight: true,
enablehover: true,
pageable: true,
pagesize: 15,
columnsresize: true,
columnsreorder: true,
//rowsheight: 35,
enablebrowserselection: true,
theme:’jqx-ar’,
selectionmode : ‘none’,
keyboardnavigation: false,
columns: [
{ text: ”, menu: false, sortable: false, datafield: ‘available’, columntype: ‘checkbox’, width: 30, editable: true, filterable: false, pinned: true,
renderer: function () {
return ‘<div style=”margin-left: 5px; margin-top: 2px;”><div id=”selectall”></div></div>’;
},
rendered: function (element) {
var checkbox = $(element).last();
$(checkbox).jqxCheckBox({ width: 25, height: 25, animationShowDelay: 0, animationHideDelay: 0 });
columnCheckBox = $(checkbox);
$(checkbox).on(‘change’, function (event) {
var checked = event.args.checked;
var pageinfo = $(“#jqxgrid”).jqxGrid(‘getpaginginformation’);
var pagenum = pageinfo.pagenum;
var pagesize = pageinfo.pagesize;

if (checked == null || updatingCheckState) return;
$(“#jqxgrid”).jqxGrid(‘beginupdate’);

// select all rows when the column’s checkbox is checked.
if (checked) {
$(“#jqxgrid”).jqxGrid(‘setcellvalue’, i, ‘available’);
for (var i=0; i<local_length; i++) {
$(‘#jqxgrid’).jqxGrid(‘selectrow’, 0);
}
}
else if (checked == false) {
$(“#jqxgrid”).jqxGrid(‘setcellvalue’, i, ‘available’);
for (var i=0; i<local_length; i++) {
if ($(‘#jqxgrid’).jqxGrid(‘unselectrow’, i)) {

}
}

}

// update cells values.
var startrow = pagenum * pagesize;
for (var i = startrow; i < startrow + pagesize; i++) {
// The bound index represents the row’s unique index.
// Ex: If you have rows A, B and C with bound indexes 0, 1 and 2, afer sorting, the Grid will display C, B, A i.e the C’s bound index will be 2, but its visible index will be 0.
// The code below gets the bound index of the displayed row and updates the value of the row’s available column.
var boundindex = $(“#jqxgrid”).jqxGrid(‘getrowboundindex’, i);
$(“#jqxgrid”).jqxGrid(‘setcellvalue’, boundindex, ‘available’, event.args.checked);
}

$(“#jqxgrid”).jqxGrid(‘endupdate’);
});
return true;
}
},
{ text: ‘EmployeeID’, datafield: ‘EmployeeID’, width: 100 },
{ text: ‘First Name’, datafield: ‘FirstName’, width: 100 },
{ text: ‘Last Name’, 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 }
],
showtoolbar: true,
autoheight: true,
rendertoolbar: function (toolbar) {
var me = this;
var container = $(“<div style=’margin: 5px;’></div>”);
var input = $(“<input value=’Button’ class=’jqx-input jqx-widget-content jqx-rc-all’ id=’searchField’ type=’button’ style=’height: 23px; float: left; width: 223px;’ />”);
toolbar.append(container);
container.append(input);
input.jqxButton({ theme: ‘jqx-ar’ ,height: ’30’, width: ’50’ });
input.click(function (event) {
var rowscount = $(“#jqxgrid”).jqxGrid(‘getdatainformation’).rowscount;
var datarow = generaterow(rowscount + 1);
$(“#jqxgrid”).jqxGrid(‘addrow’, null, datarow);
});
}
});
var updatePageState = function (pagenum) {
var datainfo = $(“#jqxgrid”).jqxGrid(‘getdatainformation’);
var pagenum = datainfo.paginginformation.pagenum;
var pagesize = datainfo.paginginformation.pagesize;
var startrow = pagenum * pagesize;
// select the rows on the page.
$(“#jqxgrid”).jqxGrid(‘beginupdate’);
var checkedItemsCount = 0;
for (var i = startrow; i < startrow + pagesize; i++) {
var boundindex = $(“#jqxgrid”).jqxGrid(‘getrowboundindex’, i);
var value = $(“#jqxgrid”).jqxGrid(‘getcellvalue’, boundindex, ‘available’);
if (value) checkedItemsCount++;
if (value) {
$(“#jqxgrid”).jqxGrid(‘selectrow’, boundindex);
}
else {
$(“#jqxgrid”).jqxGrid(‘unselectrow’, boundindex);
}
}

$(“#jqxgrid”).jqxGrid(‘endupdate’);
if (checkedItemsCount == pagesize) {
columnCheckBox.jqxCheckBox({ checked: true });
}
else if (checkedItemsCount == 0) {
columnCheckBox.jqxCheckBox({ checked: false });
}
else {
columnCheckBox.jqxCheckBox({ checked: null });
}
}

// update the selection after sort.
$(“#jqxgrid”).on(‘sort’, function (event) {
updatePageState();
});

// update the selection after page change.
$(“#jqxgrid”).on(‘pagechanged’, function (event) {
updatePageState();
});

// select or unselect rows when a checkbox is checked or unchecked.
$(“#jqxgrid”).on(‘cellvaluechanged’, function (event) {
if (event.args.value) {
$(“#jqxgrid”).jqxGrid(‘selectrow’, event.args.rowindex);
}
else {
$(“#jqxgrid”).jqxGrid(‘unselectrow’, event.args.rowindex);
}

// update the state of the column’s checkbox. When all checkboxes on the displayed page are checked, we need to check column’s checkbox. We uncheck it,
// when there are no checked checkboxes on the page and set it to intederminate state when there is at least one checkbox checked on the page.
if (columnCheckBox) {
var datainfo = $(“#jqxgrid”).jqxGrid(‘getdatainformation’);
var pagesize = datainfo.paginginformation.pagesize;
var pagenum = datainfo.paginginformation.pagenum;
var selectedRows = $(“#jqxgrid”).jqxGrid(‘getselectedrowindexes’);
var state = false;
var count = 0;
$.each(selectedRows, function () {
if (pagenum * pagesize <= this && this < pagenum * pagesize + pagesize) {
count++;
}
});

if (count != 0) state = null;
if (count == pagesize) state = true;
if (count == 0) state = false;

updatingCheckState = true;
$(columnCheckBox).jqxCheckBox({ checked: state });

updatingCheckState = false;
}
});

// select or unselect rows when the checkbox is checked or unchecked.
$(“#jqxgrid”).bind(‘cellendedit’, function (event) {
if (event.args.value) {
$(“#jqxgrid”).jqxGrid(‘selectrow’, event.args.rowindex);
}
else {
$(“#jqxgrid”).jqxGrid(‘unselectrow’, event.args.rowindex);
}
});

$(“#addrowbutton”).jqxButton({ theme: ‘jqx-ar’ });
$(“#deleterowbutton”).jqxButton({ theme: ‘jqx-ar’ });
$(“#updaterowbutton”).jqxButton({ theme: ‘jqx-ar’ });
// update row.
$(“#updaterowbutton”).bind(‘click’, function () {
var datarow = generaterow();
var selectedrowindex = $(“#jqxgrid”).jqxGrid(‘getselectedrowindex’);
var rowscount = $(“#jqxgrid”).jqxGrid(‘getdatainformation’).rowscount;
if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
var id = $(“#jqxgrid”).jqxGrid(‘getrowid’, selectedrowindex);
$(“#jqxgrid”).jqxGrid(‘updaterow’, id, datarow);
}
});
// create new row.
$(“#addrowbutton”).on(‘click’, function () {
var datarow = generaterow();
var commit = $(“#jqxgrid”).jqxGrid(‘addrow’, null, datarow);
});
// delete row.
$(“#deleterowbutton”).on(‘click’, function () {
// 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’);

});
});
</script>
</head>
<body class=’default’>
<div id=’jqxWidget’ style=”font-size: 13px; font-family: Verdana; float: left;”>
<div style=”float: left;” id=”jqxgrid”>
</div>
<div style=”margin-left: 30px; float: left;”>
<div>
<input id=”addrowbutton” type=”button” value=”Add New Row” />
</div>
<div style=”margin-top: 10px;”>
<input id=”deleterowbutton” type=”button” value=”Delete Selected Row” />
</div>
<div style=”margin-top: 10px;”>
<input id=”updaterowbutton” type=”button” value=”Update Selected Row” />
</div>
</div>
</div>
</body>
</html>

January 2, 2017 at 8:38 am jqxgrid sql delete multiple rows #90229

Peter Stoev
Keymaster

Hi Dan123,

To delete multiple rows, you should not implement the source object’s deleterow function which is for deleting a single row. After calling the Grid’s deleterow and passing and array of multiple row IDs as an argument, invoke your PHP function to delete them from your DB, too.

Best Regards,
Peter Stoev

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

Posts

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.