By gordonbooker on

I managed to get theme('table'....) and theme('pager'....) working to display sortable headings with pagination with a simple mysql query:
(by copying some code found on this site)

$sql = 'SELECT * FROM {z_slist_title}';
$header = array(
  array('data' => 'SID', 'field' => 'SID'),
  array('data' => 'SongTitle', 'field' => 'SongTitle'),
);

$sql .= tablesort_sql($header);
$result = pager_query($sql, 5);

while ($data = db_fetch_object($result)) {
    $rows[] = array($data->SID, $data->SongTitle);
  }

  if (!$rows) {
    $rows[] = array(array('data' => t('Empty at the moment..'), 'colspan' => '6'));
  }

echo theme('table', $header, $rows);
echo theme('pager', NULL, 50, 0);

However, when I change the mysql query to :

$sql = '
SELECT SongTitle,Performer,Year
FROM {z_slist_title}, {z_slist_links}, {z_slist_perf}
WHERE z_slist_title.sid = z_slist_links.sid
AND z_slist_perf.pid = z_slist_links.pid
';

the sortable headings etc work, but the page links at the bottom don't appear.
(I did of course change other bits of code such as the header and rows array data)

Can anyone help with what I need to do ?

⋅ Categories: Drupal 5.x

Comments

gordonbooker’s picture

gordonbooker commented

To Clarify, the full code that works in every respect apart from showing the paging links is :

$sql = '
SELECT SongTitle,Performer,Year
FROM {z_slist_title}, {z_slist_links}, {z_slist_perf}
WHERE z_slist_title.sid = z_slist_links.sid
AND z_slist_perf.pid = z_slist_links.pid
';

$header = array(
  array('data' => 'SongTitle', 'field' => 'SongTitle'),
  array('data' => 'Performer', 'field' => 'Performer'),
  array('data' => 'Year', 'field' => 'Year'),
);

$sql .= tablesort_sql($header);
$result = pager_query($sql, 3);

while ($data = db_fetch_object($result)) {
    $rows[] = array($data->SongTitle, $data->Performer, $data->Year);
  }

  if (!$rows) {
    $rows[] = array(array('data' => t('Empty at the moment..'), 'colspan' => '6'));
  }

echo theme('table', $header, $rows);
echo theme('pager', NULL, 50, 0);
gordonbooker’s picture

gordonbooker commented

I think the problem may lie in the count(*) that Drupal's pager_query function adds to the $sql string.

I've been trying to work out the sql syntax to send the function my own count query - but have been failing so far.

Any ideas ?

gordonbooker’s picture

gordonbooker commented

I have made a count query:

$countsql = '
SELECT COUNT(SongTitle) 
FROM z_slist_title, z_slist_links, z_slist_perf
WHERE z_slist_title.sid = z_slist_links.sid
AND z_slist_perf.pid = z_slist_links.pid
';

that returns the correct amount of rows.

I then use this in the pager function:

echo theme('pager', NULL, 50, $countsql);

but alas it still doesn't work - the queries work , but I get no page links at the bottom.

gordonbooker’s picture

gordonbooker commented

Haha - Finally did it - just posting here in case any other noob like me gets stuck with this:

You put the $count_query in the $result string, not in the theme('pager'......) bit.

So that final code that works is:


<?php

$sql = '
SELECT SongTitle,Performer,Year
FROM z_slist_title, z_slist_links, z_slist_perf
WHERE z_slist_title.sid = z_slist_links.sid
AND z_slist_perf.pid = z_slist_links.pid
';

$countsql = '
SELECT COUNT(SongTitle) 
FROM z_slist_title, z_slist_links, z_slist_perf
WHERE z_slist_title.sid = z_slist_links.sid
AND z_slist_perf.pid = z_slist_links.pid
';

$header = array(
  array('data' => 'SongTitle', 'field' => 'SongTitle'),
  array('data' => 'Performer', 'field' => 'Performer'),
  array('data' => 'Year', 'field' => 'Year'),
);

$sql .= tablesort_sql($header);

$result = pager_query($sql, 5,0,$countsql);

while ($data = db_fetch_object($result)) {
    $rows[] = array($data->SongTitle, $data->Performer, $data->Year);
  }

  if (!$rows) {
    $rows[] = array(array('data' => t('Empty at the moment..'), 'colspan' => '6'));
  }

echo theme('table', $header, $rows);

echo theme('pager', NULL, 10, 0);

?>