How do I Compare outer variable from a nested loop in php

Question

I'm fetching data from two table Tenders and payments I want to display monthly payments with monthly tenders in on array. But I need to compare if the month and year from the first loop is the same as the month and year from the nested loop

$first_sql = "SELECT YEAR(toll.transaction_date) 'year', MONTHNAME(toll.transaction_date) 'month', COUNT(toll.toll_id) 'count', SUM(toll.tender) 'tender' 
                  FROM toll WHERE company_id = '$COMPANY_ID' 
                  GROUP BY YEAR(toll.transaction_date), MONTHNAME(toll.transaction_date), MONTH(toll.transaction_date) 
                  ORDER BY YEAR(toll.transaction_date) DESC, MONTH(toll.transaction_date) DESC limit 5;";
$scnd_sql = "SELECT YEAR(company_payment_detail.payment_date) 'year', MONTHNAME(company_payment_detail.payment_date) 'month', SUM(company_payment_detail.payment_amount) 'payment' 
                    FROM company_payment_detail
                    JOIN company_account ON company_payment_detail.company_account_id = company_account.company_account_id 
                    JOIN company ON company.company_id = company_account.company_id 
                    WHERE company_account.company_id = '$COMPANY_ID' 
                    GROUP BY YEAR(company_payment_detail.payment_date), MONTHNAME(company_payment_detail.payment_date),MONTH(company_payment_detail.payment_date)
                    ORDER BY YEAR(company_payment_detail.payment_date) DESC, MONTH(company_payment_detail.payment_date) DESC limit 5";

The Results

The Loops

$run_first_sql= $conn->query($first_sql);
$run_scnd_sql = $conn->query($scnd_sql);

$chart_data = [];

//first loop

while($row= $run_first_sql->fetch_assoc()){

 $arr1 = array(
      'y' => $row['year'],
      'm' => $row['month'],
      'c' => (int)$row['count'],
      't' => (float)$row['tender']
  ); 
  while($row2= $run_scnd_sql->fetch_assoc()){
    if($row['year'] == $row2['year'] && $row['month'] == $row2['month']){
      $arr2 = array(
        'p' =>(float)$row2['payment'],
      );
   }else{
       $arr2 = array(
        'p' =>(float)0,
      );  
   }
 }
    $arr1 = array_merge($arr1, $arr2);  
  array_push($chart_data1, $arr1);
}

print_r($chart_data1);

You should join the tables in the query, not do two separate queries. — Barmar, Feb 06 '20 at 13:14
What result are you expecting to get, what are you getting instead? — Barmar, Feb 06 '20 at 13:24
See https://stackoverflow.com/questions/37978511/join-tables-with-sum-issue-in-mysql/37979049#37979049 for summing and counting multiple tables when joining. — Barmar, Feb 06 '20 at 13:26
@Barmar Where a payment was not made it should be 0 otherwise is should show the payment, I'm getting the same figure on payment on all loop instances — Wells, Feb 06 '20 at 13:28
You need to use `LEFT JOIN` to get an empty result from the second query if there's no payment for the month. — Barmar, Feb 06 '20 at 13:31
Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Feb 06 '20 at 14:50

score 1 · Accepted Answer · answered Feb 06 '20 at 13:58

Do a LEFT JOIN of the two queries. If there's no row in the second query for the same month, you'll get NULL in those columns.

SELECT t1.year, t1.month, t1.count, t1.tender, IFNULL(t2.payment, 0) AS payment
FROM (
    SELECT YEAR(toll.transaction_date) 'year', MONTHNAME(toll.transaction_date) 'month', COUNT(toll.toll_id) 'count', SUM(toll.tender) 'tender' 
    FROM toll WHERE company_id = '$COMPANY_ID' 
    GROUP BY year, month 
    ORDER BY year DESC, month DESC 
    limit 5) AS t1
LEFT JOIN (
        SELECT YEAR(company_payment_detail.payment_date) 'year', MONTHNAME(company_payment_detail.payment_date) 'month', SUM(company_payment_detail.payment_amount) 'payment' 
    FROM company_payment_detail
    JOIN company_account ON company_payment_detail.company_account_id = company_account.company_account_id 
    JOIN company ON company.company_id = company_account.company_id 
    WHERE company_account.company_id = '$COMPANY_ID' 
    GROUP BY year, month 
    ORDER BY year DESC, month DESC 
    limit 5) AS t2
ON t1.year = t2.year AND t1.month = t2.month

This is the correct data, Just exactly what I want to produce thanks @barmar — Wells, Feb 07 '20 at 08:03

score 0 · Answer 2 · answered Feb 06 '20 at 13:27

You can perform that in a single query, the year and month of both table can be used as their relationship. The query will be looked like this:

$sql = 'SELECT * FROM tenders, payments WHERE tenders.year=payments.year AND tenders.month=payments.month';

while($row = $sql ->fetch_assoc()){
     $data = array(
         'month' => $row2['month'],
         'year' => $row2['year'],
         'count' => $row2['count'],
         'tender' => $row2['tender'],
      );
 }

All the tenders year that is equal to payments year AND tenders month that is equal payments month will be fetched.

So if you are going to stick with your way of fetching data, your if condtion code will look like this:

 while($row = $run_first_sql->fetch_assoc()){
    while($row2 = $scnd_sql ->fetch_assoc()){
        if($row['month'] == $row2['month'] && $row['year'] == $row2['year']){
            $data = array(
                'month' => $row['month'],
                'year' => $row['year'],
                'count' => $row['count'],
                'tender' => $row['tender'],
            );
        }
    }
}

The data of the first query as it iterate will be checked in your second query which takes time.

Don't use old-style cross-products when teaching newbies, use ANSI JOIN. — Barmar, Feb 06 '20 at 13:29
Nested loops won't work with `fetch()`, because the first iteration of the outer loop will read all the rows from the inner loop, and there won't be anything left to process. — Barmar, Feb 06 '20 at 13:30
So you are saying that in my previous coding style which I used his/her way of coding by iterating is not working? So all of my works is a lie? It seems that you are better than me and I am teaching him the way I solve that during my newbie time @Barmar — freetzyy, Feb 06 '20 at 13:36

How do I Compare outer variable from a nested loop in php

2 Answers2