How do I print a list of transaction with some of them in sub categories

[MockY]

PURPOSE

 

Building a budgeting software for personal use where I enter all transactions throughout the month in various accounts. I have used a version I built many years ago, but am looking to enhance it and am now rebuilding it from scratch. I'm almost done with the whole thing but there is one remaining feature that is escaping me.

 

If I go and fill up my car with gas, that transaction is pretty straight forward. On purchase in one category. Gas - $40.

But if I go to the grocery store and purchase some beer, a magazine, some tomatoes, and then a soap, then I end up having 4 different categories for the same receipt.

 

Currently, I manually enter 4 different transaction from the same store and the same date. But it is hell to reconsile when the credit card statement lists it as one transaction.

 

I would therefore like to be able to enter multiple categories for one receipt.

 

CURRENT STRUCTURE

 

Take these faux transactions as an example

As you see, Nugget is listed twice even though they are from the same receipt, just because there are two categories involved.

 

What I'd like to accomplish is only one line of Nugget with the combined amount listed. I'd like to expand another row underneath to display what categories that results in the total, along with the amounts.

 

WHAT I COOKED UP SO FAR

 

This may totally be off target and not the proper way to go at it, but I'd like to share where my mind took me.

I just added a new column for the table (CombinedId) with a default value of NULL, and for the transactions that are "bundled", I add the same number.

 

But when it comes to display these guys, I either come up with something so bloated and to random that it can't be implemented. But this is my thought process is in english:

 

1.  if combined number is found, store it in an array and go to the next transaction.

2.  If the combined number changes (either a new transaction with multiple categories or a transaction with only one category), print the contents of the array, empty the array, and print the transaction that triggered the "dump" of the array.

 

 

This is probably not the way to go, so I need help.

 

 

[sKunKbad]

Barand turned me on to database normalization through the video on this link:

 

http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

 

I think if you watch the series of videos, you'll find the real answer to your problem. What this means for you is a better database design, and getting familiar with SQL joins.

[Psycho]

Yep, you will need two tables for your transactions (well, four if you count the categories and the vendors). Here's an example of how the structure would work

Table: transactions
trans_id | trans_date | vendor_id
----------------------------------
 1         2013-11-18    11
 2         2013-11-18    12
 3         2013-11-17    13

Table: vendors
vendor_id | vendor_name
-------------------------
 11          Chipotle
 12          Nugget
 13          Amazon

Table: items
item_id | trans_id | item_amount | category_id
-----------------------------------------------------------------
 1         1          17.60         16
 2         2          22.12         17
 3         2           4.54         18
 4         3          81.07         19

Table: categories
cat_id | cat_name
-------------------------
 16       Meals Out
 17       Groceries
 18       Misc. Food/Drink
 19       Gifts

You can see that there is one transaction for "nugget" on the 18th. But, the transaction doesn't have any other details. Those are stored in the 'items' table. That transaction for 'nugget' has two separate 'items'. One in the amount of $22.12 with the category groceries and one for $4.54 for Misc. Food/Drink. Now, I made the assumption that you would group like categories into one itemization. But, if you wish, you could list every single item on your receipt in the items table.

[MockY]

I know my database design leaves plenty to desire, but I do have some normalization going on. But the way I did it a few years back was probably not the greatest of design. But before I go over your suggestions Psycho, I'd like to share the current database design, seeing as I'd much rather leave it somewhat intact to avoid to also completely rebuild the database, as I've done the GUI for the software. But ultimately, I want it to be better so I'm down with a total rebuild as well, but this may shed some light to my thought process (for readability, some items in the tables are not important and therefore omitted).

Table: accounts
-----------------------------
Id
Institution
Type               
AccountDescription
CardNumber  

------------------------------
Table: main_categories
------------------------------
Id
Name

-----------------------------
Table: sub_categories
------------------------------
Id
Name
MainCategoryId

------------------------------
Table: transactions
------------------------------
Id
Name
MainCategoryId
SubCategoryId
Date
Amount
AccountId

[Psycho]

Right - and the core problem is that the transaction table is not sufficient to track itemized details. A transaction records should store a single transaction. But, you are wanting to store different details about parts of the transaction. One possible solution without modifying that table would be to create a new table to ONLY records itemized data when needed. This will probably be the same amount of work as restructuring in the proper way and modifying your existing logic. Plus, doing it this way would result in more complicated logic - creating maintainability problems.

 

