将来自两个不同循环的数据放入谷歌饼图中

Question

So I have this code for my Google pie chart (pie chart code not included, but it works fine and displays data)

<?php 
    $query = "SELECT * FROM category WHERE type = 'Expense' ";
    $select_category_list = mysqli_query($connection, $query);
    while($row = mysqli_fetch_assoc($select_category_list)) {
    $cat_id = $row['id'];
    $cat_title = $row['title']; 
   echo "['$cat_title'" . ",";
   echo "$cat_id],";
}
?>

I put the echo in different lines, but it really does not matter to if they are in one line or in two lines, what matters is to get it finally working :) Instead of $cat_id I want to put the SUM ($cat_amount) calculated by this code:

$query = "SELECT category_id, SUM(amount) AS TotalAmount FROM spendee
WHERE type = 'Expense' group by category_id ";
    $expense_query = mysqli_query($connection, $query);
    while ($row = mysqli_fetch_assoc($expense_query)) {
         $cat_amount = $row['TotalAmount'];
    }

I have tried putting while loop in a while loop, foreach loop in a while loop, putting $cat_amount instead of $cat_id (that only displays one SUM) and so on. Nothing worked. I have not tried the for loop, but I assume the result would be the same + I am not sure how would I define the times that the loop needs to run, since there could be many type 'Expense' categories added in time.

Any help would be very much appreciated. Thank you and have a lovely day.

Answer 1

maybe i'm missing something, but looks like you could join the two tables in one query

this would get the desired result in one query / loop...

sql:

SELECT a.title as Title, SUM(b.amount) AS TotalAmount FROM category a, spendee b WHERE b.category_id = a.id and b.type = 'Expense' group by a.title

php:

<?php
  $query = "SELECT a.title as Title, SUM(b.amount) AS TotalAmount FROM category a, spendee b WHERE b.category_id = a.id and b.type = 'Expense' group by a.title";
  $select_category_list = mysqli_query($connection, $query);
  while ($row = mysqli_fetch_assoc($select_category_list)) {
    $cat_title = $row['Title'];
    $cat_amount = $row['TotalAmount'];
    echo "['$cat_title'" . ",";
    echo "$cat_amount],";
  }
?>