Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I just wanted to practice doing the table "header sorting" and pagination to display a Drupal 6 table using a simple MSSQL query. At the moment, the table headers are not showing and there is no pagination, but the data in the query is being displayed. Can anyone see anything wrong with the code?

/* open database connection */
include "mssqlconnection.inc.php";

/* set up the table headers */
$headers = array(
  '',
  array('image_id' => t('image_id'), 'field' => 'image_id', 'sort'=>'asc'),
  array('geo_post' => t('geo_post'), 'field' => 'geo_post'),
  array('geo_sample' => t('geo_sample'), 'field' => 'geo_sample'),
  array('gps_lat' => t('geo_lat'), 'field' => 'geo_lat'),
  array('gps_long' => t('gps_long'), 'field' => 'gps_long'),
  ''
);


/* Set up and execute the query. */
$sql = sprintf ("SELECT TOP 100 image_id,
  geo_post,
  geo_sample,
  gps_lat,
  gps_long,
  FROM images"
);

/* add the order by clause */
$stmt .= tablesort_sql($headers);

$stmt = sqlsrv_query($conn, $sql);
if ( $stmt === false) {
  echo "Error in query preparation/execution.\n";
  die( print_r( sqlsrv_errors(), true));
}

/* collecting results */

while ($doc = sqlsrv_fetch_object($stmt)) {
  $rows[] = array(
    sprintf($imgPublish . '   ' . $imgDelete, $doc->nid),
    $doc->image_id,
    $doc->geo_post,
    $doc->geo_sample,
    doc->gps_lat,
    $doc->gps_long
  );
}

$table = theme('table', $headers, $rows);
$table .= theme('pager', array(), 10, 0);

return $table;    

/* Close the connection. */
include "closemssqlconnection.inc.php";
share|improve this question
add comment

1 Answer

theme_pager() only works in combination with pager_query() (Or the PagerDefault extender in Drupal 7). And that function is obviously not going to work with sqlsrv().

For Drupal 7, there is the http://drupal.org/project/sqlsrv project, which is properly integrated into the new database abstraction layer. I suggest you have a look at the following answer to see how you might be able to use that in Drupal 6: MSSql or SQL Server Wrapper for custom module? See also How do you make sortable tables with a pager with data from a custom table? for how to create paginated queries with Drupal 7/the DBTNG project.

share|improve this answer
    
Thank you for your quick response Berdir! I thought that might be the case... I shall have to look doing it manually. Cheers Neil –  happyneil Apr 20 '11 at 20:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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