CODEWITHSUNDEEP
mysql
Majid Fouladpour
Majid Fouladpour

Reputation: 30252

Could these two selects be crushed into one?

$usersQuery = 'SELECT user, budget FROM users';
$usersResult = mysqli_query($link, $usersQuery);
while($usersRow = mysqli_fetch_assoc($usersResult)) {
  $user = $usersRow['user'];
  $budget = $usersRow['budget'];
  $spendingsQuery = "SELECT SUM(`cost`) AS spent FROM spendings WHERE user = '$user'";
  $spendingsResult = mysqli_query($link, $spendingsQuery);
  $spendingRow = mysqli_fetch_assoc($spendingsResult);
  $spent = $spendingRow['spent'];
  $innerTaleRows .= "<tr><td>$user</td><td>$budget</td><td>$spent</td></tr>";
}

Upvotes: 0

Views: 55

Answers (3)

Request
Request

Reputation: 1

$usersQuery = 'SELECT SUM(`cost`) as spent, users.user, users.budget AS spent FROM spendings INNER JOIN users on users.user=spendings.user GROUP BY users.user, users.budget';
$usersResult = mysqli_query($link, $usersQuery);
while($usersRow = mysqli_fetch_assoc($usersResult)) {
  $user = $usersRow['user'];
  $budget = $usersRow['budget'];
  $spent = $usersRow['spent'];
  $innerTaleRows .= "<tr><td>$user</td><td>$budget</td><td>$spent</td></tr>";
}

Upvotes: 0

user562854
user562854

Reputation:

Yes they can.

MySQL query:

SELECT users.user as user, users.budget as budget, SUM(spendings.cost) AS spent FROM `users` LEFT OUTER JOIN `spendings` ON spendings.user = users.user

Full code:

$usersQuery = 'SELECT users.user, users.budget, SUM(spendings.cost) AS spent FROM `users` LEFT OUTER JOIN `spendings` ON spendings.user = users.user';
$usersResult = mysqli_query($link, $usersQuery);
while($usersRow = mysqli_fetch_assoc($usersResult)) {
    $user = $usersRow['user'];
    $budget = $usersRow['budget'];
    $spent = $usersRow['spent'];
    $innerTaleRows .= "<tr><td>$user</td><td>$budget</td><td>$spent</td></tr>";
}

Upvotes: 2

cdhowie
cdhowie

Reputation: 169028

Yes:

SELECT users.user AS user,
       users.budget AS budget,
       SUM(spendings.cost) AS spent
FROM users

LEFT OUTER JOIN spendings
ON spendings.user = users.user

GROUP BY users.user, users.budget

Upvotes: 1

Related Questions