How to display fetched data in the form of html table?

Question

I have created a custom module & used below code to display data from Database. But I want to show that data in the form of html table. Please help.

<?php
function custom_table_menu(){
 $items = array();
 $items['custom_table'] = array(
  'title' => 'Custom Table',
  'page callback' => 'custom_table_page_display',
  'access callback' => TRUE,
);
 return $items;
}


function custom_table_page_display() {
$result = db_query('SELECT * FROM {users}');
  $records = $result->fetchAll();

  $content = '<pre>' . print_r($records, TRUE) . '</pre>';

  return $content;
}
?>

Answer 1

Try this example to output your data in table format with Pager (ie. table with page. 1,2,3.. )

function custom_table_page_display() {

  $header = array(
    array('data' => t('Name'), 'field' => 'id', 'sort' => 'desc'),
    array('data' => t('Address'), 'colspan' => 3),
  );

  $query = db_select('users', 'u');

  $query->fields('u', array('name','address'));  // select your required fields here

  $table_sort = $query->extend('TableSort') // Add table sort extender.

                      ->orderByHeader($header); // Add order by headers.

  $pager = $table_sort->extend('PagerDefault')

                      ->limit(20);  // here you can limit the number of data per page.

  $result = $pager->execute();

  $rows = array();
  foreach($result as $res) {
    $rows[] = array($res->id, 
                    $res-> title,);
  }
  // If rows are not empty theme and display the rows.

  $output = '';

  if (!empty($rows)) {

    $output = theme('table', array('header' => $header, 'rows' => $rows, 'attributes' => array('id' => 'sort-table')));

    $output .= theme('pager');

  }

  else {

    $output .= t("No results found.");

  }

  return $output;

}

Answer 2

Below is the Code, you can create the listing page with sortable header and pagination. This code using the 'TableSort' theme.

function custom_table_menu(){
 $items = array();
 $items['custom_table'] = array(
  'title' => 'Custom Table',
  'page callback' => 'order_listing',
  'access callback' => TRUE,
);
 return $items;
}

function order_listing() {
    global $base_url;

        $header = array(
            array(
                'data' => t('OrderID'),
                'field' => 't.OrderID',
            ),
            array(
                'data' => t('OrderDate'),
                'field' => 't.OrderDate',
            ),
            array(
                'data' => t('PaymentAmount'),
                'field' => 't.PaymentAmount',
            ),
            array(
                'data' => t('BillingFirstName'),
                'field' => 't.BillingFirstName',
            ),
            array(
                'data' => t('BillingLastName'),
                'field' => 't.BillingLastName',
            ),
            array(
                'data' => t('OrderStatus'),
                'field' => 't.OrderStatus',
            ),
            array(
                'data' => t('ShipDate'),
                'field' => 't.ShipDate',
            ),
        );
        $cust_id = arg(2);

                        $query = db_select('Orders', 't')->extend('TableSort');
        $query->fields('t', array('OrderID', 'OrderDate', 'PaymentAmount', 'BillingFirstName', 'BillingLastName', 'OrderStatus', 'ShipDate'));


        $table_sort = $query->extend('TableSort')->orderByHeader($header);
        $pager = $table_sort->extend('PagerDefault')->limit(50); // 5 rows per page.

        $result = $pager->execute();


        $rows = array();
        foreach ($result as $row) {
            $rows[] = array('data' => array( $row->OrderID, $row->OrderDate, $row->PaymentAmount, $row->BillingFirstName, $row->BillingLastName, $row->OrderStatus, $row->ShipDate));


        }
        $output .= theme('pager');
        $output .= theme('table', array('header' => $header, 'rows' => $rows, "empty" => t("No Records Found"), 'attributes' => array('id' => 'sort-table', 'class' => array('tbl-data'))));
        $output .= theme('pager');

    return $output;
}