-1

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;
}
  • 3
    As has been mentioned to you on numerous occasions, questions of this sort of poor quality will be closed as soon as possible. This is not a "give me the code" website. Show your research effort, the steps you've taken to solve the problem yourself, and when you run into specific problems come to this site for help. I know you'll ignore it, as you have done many times already, but please read the FAQ in full before asking any more questions – Clive Feb 5 '13 at 9:41
  • 1
    Good on ya, question re-opened :) – Clive Feb 5 '13 at 10:33
  • the o/p of $rows shows that query only fetch first row and not all records – aashi Feb 5 '13 at 11:55
1

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

  • its drupal 7 and while gives error. that why i used foreach – aashi Feb 5 '13 at 10:45
  • So you can append your ->fetchAssoc() right after your ->execute() ($query = $query->execute()->fetchAssoc();) – Gregory Kapustin Feb 5 '13 at 10:53
  • it still does not show any output. also debugged array $rows And $build. It return all record but did not display record in a table format as i coded – aashi Feb 5 '13 at 11:30
  • And are they in an array format, not an iterable object or else ? – Gregory Kapustin Feb 5 '13 at 11:31
  • yes they are in array – aashi Feb 5 '13 at 11:46
0

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.

  • I used both db_query and db_select but still no output. Where iam doing wrong? – aashi Feb 5 '13 at 11:31
  • @aashi, print $query; just after execute(). Copy the output query into your MySQL GUI and them see the result. – Sithu Feb 5 '13 at 11:36
  • i had edited the question pls have a look – aashi Feb 6 '13 at 12:17
0

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();
0

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;

}
0

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.

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