Tags
Infos
Selon le PEAR ::Pager tutorials de Lorenzo Alberton, voici un Wrapper Pager/PDO, inspiré largement du Pager_Wrapper contenu dans la doc du package.
<?php
// SAMPLE USAGE
// ------------
//
// $query = 'SELECT this, that FROM mytable';
// require_once 'Pager_Wrapper.php'; //this file
// $pagerOptions = array(
// 'mode' => 'Sliding',
// 'delta' => 2,
// 'perPage' => 15,
// );
// $paged_data = Pager_Wrapper_MDB2($db, $query, $pagerOptions);
// //$paged_data['data']; //paged data
// //$paged_data['links']; //xhtml links for page navigation
// //$paged_data['page_numbers']; //array('current', 'total');
//
/**
* Helper method - Rewrite the query into a "SELECT COUNT(*)" query.
* @param string $sql query
* @return string rewritten query OR false if the query can't be rewritten
* @access private
*/
function rewriteCountQuery($sql)
{
if (preg_match('/^\s*SELECT\s+\bDISTINCT\b/is', $sql) ||
preg_match('/\s+GROUP\s+BY\s+/is', $sql) ||
preg_match('/\s+UNION\s+/is', $sql)) {
return false;
}
$open_parenthesis = '(?:\()';
$close_parenthesis = '(?:\))';
$subquery_in_select = $open_parenthesis.'.*\bFROM\b.*'.$close_parenthesis;
$pattern = '/(?:.*'.$subquery_in_select.'.*)\bFROM\b\s+/Uims';
if (preg_match($pattern, $sql)) {
return false;
}
$subquery_with_limit_order = $open_parenthesis.'.*\b(LIMIT|ORDER)\b.*'.$close_parenthesis;
$pattern = '/.*\bFROM\b.*(?:.*'.$subquery_with_limit_order.'.*).*/Uims';
if (preg_match($pattern, $sql)) {
return false;
}
$queryCount = preg_replace('/(?:.*)\bFROM\b\s+/Uims', 'SELECT COUNT(*) FROM ', $sql, 1);
list($queryCount, ) = preg_split('/\s+ORDER\s+BY\s+/is', $queryCount);
list($queryCount, ) = preg_split('/\bLIMIT\b/is', $queryCount);
return trim($queryCount);
}
/**
* PDO's version of Pager_Wrapper
* @param object PDO instance
* @param string db query
* @param array PEAR::Pager options
* @param boolean Disable pagination (get all results)
* @param integer fetch mode constant
* @return array with links and paged data
*/
function Pager_Wrapper_PDO($db, $query, $pager_options = array(), $disabled = false, $fetchMode = PDO::FETCH_ASSOC){
if (!array_key_exists('totalItems', $pager_options)) {
if ($countQuery = rewriteCountQuery($query)) {
$stmt = $db->prepare($countQuery);
$stmt->execute();
$totalItems = $stmt->fetchColumn();
}else{
$stmt = $db->prepare($query);
if (!$stmt) {
echo "\nPDO::errorCode(): ";
print $db->errorCode();
die();
}
$stmt->execute();
if ($stmt->execute() === FALSE) {
echo "\nPDO::errorCode()toto: ";
print $db->errorCode();
die();
}
$rows = $stmt->fetchAll(PDO::FETCH_COLUMN);
$totalItems = count($rows);
}
$pager_options['totalItems'] = $totalItems;
}
require_once 'Pager/Pager.php';
$pager = Pager::factory($pager_options);
$page = array();
$page['links'] = $pager->links;
$page['totalItems'] = $pager_options['totalItems'];
$page['page_numbers'] = array(
'current' => $pager->getCurrentPageID(),
'total' => $pager->numPages()
);
list($page['from'], $page['to']) = $pager->getOffsetByPageId();
$page['limit'] = $page['to'] - $page['from'] +1;
// fix offset
// limit 1,10 can not retrive the first record of result set.
$page['from'] = $page['from'] - 1;
if (!$disabled) {
$query .= ' LIMIT ' . $page['from'] . ', ' . $pager_options['perPage'];
}
// var_dump($query);
$stmt = $db->prepare($query);
if (!$stmt) {
echo "\nPDO::errorCode(): ";
print $db->errorCode();print_r($db->errorInfo());
die();
}
$stmt->execute();
$page['data'] = $stmt->fetchAll($fetchMode);
if ($disabled) {
$page['links'] = '';
$page['page_numbers'] = array(
'current' => 1,
'total' => 1
);
}
// var_dump($pager);
return $page;
}
?>Bon le code est pas fignolé… mais on peut déjà l’utiliser à des fins de tests :
<?php
require_once('DatabaseConnection.class.php');
//copy the Pager_Wrapper file where you can include it
require_once './Pager_Wrapper.php';
$db = DatabaseConnection::get()->handle();
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
//skipped the db connection code...
//let's just suppose we have a valid db connection in $db.
$pager_options = array(
'mode' => 'Sliding',
'perPage' => 2,
'delta' => 2,
);
$query = 'SELECT * FROM person';
$paged_data = Pager_Wrapper_PDO($db, $query, $pager_options);
//show the results
echo '<ul>';
foreach ($paged_data['data'] as $product) {
echo '<li>'.$product['id_person'].': '.$product['name'].'</li>';
}
echo '</ul>';
//show the links
echo $paged_data['links'];
?>
RSS 2.0