在while循环中组合两个SQL查询

Question

Basically, I want to display the combined output of the below sql queries in a table using one while loop. I have a table called participating_institutions which holds unique code and names for all institutions. I also have trades table where each of the institutions can either be a buyer, a seller or both (Yes, an institution can do a trade for its two clients). With the help of good guys here, I was able to match each code in trades table with corresponding names in participating institution table using sql JOIN as indicated in the queries. The first query below will sum all the buy values for each firm and the second query will do same for their sales. However, the table I want to display will have the [Sum(buy_value) + Sum(sell_value)] for each firm in the while loop. How do I achieve this using either mysql or php. Note: The trade table has two column in it for buy_firm_code and sell_firm_code. The records they hold is the same depending on which side of the trade a firm participated. Below is what I have done so far.

<?php
$con=mysqli_connect("localhost","db_user","password","database");

$total_value = 0;
$buy_value = 0;
$sell_value = 0;
$firm_name = "";
$institution_code = "";
$display = "";

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$buy_sql="SELECT p.institution_code, p.institution, SUM(t.trade_value)
value_bought FROM trades t JOIN participating_institutions p ON     p.institution_code = t.buy_firm_code GROUP BY t.buy_firm_code ORDER BY value_bought DESC";

if ($buy_result = mysqli_query($con, $buy_sql))
{
// Fetch one and one row for buy side
while($row=mysqli_fetch_array($buy_result)) { 
    $buy_value .= $row['value_bought'].'<br>';
    $institution_code .= $row['institution_code'].'<br>'; 
    $firm_name .= $row['institution'].'<br>'; 

}

}

$sell_sql = "SELECT  p.institution_code, p.institution, SUM(t.trade_value) value_sold FROM trades t JOIN participating_institutions p ON p.institution_code = t.sell_firm_code GROUP BY t.sell_firm_code ORDER BY value_sold DESC";

if ($sell_result = mysqli_query($con, $sell_sql))
{
// Fetch one and one row for sale side
while($row=mysqli_fetch_array($sell_result)) {
    $sell_value.= $row['value_sold'].'<br>';
    $institution_code .= $row['institution_code'].'<br>'; 
    $firm_name.= $row['institution'].'<br>';

}

}
$total_value = $buy_value + $sell_value;
$display .= '<table><tr><td>'.$institution_code.'</td><td>'.$firm_name .'</td><td>'.$total_value.'</td></tr></table>';
echo $display;

// Free result set
mysqli_free_result($buy_result);
mysqli_free_result($sell_result);
mysqli_close($con);
?>

When I run this two problems:

1. The firms repeat, I guess because I am running two queries. What I want is one output record for each firm.

2. I don't get the overall total for each firm rather I get the sum of total buy and total sell of the first record

instead of

Firmcode A:  FirmName A: 20,000 
Firmcode B:  FirmName B: 40,000
Firmcode C:  FirmName C: 50,000

I get this

Firmcode A:  FirmName A: 
Firmcode B:  FirmName B:    20,000
Firmcode C:  FirmName C:

Answer 1

Thanks guys. I have been able to resolve the issue. I created two views in my database using the two queries above. I then used a third query in my code to join the two views using the SUM aggregate function for my total buy and total sell values. It works perfectly now. Thanks for all your contribution.