My first module: db_select 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' &amp;&amp; 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');
 -&gt;fields('i', array('field_ref_nid'))
 -&gt;condition('entity_id', $nid)
 -&gt;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-&gt;join("field_data_field_issue", "i", "n.nid = i.entity_id");
$query-&gt;join("field_data_field_order", "o", "n.nid = o.entity_id");
-&gt;fields('n', array('nid', 'title'))
-&gt;fields('o', array('field_order_value'))
-&gt;fields('i', array('field_issue_value'))
-&gt;condition('field_issue_value', $resultsubquery) //I don't know how to get the result in here?
-&gt;orderBy('nid') //First order by nid
-&gt;orderBy('field_order_value') //Then order by order
-&gt;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!
$header = array(
        array('data' =&gt; 'title',        'field' =&gt; 'title', 'sort' =&gt; 'ASC'),
        array('data' =&gt; 'cal name 1',   'field' =&gt; 'field_order_value'),
        array('data' =&gt; 'Cal name 2',   'field' =&gt; 'field_issue_value'),
    );

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

$rpt_data = array();

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

    if (count($rpt_data) == 0) {
        $output = t('&lt;div class="no-data"&gt;Record not found.&lt;/div&gt;');
    } 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' =&gt; $header, 'rows' =&gt; $chunks[$current_page])); // Show the pager
        $output.= theme('pager', array('quantity',count($rpt_data)));
    }