The following is part of a personal budgeting program I'm writing.
This code pulls line item information from multiple tables and writes it into an array and then displays the information by transid => family => category => lineItems. Everything works, and I get the results I want out of it. My question is if there is a more efficient way to accomplish this task?
Since this is a personal program, I'm only asking so that I can improve my coding abilities.
";
$ul = "
";
$_li = "";
$data = [];
$itemCount = 0;
$arrayItemCount = 0;
$categoryQry = "SELECT a.itemQty, b.transDate, b.transID, b.amount, a.itemPrice, a.itemCategory, c.catFamily, a.itemName, a.itemSource FROM budget.lineItems AS a JOIN budget.quickEntry AS b ON a.transID = b.transID JOIN budget.categories AS c ON a.itemCategory = c.catName WHERE b.processed = 'y' ORDER BY c.catFamily, c.catName, b.transDate";
$categories = $conn->prepare ($categoryQry);
$categories->execute ();
$categories->store_result ();
$categories->bind_result ($itemQty, $transDate, $transID, $totalPrice, $itemPrice, $category, $family, $itemName, $source);
while ($categories->fetch ()) {
if (!isset($data[$transID]['amount'])) {
$data[$transID]['amount'] = 0;
}
if (!isset($data[$transID]['line'])) {
$data[$transID]['line'] = '';
}
if (!isset($data[$transID]['line'][$family]['amount'])) {
$data[$transID]['line'][$family]['amount'] = 0;
}
if (!isset($data[$transID]['line'][$family]['line'])) {
$data[$transID]['line'][$family]['line'] = '';
}
if (!isset($data[$transID]['line'][$family]['line'][$category]['amount'])) {
$data[$transID]['line'][$family]['line'][$category]['amount'] = 0;
}
if (!isset($data[$transID]['line'][$family]['line'][$category]['line'])) {
$data[$transID]['line'][$family]['line'][$category]['line'] = '';
}
$itemCount++;
$qtyPrice = $itemPrice * $itemQty;
$data[$transID]['amount'] += $qtyPrice;
$data[$transID]['transDate'] = $transDate;
$data[$transID]['source'] = $source;
$data[$transID]['line'][$family]['amount'] += $qtyPrice;
$data[$transID]['line'][$family]['line'][$category]['amount'] += $qtyPrice;
$data[$transID]['line'][$family]['line'][$category]['line'][$itemName] = ['itemQty' => $itemQty, 'itemPrice' => $itemPrice];
}
foreach ($data as $transID => $transValue) {
echo $transID .$s.$transValue['transDate'].$s.$transValue['source'].$s.$transValue['amount']. $ul;
foreach ($transValue['line'] as $category => $categoryValue) {
echo $li . $category .$s.$categoryValue['amount']. $ul;
foreach ($categoryValue['line'] as $line => $lineValue) {
echo $li . $line .$s.$lineValue['amount']. $ul;
foreach ($lineValue['line'] as $item => $details) {
echo $li . $item .$s . $details['itemQty'] . $s . $details['itemPrice'] . $_li;
}
echo $_ul . $_li;
}
echo $_ul . $_li;
}
echo $_ul . $br;
}
