In this tutorial, I'll show you how you can create a table with re-sortable column headers and pagination from custom SQL. I strongly recommend you attempt to implement this functionality using the Views module first :)
<?php
/**
* Implements hook_menu()
*/
function helper_menu() {
$items = array();
$items['pager-test'] = array(
'title' => t('Pager Test'),
'description' => t('Pager Test'),
'page callback' => 'helper_page_callback_pager_test',
'access arguments' => array('access content'),
'type' => MENU_CALLBACK,
);
return $items;
}
/**
* Implements page callback for pager-test url
*/
function helper_page_callback_pager_test() {
// define number of results per page
$number_results = 10;
// define a list of columns to select
$columns = array(
'nid' => t('ID'),
'type' => t('Type'),
'title' => t('Title'),
);
// define sql to fetch results
$sql = "select %s from {node} where status = 1";
// define sql to fetch number of results
$sql_count = "select count(*) from {node} where status = 1";
// create resort header from column list
$header = array();
foreach ($columns as $key => $value) {
$header[] = array(
'data' => $value,
'field' => $key,
);
}
// add tablesort to SQL
$sql .= tablesort_sql($header);
// execute query
$resource = pager_query($sql, $number_results, 0, $sql_count, implode(', ', array_keys($columns)));
// fetch results
$results = array();
while ($row = db_fetch_array($resource)) {
$results[] = $row;
}
// define variable to contain page callback output
$output = "";
// create table html
$output .= theme('table', $header, $results);
// create pager html
$output .= theme('pager', NULL, $number_results, 0);
return $output;
}
?>The above code results in the following table:
Same thing but with Oracle. How?
Hi,
I just dropped in your site and it's being very useful. In this case I want to make this kind of table but with two differences. The first is that I want to query a Oracle database and I don't know how to change the code. The second one is related with a problem that occurs when there's many columns on the table. Is it possible to use a horizontal scroll bar? How do you implement that?
Thanks!
Great tutorial! Im wondering
Great tutorial!
Im wondering if it's possible to add an custom column. Like 'Options' with an edit-link for each node...
Thanks
Thanks for this. Much more useful than the API documentation alone. This helped me implement paged node content for a custom node type.
Links
What would be the most efficient ways to turn these rows into links to the appropriate nodes?
This was my solution:
while ($row = db_fetch_array($resource)) {$row['Title']=l(t($row['node_title']),'node/'.$row['nid'] );
unset($row['nid']);
$results[] = $row;
What do you think about using...
Thanks for the code, I am using it and am grateful that you took time to put it up.
I was wondering though, I ended up replacing the $sql_count count db query with mysql_num_rows.
Thus becoming:
$sql_count= "SELECT ".mysql_num_rows($results);
On large queries I though this could save db time and as you already have the results do it programatically.
Thoughts?
pager with an another database than the drupal database
Hi,
I'm wondering if it's possible to create a pager by working with another database?
because the pager_query is a specified drupal function and I don't find an equivalent for another database registered in PostgreSQL.
Can anybody help me please?
Anyway, this tutorial is really great. Thank you very much.
That's very useful!
Thanks Eric,
Roger Padilla
thank you, very informative
thank you, very informative
Regarding retrieving from two tables
Hey guys i figured it out but i am not able to make that as a link to the actual content...Can anyone help me out with that.
Regarding retrieving from two tables
Hey can you please let me know how to do the same by retrieving from two different tables.
-----I created a new content type "x" and it has the default title and body from node table and the remaining 4 items in the form are sent to a table 'x' in the database.
----Now, i need to retrieve the title,teaser,and the 4 items in table 'x' as a table when clicked on the menu item 'x'.
Can you reply back soon...
Thanks for the time..
Got no problem, i try this
Got no problem, i try this with
// define a list of columns to select
$columns = array(
'nid' => t('ID'),
'type' => t('Type'),
'title' => t('Title'),
't.field1' => t('My Field one'),
't.field2' => t('My Field two'),
);
You can see i used alias t for your second table, so it must be the same alias than in your $sql query
// define sql to fetch results
$sql = "select n.*,t.* from {node} n INNER JOIN [yourtable} t ON n.nid=t.nid WHERE status = 1";
// define sql to fetch number of results
$sql_count = "select count(*) from {node} n INNER JOIN [yourtable} t ON n.nid=t.nid WHERE status = 1";
comment: alias yourtable table with t and the inner join is made with a relation between nid in the two tables
comment2: This method works with more than 2 tables
Thank you so much
This tutorial was so helpful..thank you very much
Column headers alignment
Great tutorial but... is it possible to align column headers?