I am extending the How to use Inner Joins with where clause? question because I need some extrnsion of this query results.
Here I have made a php script as below. This because a one table is in another server and it is not in same localhost. So I have created two different sql queries
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "project1";
$servername2 = "112.76.456.89";
$username2 = "user";
$password2 = "user990";
$dbname2= "project2";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Create connection
$conn2 = mysqli_connect($servername2, $username2, $password2, $dbname2);
// Check connection
if (!$conn2) {
die("Connection failed: " . mysqli_connect_error());
}
$sql1 = "SELECT t2.UserID,
t1.Username,
t2.TotalAmountResetDate,
t2.TotalAmount,
sum(COALESCE(Totalnvested,0)) Totalnvested
FROM userdetails1.UserTable t1
INNER JOIN userdetails2.AmountTable t2
ON t1.UserID = t2.UserID
LEFT JOIN userdetails2.TotalInvestmentsTable investment
ON t1.Username = investment.Username AND
investment.total_invested_date BETWEEN '2022-01-31' and '2022-02-12'
WHERE t2.TotalAmountResetDate='2022-01-31' ";
$sql2="SELECT Username,sum(TotalExpenses) from TotalExpensesTable where date_of_expense_date between '2022-01-31' and '2022-02-12'
group by Username";
$result1 = mysqli_query($conn, $sql);
$result2 = mysqli_query($conn2, $sql2);
mysqli_close($conn);
mysqli_close($conn2);
?>
From each sql query I can get the output as below.
Query1;
UserId Username TotalAmountResetDate TotalAmount Totalnvested
1 John 1/31/2022 -12 45000
2 Micheal 1/31/2022 345 45000
3 Ann 1/31/2022 213 45000
4 Shasha 1/31/2022 0 0
5 mike 1/31/2022 0 0
Query2:
Username TotalExpenses
John 2543
Micheal 2345
Ann 3290
Now I need to get the output by combining these two as below,
UserId Username TotalAmountResetDate TotalAmount Totalnvested TotalExpenses TotalBalance
1 John 1/31/2022 -12 45000 2543 42445
2 Micheal 1/31/2022 345 45000 2345 43000
3 Ann 1/31/2022 213 45000 3290 41923
4 Shasha 1/31/2022 0 0 0 0
5 mike 1/31/2022 0 0 0 0
Can someone show me how should I get this kind of output using merging of above two queries? Do I need to use foreach loop?
I need to get UserId,Username,TotalAmountResetDate,TotalAmount,Totalnvested,TotalExpenses,TotalBalance then I can show it using a HTML table.
CodePudding user response：
An example using hashMap to merge two datasets.
<?php
$result1 = mysqli_query($conn, $sql);
$result2 = mysqli_query($conn2, $sql2);
$res1 = mysqli_fetch_all($result1, MYSQLI_ASSOC);
$res2 = mysqli_fetch_all($result2, MYSQLI_ASSOC);
$hashMapRes2 = array_reduce($res2, function ($carry, $item) {
$username = $item['Username'];
if (!isset($carry[$username])) {
$carry[$username] = $item;
}
return $carry;
}, []);
$output = array_map(function ($item) use ($hashMapRes2) {
$username = $item['Username'];
if (isset($hashMapRes2[$username])) {
$item['TotalExpenses'] = $hashMapRes2[$username]['TotalExpenses'];
} else {
$item['TotalExpenses'] = 0;
}
return $item;
}, $res1);
print_r($output);
Output:
Array
(
[0] => Array
(
[UserId] => 1
[Username] => John
[TotalAmountResetDate ] => 1/31/2022
[TotalAmount] => -12
[Totalnvested] => 3055
[TotalExpenses] => 11
)
[1] => Array
(
[UserId] => 1
[Username] => Micheal
[TotalAmountResetDate ] => 1/31/2022
[TotalAmount] => 50
[Totalnvested] => 3055
[TotalExpenses] => 12
)
[2] => Array
(
[UserId] => 1
[Username] => Shasha
[TotalAmountResetDate ] => 1/31/2022
[TotalAmount] => -12
[Totalnvested] => 3055
[TotalExpenses] => 0
)
)
CodePudding user response：
In this case, the joining table is not the answer because the two tables are in different databases on different servers. The simple solution is using a dependent function with parameters for the second query. Example logic here:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "project1";
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Write a query to get local server data.
$sql = "...............";
$result = mysqli_query($conn, $sql);
//then use a function to get data from the remote database
function dataBy_userName($username){
$servername2 = "112.76.456.89";
$username2 = "user";
$password2 = "user990";
$dbname2= "project2";
$conn2 = mysqli_connect($servername2, $username2, $password2, $dbname2);
$sql = ".............. where username = '$username'";
$result2 = mysqli_query($conn2, $sql2);
return $result2;
}
?>
//then for html table
<table>
<tr>
// table headings
<tr>
//then fetch the 1st query result
<?php
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) { ?>
<tr>
<td>.....</td>
<td>.....</td>
<td>.....</td>
<td>.....</td>
<td><?= $totalInvested = ..... ?></td>
// then call the function of remote data
<?php
$result2 = dataBy_userName($row['username']));
if (mysqli_num_rows($result2 ) > 0) {
while($row2 = mysqli_fetch_assoc($result2)) { ?>
<td><?= $totalExpenses = ..... ?></td>
<?php }
}else{?>
<td><?= $totalExpenses = 0 ?></td>
<?php } ?>
<td><?= $totalInvested - $totalExpenses ?></td>
</tr>
<?php }
}
?>
</table>
#Note: Here is the logic of how it can be done simply. But the applied code structure should be better & you should use PDO and OOP instead of this procedural method. The code pattern has a huge risk of SQL injection if it's aslo used for other queries such as insert, update, delete.