CODEWITHSUNDEEP
phpmysql
Azeal Webs
Azeal Webs

Reputation: 11

How to group two mysql tables by date

How can I fetch date-wise reports from two tables ?

Credit table :

------------------------
Date        |   Amount
------------------------
20/10/2014  |    1200
------------------------
10/10/2014  |    3200
------------------------
05/10/2014  |    1300
------------------------

Debit table:

------------------------
Date        |   Amount
------------------------
20/10/2014  |    2200
------------------------
15/10/2014  |    5200
------------------------
02/10/2014  |    1800
------------------------

Output should be :

Date          Cr           Dr
------------------------------
20/10/2014   1200        2200
15/10/2014      0        5200
10/10/2014   3200           0
05/10/2014   1300           0
02/10/2014      0        1800

I tried the following method :

$date[] = { '20/10/2014','19/10/2014',........Last date };
$i=0;
while ($<sizeof($date)) 
  {
    $credit  = mysql_fetch_assoc(mysql_query("select * from credit where date='".$date[$i]."'"));
    $debit  = mysql_fetch_assoc(mysql_query("select * from debit where date='".$date[$i]."'"));

    echo $credit["amount"]. $debit["amount"];
   }

I collected in date all the dates between the from and to dates.

I know this a bad method and that it slowers down the execution of the pages, so how could I solve this problem in a smarter way ?

Thank you in advance.

Upvotes: 1

Views: 174

Answers (3)

Logan
Logan

Reputation: 1371

I would go with the following query with a left outer join between two tables union right outer join excluding the inner join matching records and defaulting the left table value to 0. If there ain't no repeating dates in the credit & debit tables you can remove the sum method from the sql statement. 

select c.date_, ifnull(sum(c.amount),0)  cr, ifnull(sum(d.amount),0) db from credit c left outer join debit d on c.date_ = d.date_
group by c.date_
union all
select d.date_, 0 cr, ifnull(sum(d.amount),0) db from credit c right outer join debit d on c.date_ = d.date_ where c.date_ is null
group by d.date_
order by date_ desc

Here you go with a sql fiddle link for your reference

Upvotes: 0

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

Use below query as there is no outer join in mysql use left and right join

SELECT date, IFNULL(c.amount, 0) AS credit, IFNULL(d.amount, 0) AS debit
FROM credit c LEFT JOIN debit d
ON c.date = d.date
ORDER BY date DESC
UNION
SELECT date, IFNULL(c.amount, 0) AS credit, IFNULL(d.amount, 0) AS debit
FROM credit c RIGHT JOIN debit d
ON c.date = d.date
ORDER BY date DESC

And the PHP code would be like this

$query =  'SELECT date, IFNULL(c.amount, 0) AS credit, IFNULL(d.amount, 0) AS credit
           FROM credit c LEFT JOIN debit d
           ON c.date = d.date
           ORDER BY date DESC
           UNION
           SELECT date, IFNULL(c.amount, 0) AS credit, IFNULL(d.amount, 0) AS credit
           FROM credit c RIGHT JOIN debit d
           ON c.date = d.date
           ORDER BY date DESC';

$con = mysqli_connect( ) //Connection string;   

$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result))  
    {echo '<tr>
    <td>'.$row["date"].'</td>
    <td>'.$row["credit"].'</td>
    <td>'.$row["debit"].'</td>
    </tr>';

    }

Upvotes: 0

Ullas
Ullas

Reputation: 11556

Query

SELECT * FROM (SELECT a.`Date`,
IFNULL(a.Amount, 0) AS Cr,
IFNULL(b.Amount, 0) AS Dr
FROM Credit a 
LEFT JOIN Debit b
ON a.`Date` = b.`Date`
UNION
SELECT b.`date`, 
IFNULL(a.Amount, 0) AS Cr, 
IFNULL(b.Amount, 0) AS Dr
FROM Credit a
RIGHT JOIN Debit b
ON a.`date` = b.`date`)t
ORDER BY t.`Date` DESC;

Demo

Upvotes: 2

Related Questions