0

I have two tables in my SQLite database. cash and bank both the tables contain the following attributes (id, person_id, date, remarks, type, amount, balance) I want to fetch all the transactions stored in both the tables which has to be sorted by date, for that, I used UNION but I also want to get 2 separate columns for cash balance and bank balance if the record is coming from cash table then only the cash balance should change and bank balance should remain same. Here is my code

$sql = "SELECT *, 'cash' AS mop 
        FROM cash 
        UNION ALL SELECT *, 'bank' AS mop 
                  FROM bank 
                  ORDER BY date DESC";

        $result = $db->query($sql); ?>
        <tr class="tab-head">
            <td>Date & Time</td>
            <td>Narration</td>
            <td>Mode</td>
            <td>Debit</td>
            <td>Credit</td>
            <td>Cash Balance</td>
            <td>Bank Balance</td>
        </tr> <?php
        if(numr($result)>0){
            while ($row=$result->fetchArray(SQLITE3_ASSOC)) { 
                $date = date("d/m/Y h:i:s", strtotime($row['date']));
                $remarks = $row['remarks'];
                $type = $row['type'];
                $amount = $row['amount'];
                $balance = $row['balance'];
                $mop = $row['mop'];
                $type == "in" ? ($credit = $amount AND $debit = "-") : ($debit = $amount AND $credit = "-"); 
                $mop == "cash" ? ($cbal = $balance AND $bbal = "-") : ($bbal = $balance AND $cbal = "-"); ?>
                <tr>
                    <td><?php echo $date; ?></td>
                    <td><?php echo $remarks; ?></td>
                    <td><?php echo $mop; ?></td>
                    <td><?php echo $debit; ?></td>
                    <td><?php echo $credit; ?></td>
                    <td><?php echo $cbal; ?></td>
                    <td><?php echo $bbal; ?></td>
                </tr> <?php
            }
        }

This is what I'm Getting from the code above As You can see in the Output if the source table is cash it is showing the modified cash balnce and leaving the bank balance blank and vice versa. What I want is I want to display the unchanged balance in the place of - Can anyone please help me with any sql query or PHP code, both are accepted but I will prefer a proper sql query. Please Help

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Waeez
  • 289
  • 4
  • 12
  • 29
  • @RiggsFolly Have you even read the question before marking it duplicate. the link you provided have nothing to do with my question – Waeez Feb 25 '18 at 14:01
  • But it is relevant to the code that is shown to us, which of course would not run. We can only operate on what you show us. – RiggsFolly Feb 25 '18 at 14:03
  • What are you talking about? This is not an answer to my question. – Waeez Feb 25 '18 at 14:06
  • But that is the different thing. There no point in marking the question as duplicate. You should've edited my question or you should've suggest me to do so – Waeez Feb 25 '18 at 14:12
  • @RiggsFolly This query *deliberately* uses constant string to differentiate the two parts of the UNION (see the Mode column in the output), and in any case SQLite does not use exactly the same rules as MySQL. – CL. Feb 25 '18 at 14:12
  • Oh silly me, I missed the SQLite bit. I do apologise – RiggsFolly Feb 25 '18 at 14:13
  • will you please help me to reopen it – Waeez Feb 25 '18 at 14:15
  • Hay RiggsFolly. please help me to reopen this question as you marked it duplicate unnecessarily. – Waeez Feb 25 '18 at 14:29
  • One of us is! It has been reopened already!! Try refreshing the page !!! – RiggsFolly Feb 25 '18 at 14:43
  • Okkay! thanks a lot – Waeez Feb 25 '18 at 15:08

1 Answers1

1

Specify the columns that you want before doing the union all:

select id, person_id, date, remarks, type, amount,
       balance as cash_balance, 0 as bank_balance
from cash
union all
select id, person_id, date, remarks, type, amount,
       balance as cash_balance, balance as bank_balance
from cash
order by date desc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786