jQuery UI Widgets Forums Grid server_side_grid_filtering_and_paging with PHP PDO

This topic contains 1 reply, has 2 voices, and was last updated by  Dimitar 8 years, 6 months ago.

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

  • mesken
    Participant

    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

    • This topic was modified 8 years, 6 months ago by  mesken.
    • This topic was modified 8 years, 6 months ago by  mesken.

    Dimitar
    Participant

    Hello mesken,

    Unfortunately, we do not have any examples with PDO.

    Best Regards,
    Dimitar

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

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

You must be logged in to reply to this topic.