Creating a table with data from 3 separate tables

[mslilafowler]

Hello

I’m trying to create a ledger for my customers, kind of like a bank statement. The data I have are in 3 separate tables:
Customer Orders (grand total)
Customer Payments (customer pays seller)
Customer Credits (seller pays customer)

The tricky thing is, I’d like to display all the records from the 3 tables in a single statement. Here’s my SQL query:

SELECT t_transaction_datetime, t_grand_total, p_payment_datetime, p_amount, c_credit_datetime, c_amount 
FROM transactions 
	LEFT JOIN payments ON p_transaction_id=t_transaction_id
	LEFT JOIN credits ON c_customer_id=t_customer_id 
WHERE t_customer_id = 123

The result of this table, obviously, creates some unwanted duplicate records. I tried to recreate the DB on sqlfiddle but it’s too large and the data isn’t transferring correctly, so I attached an image to show you what I mean. The underlined rows are the unwanted duplicate rows.

[phdr]

For the case of multiple rows per ‘Order ID’, what output do you want to produce?

[mslilafowler]

The duplicates should either be blank or 0.00, but I’m unsure of how to target the duplicate rows in order to manipulate them

[phdr]

I’m guessing that means you do want to show something from each row, rather than consolidate multiple rows for the same order id into a single row?

In any case, here’s probably the answer - when you fetch the data from the query, you would index/pivot it using the order id as the main array index. This will give you a sub-array of rows for each order id value. When you loop to display the result, you can use php’s count() function to get the number of rows for each order id value and use this as a rowspan attribute in the columns where you only want to display a value once per order id.

[mslilafowler]

“I’m guessing that means you do want to show something from each row, rather than consolidate multiple rows for the same order id into a single row?”

Correct. Because when customer makes a payment, or seller credits a customer, the Order ID is relevant so there will be “legal” duplicates in the Order ID field, but the rest of the cells in that row should be unique to each other.

I’m going to test out your idea in the am

[phdr]

Here’s an example using the first three rows of data -

<?php

// fake some data that would be fetched from the sql query
$stmt = [];
$stmt[] = ['date'=>'14 Feb 2022', 'order id'=>'1134', 'order total'=>'510', 'payment'=>'10', 'p date'=>'2022-02-15'];
$stmt[] = ['date'=>'14 Feb 2022', 'order id'=>'1134', 'order total'=>'510', 'payment'=>'500', 'p date'=>'2022-02-15'];
$stmt[] = ['date'=>'12 Mar 2022', 'order id'=>'1274', 'order total'=>'230', 'payment'=>'230', 'p date'=>'2022-03-15'];


// array to hold the indexed/pivoted data
$data = [];

// index/pivot the data using the order id as the main array index
foreach($stmt as $row)
{
	$data[ $row['order id'] ][] = $row;
}


// produce the output -
$output = '';
foreach($data as $order_id=>$arr1)
{
	// at this point, you have an $order_id
	// count($arr1) will tell you how many rows there are going to be in the output section,
	// that can be used for the rowspan attribute in date, order id, and order total columns
	$rs = count($arr1);
	$first = true;
	foreach($arr1 as $row)
	{
		// start tr
		$output .= "<tr>";
		// output the initial rowspan column(s)
		if($first)
		{
			// date, order id, order total
			$output .= "<td rowspan='$rs'>{$row['date']}</td>";
			$output .= "<td rowspan='$rs'>{$row['order id']}</td>";
			$output .= "<td rowspan='$rs'>{$row['order total']}</td>";
		}
		// output the unique row data
		$output .= "<td>{$row['payment']}</td><td>{$row['p date']}</td>";
					
		// output the final rowspan column(s)
		if($first)
		{
			// not used
			//$output .= "<td rowspan='$rs'>...</td>";					
		}
		$first = false;
		// end tr
		$output .= "</tr>";
	}
}

?>
<style>
table, th, td {
  border: 1px solid black;
}
</style>

<table>
  <tr>
    <th>Order Date</th>
    <th>Order ID</th>
    <th>Order Total</th>
    <th>Payment</th>
    <th>Payment Date</th>
  </tr>
<?php echo $output; ?>
</table>

Output:

Order Date	Order ID	Order Total	Payment	Payment Date
14 Feb 2022	1134	510	10	2022-02-15
			500	2022-02-15
12 Mar 2022	1274	230	230	2022-03-15

[mslilafowler]

And again, that is a brilliant solution, Thank you phdr. Been tinkering with the code solution for hours, and finally understanding it a bit! It took me a while to figure out why you did what, and how to get the correct Balance showing per row:

Here’s what I did:

					// output the initial rowspan column(s)
					if($first)
					{
						$active_purchases += $row['t_grand_total'];
						// date, order id, order total
						$detailed_ledger .= "<td rowspan='$rs'>{$t_transaction_datetime}</td>";
						$detailed_ledger .= "<td rowspan='$rs'>{$row['t_transaction_id']}</td>";
						$detailed_ledger .= "<td rowspan='$rs'>{$t_grand_total}</td>";
						$current_balance += ($row['t_grand_total']);
					}

					// output the unique row data
					$current_balance -= $row['p_amount'];
					$current_balance -= $row['c_amount'];

					$detailed_ledger .= "<td>{$row['p_amount']}</td><td>{$p_payment_datetime}</td><td>{$row['c_amount']}</td><td>{$c_credit_datetime}</td><td>{$current_balance}</td>";