nested grid and paging – number of

[kathylParticipant]

I currently have a nested grid that is working fine. It is customers and their orders. One customer may have multiple orders. urrent everything works fine, except for paging. If I set my page to display “20”records, it may show only 10 customer rows – because each customer has 2 orders, this makes 20 rows. What I really want to show is 20 customers, and their orders. Is there a way to do this or am I doing something wrong?

[DimitarParticipant]

Hello kathyl,

Please clarify – are the customers in the parent grid and their orders in the nested grids or this information is displayed in the nested grids only? Generally, the pagesize option does not “take in mind” the existence of row details/nested grids – a row and its nested grid is considered only one record.

Best Regards,
Dimitar

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

[kathylParticipant]

Dimitar, Yes the customers are the parent grid and their orders in the nested grid. I think I have narrowed it down to the sorting function. The filter function seems to display the correct number of records. Below is my sorting code, direct from the phpdemos/sorting-filtering-paging demo. When I choose to sort by any field, I get a response of 6-8 records on the first page, then 6-8 on the second page, etc. This is instead of 10 records on each page. I can’t figure out what the issue is with the sorting.

$new_total_rows = 0;
$pagenum = \htmlspecialchars($_GET['pagenum']);
$pagesize = \htmlspecialchars($_GET['pagesize']);
$start = $pagenum * $pagesize;
$result = $mysqli->prepare($limitQuery);
$result->bind_param('ii', $start, $pagesize);

$filterquery = "";
if (isset($_GET['filterscount']))
	{
	$filterscount = \htmlspecialchars($_GET['filterscount']);
	if ($filterscount > 0)
		{
		$where = "  AND  (";
		$tmpdatafield = "";
		$tmpfilteroperator = "";
		$valuesPrep = "";
		$value = [];
		for ($i = 0; $i < $filterscount; $i++)
		{
			$filtervalue = \htmlspecialchars($_GET["filtervalue" . $i]);
			$filtercondition = \htmlspecialchars($_GET["filtercondition" . $i]);
			$filterdatafield = \htmlspecialchars($_GET["filterdatafield" . $i]);
			$filteroperator = \htmlspecialchars($_GET["filteroperator" . $i]);
			if ($tmpdatafield == "")
				{
				$tmpdatafield = $filterdatafield;
				}
			  else if ($tmpdatafield <> $filterdatafield)
				{
				$where.= ") AND (";
				}
			  else if ($tmpdatafield == $filterdatafield)
				{
				if ($tmpfilteroperator == 0)
					{
					$where.= " AND ";
					}
				  else $where.= " OR ";
				}
			// build the "WHERE" clause depending on the filter's condition, value and datafield.
			switch ($filtercondition)
			{
			case "CONTAINS":
				$condition = " LIKE ";
				$value[0][$i] = "%{$filtervalue}%";
				$values[] = & $value[0][$i];
				break;

			case "DOES_NOT_CONTAIN":
				$condition = " NOT LIKE ";
				$value[1][$i] = "%{$filtervalue}%";
				$values[] = & $value[1][$i];
				break;

			case "EQUAL":
				$condition = " = ";
				$value[2][$i] = $filtervalue;
				$values[] = & $value[2][$i];
				break;

			case "NOT_EQUAL":
				$condition = " <> ";
				$value[3][$i] = $filtervalue;
				$values[] = & $value[3][$i];
				break;

			case "GREATER_THAN":
				$condition = " > ";
				$value[4][$i] = $filtervalue;
				$values[] = & $value[4][$i];
				break;

			case "LESS_THAN":
				$condition = " < ";
				$value[5][$i] = $filtervalue;
				$values[] = & $value[5][$i];
				break;

			case "GREATER_THAN_OR_EQUAL":
				$condition = " >= ";
				$value[6][$i] = $filtervalue;
				$values[] = & $value[6][$i];
				break;

			case "LESS_THAN_OR_EQUAL":
				$condition = " <= ";
				$value[7][$i] = $filtervalue;
				$values[] = & $value[7][$i];
				break;

			case "STARTS_WITH":
				$condition = " LIKE ";
				$value[8][$i] = "{$filtervalue}%";
				$values[] = & $value[8][$i];
				break;

			case "ENDS_WITH":
				$condition = " LIKE ";
				$value[9][$i] = "%{$filtervalue}";
				$values[] = & $value[9][$i];
				break;

			case "NULL":
				$condition = " IS NULL ";
				$value[10][$i] = "%{$filtervalue}%";
				$values[] = & $value[10][$i];
				break;

			case "NOT_NULL":
				$condition = " IS NOT NULL ";
				$value[11][$i] = "%{$filtervalue}%";
				$values[] = & $value[11][$i];
				break;
			}
			
			$where.= " " . $filterdatafield . $condition . "? ";
			$valuesPrep = $valuesPrep . "s";
			if ($i == $filterscount - 1)
			{
				$where.= ")";
			}
			$tmpfilteroperator = $filteroperator;
			$tmpdatafield = $filterdatafield;
		}     //end for loop
		
		$filterquery.= $query . $where;

		$valuesPrep = $valuesPrep . "ii";
		$values[] = & $start;
		$values[] = & $pagesize;
		$query = $query . $where . " LIMIT ?, ?";
		$result = $mysqli->prepare($query);
		call_user_func_array(array($result,"bind_param") , array_merge(array($valuesPrep) , $values));
		}
	}  // end of filters
	
if (isset($_GET['sortdatafield']))
	{
	$sortfields = array(
		"Address",
		"ipNum",
		"specificNums",
		"Project",
		"Agent",
		"Area"
	);
	$sortfield = \htmlspecialchars($_GET['sortdatafield']);
	$sortorder = $_GET['sortorder'];
		
	if (($sortfield != NULL) && (in_array($sortfield, $sortfields)))
	{
	if ($sortorder != '')
		{
		if ($_GET['filterscount'] == 0)
			{
			 $query =  $query . " ORDER BY "  . $sortfield . " " . $sortorder . " LIMIT ?, ?";
			error_log("query is $query\n");
			 $result = $mysqli->prepare($query);
			$result->bind_param('ii', $start, $pagesize);
			}
		  else
			{
			$filterquery.= " ORDER BY " . $sortfield . " ". $sortorder . " LIMIT ?, ?";
			$query = $filterquery;
			$result = $mysqli->prepare($query);
			call_user_func_array(array($result,"bind_param") , array_merge(array($valuesPrep) , $values));
			}
		}
	}
	}  // end of sort

$result->execute();
$result->bind_result($id, $address, $ipnum, $specificNums, $project, $agent, $area, $rating );

$deviceData = null;

while ($result->fetch())
	{
	$deviceData[] = array(
		'customerId' => $id,
		'address' => $address,
		'ipNum' => $ipNum,
		'specificNums' => $specificNums,
		'project' => $project,
		'agent' => agent,
		'area' => $area,
		'rating' => $rating		
	);
	}
$result = $mysqli->prepare("SELECT FOUND_ROWS()");
$result->execute();
$result->bind_result($total_rows);
$result->fetch();
if ($new_total_rows > 0) $total_rows = $new_total_rows;

$data[] = array(
	'TotalRows' => $total_rows,
	'Rows' => $deviceData
);
echo json_encode($data);

$result->close();
$mysqli->close();
?>

[DimitarParticipant]

Hi kathyl,

Unfortunately, we are not sure what causes the issue you describe. It may originate on your client side and have to do something with incorrect data adapter settings. As a reference you can also take a look at the example included in the jQWidgets download package under phpdemos\server_side_grid__with_nested_grids.

Best Regards,
Dimitar

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

[Author]

Posts