title: Handling Bank Reconciliation with php and mysql
body: <p>I am handling bank reconciliation with mysql and php,
  I have  two banks from which interest and charges are recorded
  Below displays the tables</p>
  
  <pre><code>Banks table
  | BankID    |   BankName    |
  |  01       |       AB Bank |
  |  02       |       CD Bank |
  </code></pre>
  
  <p>They user is free to add more banks to the <code>Banks table</code></p>
  
  <p>Below is the reconciliation table, this table receives various values from the bank per a month or two</p>
  
  <pre><code>reconcilation table
  |   BankID  |   interest    |   Charges     |   Date        | Year  |
  |     01    |       20.00   |       10.00   |   2013-05-30  | 2013  |
  |     02    |       0.00    |       0.00    |   2013-05-31  | 2013  |
  |     01    |       0.20    |       0.00    |   2013-08-27  | 2013  |
  |     02    |       50.00   |       0.00    |   2013-08-30  | 2013  |
  </code></pre>
  
  <p>I want to be able to show  the first reconciliation ( that is reconciliation with <code>2013-05-30</code> and <code>2013-05-31</code> as their date) for the two banks on one table and also show the second reconciliation ( that is reconciliation with <code>2013-08-30</code> and <code>2013-08-27</code> as their date) for the two banks on different table using one query on the same page. The <code>reconcilation table</code> is most often updated every two or three months within a year.</p>
  
  <p>This is tabulated below. For example on one php page we show </p>
  
  <pre><code>  ANALYSIS OF BANK ACCOUNTS (2013/05/01 to 2013/05/31)
  
  | Date      |   Details     |   AB Bank     |   CD Bank |
  |2013/05/01 |   Interest    |   20.00       |   0.00    |
  |           |   Charges     |   10.00       |   0.00    |
  
  
  
  
   ANALYSIS OF BANK ACCOUNTS (2013/06/01 to 2013/09/31)
  
  | Date      |   Details     |   AB Bank     |   CD Bank |
  |2013-06-01 |   Interest    |   0.20        |   50.00   |
  |           |   Charges     |   0.00        |   0.00    |
  </code></pre>
  
  <p>NB: The user can add more banks into the <code>Banks table</code> which will affect the <code>reconciliation table</code> values .</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>This is what I have tried so far</p>
  
  <pre><code>/**
   * This queries the various components for the various
   * banks within a particular period
   */ 
  $bankAccount1= "SELECT
                      a.bankID,
                      a.interest,
                      a.charges,
                      a.date
                  FROM 
                      `Bank Reconciliation` a 
                      group by bankID";
  
  $bankResult1=$db-&gt;query($bankAccount1);
  
  while($bankRow1=mysqli_fetch_assoc($bankResult1)){
      $bankID=$bankRow1['bankID'];
      $interest=$bankRow1['interest'];
      $tableHead .='&lt;th&gt;'.$bankID.'&lt;/th&gt;';
      $bankInterest .='&lt;td&gt;'.$interest.'&lt;/td&gt;';
      $bankCharges .='&lt;td&gt;'.$charges.'&lt;/td&gt;';
  }
  
  
  
  
  /**
   * This query is suppose to list the number
   * of reconciliation that has been done within one 
   * period.
   */
  $bankAccount2= "SELECT
                      a.bankID,
                      a.date
                  FROM 
                      `Bank Reconciliation` a 
                  group by date";
  
  while($bankRow2=mysqli_fetch_assoc($bankResult2)){
          $bankName=$bankRow2['bankID'];
          $date=$bankRow2['date'];
  
          echo'
              &lt;table&gt;
                  &lt;thead&gt;
                      &lt;tr&gt;
                          &lt;th&gt;Date &lt;/th&gt;
                          &lt;th&gt;Details&lt;/th&gt;
                          '.$tableHead.'
                      &lt;/tr&gt;
                  &lt;/thead&gt;
                  &lt;tbody&gt;
                      &lt;tr&gt;
                          &lt;td&gt;'.$date.'&lt;/td&gt;
                          &lt;td&gt;Interest&lt;/td&gt;
                          '.$bankInterest.'
                      &lt;/tr&gt;
                      &lt;tr&gt;
                          &lt;td&gt;'.$date.'&lt;/td&gt;
                          &lt;td&gt;Charge&lt;/td&gt;
                          '.$bankCharges.'
                      &lt;/tr&gt;
                  &lt;/tbody&gt;
              &lt;/table&gt;';
  }
  </code></pre>
  
  <p>This still does not work as expected, gives same values for the different reconciliation periods. I guess i am doing something wrong, I would be grateful if someone could help fix it. Thanks</p>