CODEWITHSUNDEEP
mysqlsqlhtml-table
Sudip Dutta
Sudip Dutta

Reputation: 1

How to fetch data from two tables datewise?

I am trying to fetch data from two mysql tables and arrange them datewise. The two tables relates to income and expenditure. What I am trying to achieve is first display the data from the date say 15/04/2014 from the income table , then display the data dated 15/04/2014 from the expenditure table and then again loop with the next date and display the data related to 16/04/14 from the income table and then from expenditure table. The present code is looping for as many times the no. of entries in the income table. How to correct this? The code is as following:

<?php
  include "connection.php";
?>
<table width="100%" border="1" align="center" cellpadding="2" cellspacing="2">
                <tr>
                  <td>Date</td>
                  <td>Particulars</td>
                  <td>Transaction Type</td>
                  <td>Sub Expense</td>
                  <td>Expense</td>
                  <td>Subincome</td>
                  <td>Income</td>
                  <td>Cash in Hand</td>
                  <td>Remarks</td>
                </tr>
                <?php
                $datequery = mysql_query("select date from income order by date asc") or die (mysql_error());
                while ($row = mysql_fetch_array($datequery)) 
                {
                $currentdate = $row['date'] ;

                echo $currentdate."<br>";

                            $result = mysql_query("SELECT *  FROM income WHERE date = '$currentdate' ") or die (mysql_error());
                            while ($row = mysql_fetch_array($result)) 
                            {
                                ?>
                            <tr>
                              <td><?php echo $row['date']; ?></td>
                              <td><?php echo $row['particulars']; ?></td>
                              <td><?php echo $row['trntype']; ?></td>
                              <td>&nbsp;</td>
                              <td>&nbsp;</td>
                              <td><?php $subincome = $row['subincome']; echo $subincome ;?></td>
                              <td><?php $income =  $row['income']; echo $income ?></td>
                              <td><?php $balance = $balance + $row['subincome'] + $row['income'] - $row['subexpense'] - $row['expense']; ;  echo $balance; ?></td>
                              <td><?php echo $row['remarks']; ?></td>
                            </tr>
                                            <?php
                            }
                                        $result = mysql_query("SELECT *  FROM expenditure WHERE date = '$currentdate' ") or die (mysql_error());
                                        while ($row = mysql_fetch_array($result)) 
                                        {
                                            ?>
                                        <tr>
                                          <td><?php echo $row['date']; ?></td>
                                          <td><?php echo $row['particulars']; ?></td>
                                          <td><?php echo $row['trntype']; ?></td>
                                          <td><?php echo $row['subexpense']; ?></td>
                                          <td><?php echo $row['expense']; ?></td>
                                          <td>&nbsp;</td>
                                          <td>&nbsp;</td>
                                          <td><?php $balance = $balance + $row['subincome'] + $row['income'] - $row['subexpense'] - $row['expense'];  echo $balance; ?></td>
                                          <td><?php echo $row['remarks']; ?></td>
                                        </tr>
                                        <?php
                                        }
                $date = strtotime($currentdate);
                $date = strtotime("+1 day", $date);;
                $currentdate =  date('Y-m-d', $date);

                }
                ?>
              </table>

Upvotes: 0

Views: 1659

Answers (2)

Michael Green
Michael Green

Reputation: 1491

If you want to get old-school you can retrieve all your income records sorted ascending date in one query and all your expenditure records in another query, also sorted ascending date. Maintain indexes into each result set array. Step forward through each incrementing the array indexes as dates change. You've done a merge join which is what the union all .. order by achieves in @Mureinik's answer. Do it his way, though; it's better.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311808

You could have a single query, using the union all operator, and sort accordingly. In my query bellow, I've added a dummy column to sort by, so I can ensure that the row from income always comes before the row from expenditure:

SELECT  *
FROM    (SELECT *, 1 AS dummy FROM income
         UNION ALL 
         SELECT *, 2 AS dummy FROM expenditure) t
ORDER BY `date` ASC, dummy ASC

Upvotes: 1

Related Questions