how to display fetched record from database in a table [closed]

Question

I am fetching records from a custom table and want to display it in a table. Here is my custom table structure:

nid | sku    | orderIn | orderOut
1   | prod-1 | 200     | 20
2   | prod-1 | 230     | 10
3   | prod-1 | 250     | 20
4   | prod-1 | 200     | 20

Here is the function where I am using my query to fetch the records from the database. The problem is that drupal_set_message() doesn't print the array.

     function formModule_menu() {
      $items = array();

      $items['form/formModule/reviewOrder'] = array(
        'title' => 'Review Order',
        'page callback' => 'formModule_page',
        'access arguments' => array('access simple page'),
        'access callback' => TRUE,

        'type' => MENU_NORMAL_ITEM,
        'weight' => 3,
      );

      return $items;
    }

function formModule_page() {
  return array('#markup' => '<p>' . t('Review Order: The table below shows the TOTAL STOCK and TOTAL QUANTITY of product purchased.') . '</p>');
  // We are going to output the results in a table with a nice header.
  $header = array(
    // The header gives the table the information it needs in order to make
    // the query calls for ordering. TableSort uses the field information
    // to know what database column to sort by.
    array('data' => t('S.No.'), 'field' => 't.S.No.'),
    array('data' => t('Product Name'), 'field' => 't.Product Name'),
    array('data' => t('Total Stock'), 'field' => 't.Total Stock'),
    array('data' => t('Total Purchasing'), 'field' => 't.Total Purchasing'),

  );

  $query = db_select('formmodule_order', 't'); // Using the TableSort Extender is what tells the
                           // the query object that we are sorting.
  $query->fields('t');

  $result = $query
    ->orderByHeader($header) // Don't forget to tell the query object how to
                             // find the header information.
    ->execute();

  $rows = array();
  foreach ($result as $row) {
    // normally we would add some nice formatting to our rows
    // but for our purpose we are simply going to add our row
    // to the array.
    $rows[] = array('data' => (array) $row);
  }

  // build the table for the nice output.
  $build['formmodule_order'] = array(
    '#theme' => 'table',
    '#header' => $header,
    '#rows' => $rows,
  );

  return $build;
}

Answer 1

You miss a fetching function after your query is executed. You can use while($row = $result->fetchAssoc()) { instead of foreach ($result as $row) {

Answer 2

As Gregory mentioned, you would need to use fetch statement after calling execute().

Check all available fetch functions in the documentation.

Or use db_query() instead. The db_query is still valid in D7, but db_select() is recommended.

Answer 3

The main error in your code is the first line of formModule_page().

return array('#markup' => '<p>' . t('Review Order: The table below shows the TOTAL STOCK and TOTAL QUANTITY of product purchased.') . '</p>');

Since that line is a return, the rest of the code will never be executed.

The other error is in the following code.

$query = db_select('formmodule_order', 't'); // Using the TableSort Extender is what tells the
                       // the query object that we are sorting.
$query->fields('t');

$result = $query
  ->orderByHeader($header) // Don't forget to tell the query object how to
                         // find the header information.
  ->execute();

That code is not using the TableSort extender, since the $query object is not extended. As consequence of this, the method orderByHeader is not recognized: SelectQueryInterface doesn't define it.
The correct way is invoking SelectQuery::extend(), as in the following code.

$query = db_select('formmodule_order', 't')
  ->fields('t');

$query = $query->extend('TableSort');

$result = $query
  ->orderByHeader($header)
  ->execute();

Answer 4

hook_menu() implementation

function formModule_menu() {
  $items = array();

  $items['form/formModule/reviewOrder'] = array(
    'title' => 'View Order',
    'page callback' => 'drupal_get_form',
    'page arguments' => array('formModule_display'),
    'access callback' => TRUE,
    'file' => 'formModule_display.inc',
    'type' => MENU_NORMAL_ITEM,
    'weight' => 3,
  );
  return $items;
}

yourmodule.inc file

function formModule_display($form, &$form_state)
{

  // The form we are building
  $form = array();

    $query = db_select('formmodule_order', 'h')
   ->fields('h',array('nid','sku','orderIn','orderOut','created'));

    $result = $query->execute();
   //print_r($row->sku); 

   $form['requests'] = array('#prefix' => '<div id="interview-request">',
                            '#suffix' => '</div>',
                            '#tree' => TRUE,
                            '#theme' => 'table',
                            '#header' => array(t('Product ID'), t('Product Code'), t('Product Stock'), t('Product Purchased'), t('Created')),
                            '#rows' => array(),
                            );


 while($row = $result->fetchAssoc()) {

     //   print_r($row);
    //$debug .= '<br/>Header Record #'.' <pre>' . print_r($row, true) . '</pre>';
  $nid = array('#id' => 'id',
                 '#type' => 'markup',
                 '#markup' => '<b>' . $row['nid']. '</b>',
                 );
    //drupal_set_message('<pre>' . print_r($nid, true) . '</pre>');
    $sku = array('#id' => 'sku',
                     '#type' => 'markup',
                    '#markup' => '<b>' . $row['sku'] . '</b>',
                 );
    //drupal_set_message('<pre>' . print_r($sku, true) . '</pre>');
   $orderin = array('#id' => 'orderin',
                 '#type' => 'markup',
                 '#markup' => '<b>' . $row['orderin']. '</b>',
                 );
            //   print_r($orderin);
    //drupal_set_message('<pre>' . print_r($nid, true) . '</pre>');
    $orderout = array('#id' => 'orderout',
                     '#type' => 'markup',
                    '#markup' => '<b>' . $row['orderout'] . '</b>',
                 );
    //drupal_set_message('<pre>' . print_r($orderOut, true) . '</pre>');
    $created = array('#id' => 'created',
                     '#type' => 'markup',
                    '#markup' => '<b>' . $row['created'] . '</b>',
                 );
    $form['requests'][] = array('nid' => &$nid,
                                'sku' => &$sku,
                                'orderIn' => &$orderin,
                                'orderOut' => &$orderout,
                                'created' => &$created,
                                );
    $form['requests']['#rows'][] = array(array('data' => &$nid),
                                         array('data' => &$sku),
                                         array('data' => &$orderin),
                                        array('data' => &$orderout),
                                         array('data' => &$created),
                                         );
    unset($nid);
    unset($sku);
    unset($orderin);
    unset($orderout);
     unset($created);



  }

  return $form;

}

Answer 5

Maybe consider another approach, using the Forena module. It is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some reasons for considering Forena. Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links. Refer to its community documentation for a lot more details.

To answer this specific answer I'd use its typical report writing features to build the report (the content of the custom table to be displayed), and then use the most appropriate site building feature(s) to actually visualize these reports. As a variation, use the available hooks provided by Forena in your custom module. The forena documentation that gets installed with the module also includes a chapter about (a) these site building features and (b) the available hooks, as shown also in the demo site (via the various links near the bottom of that link).

Disclosure: I'm a co-maintainer of this module,
I hope this does not violate the site's policy on self-promotion.