title: How to create General Ledger/T-Account using PHP Mysql
body: <p>I am trying to create a double entry financial accounting system. I have completed designing the database for it, please check this link to see the diagram of my database . <a href="http://i39.tinypic.com/juhbv6.png" rel="nofollow noreferrer">http://i39.tinypic.com/juhbv6.png</a>   </p>
  
  <p>I have tried a lot to develop the mysql query to produce the general ledger but I got no where close. All I have managed to do is create ledger for only one account where I have to create for unlimited account.</p>
  
  <p>The query I have tried is following:</p>
  
  <pre><code> $this-&gt;db-&gt;select('*');
      $this-&gt;db-&gt;from('credit_side');
      $this-&gt;db-&gt;join('debit_side', ' debit_side.transaction_id_dr = credit_side.transaction_id_cr ');
      $this-&gt;db-&gt;join('transaction_info', 'transaction_info.transaction_id = credit_side.transaction_id_cr ');
      $this-&gt;db-&gt;join('accounts', 'accounts.code = credit_side.account_code ');
      $this-&gt;db-&gt;where('debit_side.account_code', '1001'); 
      $this-&gt;db-&gt;order_by('voucher_date','ASC');
  </code></pre>
  
  <p>After failing to write the mysql query which can produce the ledger for all accounts I have written down the logic to create General Ledger/T account .</p>
  
  <p>Now, would you please help me with the mysql query? </p>
  
  <p>Please check the database before going through the following.
  Thanks in Advance :)  </p>
  
  <ol>
  <li><p>Get the <code>accounts.name</code> AS AccountHead, <code>accounts.code</code> from accounts.  </p></li>
  <li><p>Go to the table <code>debit_side</code> and <code>get debit_side.account_code</code>, 
  if <code>debit_side.account_code</code>=<code>accounts.code</code> then get <code>credit_side.account_code</code> AS AccountName1 (but when I will echo in php, I want to get the name instead of the code itself)  and <code>credit_side.amount</code> AS Amount1, <code>SUM(credit_side.amount)</code> AS TotalAmount1  from <code>credit_side</code> where <code>debit_side.transaction_id_dr</code>=<code>credit_side.transaction_id_cr</code>
  and <code>transaction_info.voucher_date</code> is in between date1 and date2 
  WHERE <code>transaction_info.transaction_id</code>=<code>debit_side.transaction_id_dr</code></p></li>
  <li><p>After completing the second step go to the table <code>credit_side</code> and <code>get credit_side.account_code</code>,<br>
  if <code>credit_side.account_code</code>=<code>accounts.code</code> then get <code>debit_side.account_code</code> AS AccountName2 (but when I will echo in php, I want to get the name instead of the code itself)  and <code>debit_side.amount</code> AS Amount2, <code>SUM(debit_side.amount)</code> AS TotalAmount2  from <code>debit_side</code> where <code>credit_side.transaction_id_cr</code>=<code>debit_side.transaction_id_dr</code>
  and <code>transaction_info.voucher_date</code> is in between date1 and date2 
  WHERE <code>transaction_info.transaction_id</code>=<code>credit_side.transaction_id_cr</code></p></li>
  </ol>
  
  <p>Now in the view file I aim to have the following:</p>
  
  <pre><code>   &lt;table width="200" border="0"&gt;
       &lt;tr&gt;&lt;td colspan="5"&gt;Account Head &lt;?echo $AccountHead ; ?&gt; &lt;/td&gt; &lt;/tr&gt;
  
  
      &lt;tr&gt;
             &lt;td&gt;Dr.&lt;/td&gt;
             &lt;td&gt;amount.&lt;/td&gt;
              &lt;td&gt;&amp;nbsp;&lt;/td&gt;
             &lt;td&gt;Cr&lt;/td&gt;
            &lt;td&gt;Amount&lt;/td&gt;
  
      &lt;/tr&gt;
       &lt;tr&gt;
         &lt;td&gt;&lt;?echo $AccountName1 ; ?&gt;&lt;/td&gt;
         &lt;td&gt;&lt;?echo $Ammount1 ; ?&gt;&lt;/td&gt;
        &lt;td&gt;&lt;/td&gt;
   &lt;td&gt;&lt;?echo $AccountName2 ; ?&gt;&lt;/td&gt;
       &lt;td&gt;&lt;?echo $Ammount2 ; ?&gt;&lt;/td&gt;
  
       &lt;/tr&gt;
  
       &lt;tr&gt;
       &lt;td&gt;Total&lt;/td&gt;
       &lt;td&gt;&lt;?echo $TotalAmount1 ; ?&gt;&lt;/td&gt;
       &lt;td&gt;&amp;nbsp;&lt;/td&gt;
   &lt;td&gt;Total  &lt;/td&gt;
   &lt;td&gt;&lt;?echo $TotalAmount2 ; ?&gt;&lt;/td&gt;
  
       &lt;/tr&gt;
       &lt;/table&gt;
  </code></pre>
  
  <p><h1> General Ledger Sample</h1>
  <img src="https://i.stack.imgur.com/TooYb.jpg" alt="enter image description here"></p>