But, since you asked:

 

Create a new table similar to before for itemized data. But, only use it when the entire transaction does not apply to a single category. This will require you to ensure the 'total' in the transaction table equals the sum of parts in the items table - something you should never have to do. This tale will need an ID column, a foreign key to the transactions table, as well as Category, SubCategory and amount fields. Once you do this, you will need to develop the ability to itemize a transaction (i.e. add/edit/delete). Then almost any functionality that relates with transactions would need to be updated as well. Anything that displays transactions, individually or a list, will likely need to display information about the itemization. Plus, you would have to update any delete functions to prevent orphaned records. The more I think about it, I feel even stronger that the work involved to do it without modifying the transaction table will be the same as not modifying that table. But, it is your project to do what you would like.

[MockY]

I tried both methods and came to the same conclusion: Rebuilding the database from scratch is the only answer. So thank you very much for steering me in the right direction.

Using your solution, this is what I came up with. It does what I want but I seriously doubt this is the proper way to go about this. So if you feel like you'd like to share more of your wealth in knowledge, I am all ears.

In either case, I'll mark the thread as answered and provide the code I cooked up with based on the database structure you provided. Keep in mind that I did not consider any UI design what so ever in the example. This is just to prove that it worked as intended, and I'll move the adapt the code accordingly when moved to the project.

<?php

$query = "SELECT t.trans_id, t.trans_date, v.vendor_name 
                 FROM transactions t, vendors v 
                 WHERE t.vendor_id=v.vendor_id";
$result = mysqli_query($link, $query);

if (mysqli_num_rows($result) > 0) { ?>
<table>
    <tr>
        <th>Date</th>
        <th>Vendor</th>
        <th>Category</th>
        <th>Amount</th>
    </tr>
<?php
    while($r = mysqli_fetch_assoc($result)) {

        $trans_id = $r['trans_id'];
        $trans_date = strtoupper(date('M d', strtotime($r['trans_date'])));
        $vendor = $r['vendor_name'];

        $iquery = "SELECT i.item_amount, c.cat_name 
                    FROM items i, categories c 
                    WHERE c.cat_id=i.category_id 
                        AND i.trans_id='$trans_id'";        
        $iresult = mysqli_query($link, $iquery);

        if (mysqli_num_rows($iresult) == 1) { 
            // Only one category per transaction
            $ir = mysqli_fetch_assoc($iresult); ?>
        <tr>
            <td><?php echo $trans_date; ?></td>
            <td><?php echo $vendor; ?></td>
            <td><?php echo $ir['cat_name']; ?></td>
            <td><?php echo $ir['item_amount']; ?></td>
        </tr>

  <?php } else {

            // Multiple categories per receipt
            $total_amount = 0;
            $transaction_items = array();
            while ($ir = mysqli_fetch_array($iresult)) { 
                $total_amount += $ir['item_amount'];
                $transaction_items[] = array("Category" => $ir['cat_name'], "Amount" => $ir['item_amount']);
            } ?>
            
        <tr>
            <td><?php echo $trans_date; ?></td>
            <td><?php echo $vendor; ?></td>
            <td onclick="toggle_visibility(<?php echo $trans_id; ?>)">Show Details</td>
            <td><?php echo $total_amount; ?></td>
        </tr>
        <tr style="display: none;" class="transaction_details" id="<?php echo $trans_id; ?>">
            <td colspan="4">
                <div>
                    <?php foreach ($transaction_items as $item) {
                    echo '<div style="float: left; width: 50%;">' . $item['Category'] . '</div>';
                    echo '<div style="float: left; width: 50%;">' . $item['Amount'] . '</div>'; 
                    echo '<div style="clear: both;"></div>';
                     } ?> 
                </div>
            </td>
        </tr>
                            
      <?php }

    } ?>
    
    </table>


<?php
} else {
    
    echo 'No transactions';
    
}


?>
<script src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<!-- Toggle Transaction details -->
<script type="text/javascript">
    function toggle_visibility(id) {
       var e = document.getElementById(id);
       $(e).toggle();
    }
</script>