server_side_grid_filtering_and_paging with PHP PDO

[meskenParticipant]

Hello,

My application was buil with PHP-MYSQL using normal MYSQL functions like mysql_ but know we are moving all the application to PDO.
Please how can you convert datagrid server side code to PDO. This is what we have tried and it not working

$pagenum = $_GET['pagenum'];
$pagesize = $_GET['pagesize'];
$start = $pagenum * $pagesize;
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM groupe ORDER BY IDGroupe DESC LIMIT ?, ?";

// filter data.
if (isset($_GET['filterscount']))
{
	$filterscount = $_GET['filterscount'];
			
	if ($filterscount > 0)
	{
		$where = " WHERE (";
		$tmpdatafield = "";
		$tmpfilteroperator = "";
		for ($i=0; $i < $filterscount; $i++)
		{
			// get the filter's value.
			$filtervalue = $_GET["filtervalue" . $i];
			// get the filter's condition.
			$filtercondition = $_GET["filtercondition" . $i];
			// get the filter's column.
			$filterdatafield = $_GET["filterdatafield" . $i];
			// get the filter's operator.
			$filteroperator = $_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":
					$where .= " " . $filterdatafield . " LIKE '%" . $filtervalue ."%'";
					break;
				case "DOES_NOT_CONTAIN":
					$where .= " " . $filterdatafield . " NOT LIKE '%" . $filtervalue ."%'";
					break;
				case "EQUAL":
					$where .= " " . $filterdatafield . " = '" . $filtervalue ."'";
					break;
				case "NOT_EQUAL":
					$where .= " " . $filterdatafield . " <> '" . $filtervalue ."'";
					break;
				case "GREATER_THAN":
					$where .= " " . $filterdatafield . " > '" . $filtervalue ."'";
					break;
				case "LESS_THAN":
					$where .= " " . $filterdatafield . " < '" . $filtervalue ."'";
					break;
				case "GREATER_THAN_OR_EQUAL":
					$where .= " " . $filterdatafield . " >= '" . $filtervalue ."'";
					break;
				case "LESS_THAN_OR_EQUAL":
					$where .= " " . $filterdatafield . " <= '" . $filtervalue ."'";
					break;
				case "STARTS_WITH":
					$where .= " " . $filterdatafield . " LIKE '" . $filtervalue ."%'";
					break;
				case "ENDS_WITH":
					$where .= " " . $filterdatafield . " LIKE '%" . $filtervalue ."'";
					break;
			}
									
			if ($i == $filterscount - 1)
			{
				$where .= " )";
			}
					
			$tmpfilteroperator = $filteroperator;
			$tmpdatafield = $filterdatafield;			
		}
				

		$result = $connect->prepare($query); 
		$result->execute(array($start, $pagesize)); 
		$sql = "SELECT FOUND_ROWS() AS <code>found_rows</code>;";
		$resultat = $connect->prepare($sql);
		$resultat->execute();
		$rows=$resultat->fetch(PDO::FETCH_ASSOC); 
		$total_rows = $rows->found_rows;
			
		$query1 = "SELECT * FROM groupe ".$where." ORDER BY IDGroupe DESC LIMIT ?, ?";
		
	}
}

$resultat1 = $connect->prepare($query1); 
$resultat1->execute(array($start, $total_rows)); 
$sql1 = "SELECT FOUND_ROWS() AS <code>found_rows</code>;";
$result1 = $connect->prepare($sql1);
$result1->execute();
$rows1=$result1->fetch(PDO::FETCH_ASSOC); 
$total_rows1 = $rows1->found_rows;

// get data and store in a json array
$conteur=1;
while($ligne=$resultat1->fetch(PDO::FETCH_OBJ)) { 
				
	$Categoriesgroupe[] = array(
									   
		'IDGroupe' => stripslashes($ligne->IDGroupe),
		'libelleGroupe' => stripslashes($ligne->libelleGroupe),
		'descriptionGroupe' => stripslashes($ligne->descriptionGroupe),
		'droitsAccess' => stripslashes($ligne->droitsAccess),
		'Numero' => $conteur,
					
	);
			
	$conteur++;
			
}
$data[] = array(
	'TotalRows' => $total_rows1,
	'Rows' => $Categoriesgroupe
);

echo json_encode($data);

Thank you for your help

[DimitarParticipant]

Hello mesken,

Unfortunately, we do not have any examples with PDO.

Best Regards,
Dimitar

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

[Author]

Posts