My first module: db_select question

Question

I'm working on my first module, in this module I want to show a pager to two similar nodes. (Yes I know there are modules for this but it's more complex then the above but I kept it simple to make my question more clear). I'm completely new to making modules and the way de db_select works so I need some help with keeping my code clean and simple.

I have three tables I need information from.

• Table 1: node
• Table 2: field_data_field_issue (referenced node)
• Table 3: field_data_field_order (order of nodes field)

First I need to get the current node id. For this I use the following code:

if (arg(0) == 'node' && is_numeric(arg(1))) {
  // Get the nid
  $nid = arg(1);
}

Now I need to find the nid of the node the current node refers to. I have to do this in Table 2. So I use this query:

$subquery = db_select('field_data_field_issue', 'i');
 ->fields('i', array('field_ref_nid'))
 ->condition('entity_id', $nid)
 ->execute(); 
 return $subquery;

Third I need to find all nodes that have the same referenced node and order them by nid and order. So I'm going for a query with join:

$query = db_select('node', 'n');
$query->join("field_data_field_issue", "i", "n.nid = i.entity_id");
$query->join("field_data_field_order", "o", "n.nid = o.entity_id");
->fields('n', array('nid', 'title'))
->fields('o', array('field_order_value'))
->fields('i', array('field_issue_value'))
->condition('field_issue_value', $resultsubquery) //I don't know how to get the result in here?
->orderBy('nid') //First order by nid
->orderBy('field_order_value') //Then order by order
->execute(); 
return $query;

This is what I have till now. Things I didn't figure out yet and where I could use some help with are.

• How to get the result of $subquery in $query.
• Is this the way to do it? Is this clean enough and am I missing out on something?
• I need the two nodes next to the current node. So if the ordering is (nid): 3,2,4,5,1,6,8,9 and I'm currently at node 1 I want to output node 5 and 6.

Thanks in advance!

Answer 1

$header = array(
        array('data' => 'title',        'field' => 'title', 'sort' => 'ASC'),
        array('data' => 'cal name 1',   'field' => 'field_order_value'),
        array('data' => 'Cal name 2',   'field' => 'field_issue_value'),
    );

// $result = db_query("SQL here");

$rpt_data = array();

    foreach ($results as $key => $rpt_data1){
        $rpt_data[$key][] = 'Result item';
        $rpt_data[$key][] = $rpt_data1->product_title;
        $rpt_data[$key][] = $rpt_data1->stock_value;
    }

    if (count($rpt_data) == 0) {
        $output = t('<div class="no-data">Record not found.</div>');
    } else {
        $per_page = 20; // Initialize the pager
        $current_page = pager_default_initialize(count($rpt_data), $per_page);  // Split your list into page sized chunks
        $chunks = array_chunk($rpt_data, $per_page, TRUE);  // Show the appropriate items from the list
        $output = theme('table', array('header' => $header, 'rows' => $chunks[$current_page])); // Show the pager
        $output.= theme('pager', array('quantity',count($rpt_data)));
    }