0

This my SQL query to fetch the details.

$query = "SELECT DISTINCT TITLE, PID, TYPE, SUM(DAYCOUNT) AS tot, ROUND(SUM(DAYCOUNT)/(
        SELECT SUM(DAYCOUNT) FROM REPORT_LIST_VIEW), 4) AS per
        FROM REPORT_LIST_VIEW
        WHERE DAYCOUNT > '0'
        GROUP BY TITLE, PID, TYPE
        ORDER BY TITLE ASC";
        $res = db_query($query);

I am displaying out put like this

foreach ($res as $row) { 
$i++; ?>
<li>
<a href="/page-file-views-user?id=<?php echo $row->pid ;?>&type=<?php echo $row->type; ?>" >
<ul class="inner-ul">
<li class="name"><?php echo ucfirst($row->title); ?></li>
<li class="percentage">
<ul>
<li><?php echo $row->per * 100; ?>%</li>
<li><?php echo $row->tot; ?></li>
</ul>
</li>
</ul>
</a>
</li>
<?php $sum+= $row->tot; } ?>

How can I add a pagination (mini pager) at top & bottom of the result?

<?php function pager_array_splice($data, $limit = 9, $element = 0) {
global $pager_page_array, $pager_total, $pager_total_items; $page = isset($_GET['page']) ? $_GET['page'] : ''; // Convert comma-separated $page to an array, used by other functions. $pager_page_array = explode(',', $page); // outs puts empty array // We calculate the total of pages as ceil(items / limit). $pager_total_items[$element] = count($data); // output 1 $pager_total[$element] = ceil($pager_total_items[$element] / $limit); // output 1 $pager_page_array[$element] = max(0, min((int)$pager_page_array[$element], ((int)$pager_total[$element]) - 1)); // no output return array_slice($data, $pager_page_array[$element] * $limit, $limit, TRUE); } ?>

And in my pagination div

$output = ''; $customArr = pager_array_splice($data, 1); $output .= theme('views_mini_pager', array('quantity' => 1)); print $output;

But no result is printing. Any error in my logic.

1

You want to take advantage of the PagerDefault class.

Write your query as a db_select query which I'll leave as an exercise for the reader because, frankly, I'm not sure if it's possible to create a db_select query with a subquery as part of an expression like that :). Assuming you build up your select query in a variable called $query, you'd have code along these lines:

$query = db_select('REPORT_LIST_VIEW')->extend('PagerDefault');
...build up $query object here...
$res = $query->execute();

$pager = drupal_render(array('#theme' => 'pager'));

Then just output $pager wherever you want it.

Also, in general, I'd recommend placing this in a block rather than hard-coding it into your theme so that it's more reusable.

|improve this answer|||||
  • Thanks Aaron. Can i use the same query structure to build the pager. I know i need more code to execute it. – drup Aug 7 '15 at 4:27
  • Here's the SelectQuery documentation: api.drupal.org/api/drupal/includes%21database%21select.inc/… and here's some info on how to build dynamic queries (which is what you need to do): drupal.org/dynamic-queries – Aaron Aug 7 '15 at 12:03
  • I am getting all the results properly. I need a pagination to reduce the page load and length. Once the $res = db_query($query), i am using foreach ($res as $row) { ...} to display result. On the top and bottom of results, i need to add pager. If its difficult to done by drupal API, can be implemented by PHP SQL pagination. – drup Aug 7 '15 at 12:13
  • Well, my answer focuses on how to use the Drupal API to create a pager. If you're going to write a pager from scratch that's a much longer answer that I'll leave someone else to tackle. – Aaron Aug 7 '15 at 12:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.