Need help finding if the statement is true or not inside foreach loop

[imgrooot]

So I two tables. Users and earnings.  I retrieve earning balance of each user. This works fine. But I also want to find out outside of the foreach loop if any of the earning balance inside the loop is greater than the listed amount. 

For e.g. Say there are 10 users. The listed amount is $1,000. Only 1 of the 10 users have an earning balance of greater than $1,000. I want the statement to return true under that condition. It could even be 10/10 users that have a greater earning balance. As long as at least one of them have it, it should return true. 

Here is my code. How do I fix the "is_true" part to get the results I want?

$get_members = $db->prepare("SELECT user_id, user_name FROM users");
$get_members->execute();
$result_members = $get_members->fetchAll(PDO::FETCH_ASSOC);
if(count($result_members) > 0) {
  foreach($result_members as $row) {
    $get_user_id  	= $row['user_id'];
    $get_username  	= $row['username'];

    $get_principal = $db->prepare("SELECT deposit FROM earnings WHERE e_type = :e_type AND sent_to = :sent_to AND principal = :principal");
    $get_principal->bindValue(':e_type', 1);
    $get_principal->bindParam(':sent_to', $get_user_id);
    $get_principal->bindValue(':principal', 1);
    $get_principal->execute();
    $result_principal = $get_principal->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_principal) > 0) {
      $ub = 0;
      foreach($result_principal as $key=>$row) {
        $ub+= $row['deposit'];
      }
      $p_deposit 	= $ub;
    } else {
      $p_deposit 	= 0;
    }

    $get_withdrawal = $db->prepare("SELECT withdrawal FROM earnings WHERE e_type = :e_type AND sent_to = :sent_to AND principal = :principal");
    $get_withdrawal->bindValue(':e_type', 2);
    $get_withdrawal->bindParam(':sent_to', $get_user_id);
    $get_withdrawal->bindValue(':principal', 0);
    $get_withdrawal->execute();
    $result_withdrawal = $get_withdrawal->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_withdrawal) > 0) {
      $ub = 0;
      foreach($result_withdrawal as $key=>$row) {
        $ub+= $row['withdrawal'];
      }
      $t_principal 	= $ub;
    } else {
      $t_principal 	= 0;
    }

    $earning_balance = number_format($p_deposit - $t_principal);

    ?>
    <div>
      <div><?php echo $get_username; ?></div>
      <div><?php echo $earning_balance; ?></div>
    </div>
    <?php

    if($earning_balance > 1000) {
      $is_true = 1;
    } else {
      $is_true = 0;
    }

  }
}

if($is_true == 1) {
  echo 'yes';
} else {
  echo 'no';
}

 

[Barand]

Avoid  running queries inside loops - it drains resources. Use joins instead and run a single query. The loop then becomes unnecessary.

If you must run prepared queries inside loops do not prepare the statement and bind inside the loop. Do those first. Inside the loop you just change the values and execute.

$listed_amount = 1000;

$sql = "SELECT u.username
             , SUM(amount) as total
        FROM user u 
            LEFT JOIN
            (
                SELECT sent_to as user_id
                     , deposit as amount
                FROM earnings
                WHERE e_type = 1 AND principal = 1
                UNION
                SELECT sent_to
                     , -withdrawal
                FROM earnings
                WHERE e_type = 2 AND principal = 0
            ) tot USING (user_id)
        GROUP BY u.user_id
        HAVING total >= :listed";
$stmt = $db->prepare($sql);
$stmt->execute( [':listed' => $listed_amount] );

$is_true = $stmt->rowCount() > 0;

 

[imgrooot]

10 hours ago, Barand said:

Avoid  running queries inside loops - it drains resources. Use joins instead and run a single query. The loop then becomes unnecessary.

If you must run prepared queries inside loops do not prepare the statement and bind inside the loop. Do those first. Inside the loop you just change the values and execute.

$listed_amount = 1000;

$sql = "SELECT u.username
             , SUM(amount) as total
        FROM user u 
            LEFT JOIN
            (
                SELECT sent_to as user_id
                     , deposit as amount
                FROM earnings
                WHERE e_type = 1 AND principal = 1
                UNION
                SELECT sent_to
                     , -withdrawal
                FROM earnings
                WHERE e_type = 2 AND principal = 0
            ) tot USING (user_id)
        GROUP BY u.user_id
        HAVING total >= :listed";
$stmt = $db->prepare($sql);
$stmt->execute( [':listed' => $listed_amount] );

$is_true = $stmt->rowCount() > 0;

 

Good to know. Thanks to you I am learning more about these joins and simplifying my queries.  

As per your example above, it does work. The only thing is that I am getting different earning balance results from my original queries. 

Here is the new code based on your example. Can you check if it's correct? Also I take it "-withdrawal" is suppose to subtract from the "deposit"? 

$listed_amount = 1000;

$sql = "SELECT u.username
             , SUM(amount) as total
        FROM users u
            LEFT JOIN
            (
                SELECT sent_to as user_id
                     , deposit as amount
                FROM earnings
                WHERE e_type = 1 AND status = 1 AND principal = 1
                UNION
                SELECT sent_to
                     , -withdrawal
                FROM earnings
                WHERE e_type = 2 AND status = 1 AND principal = 0
            ) tot USING (user_id)
        WHERE u.ref = 1
        GROUP BY u.user_id
        HAVING total >= :listed";
$stmt = $db->prepare($sql);
$stmt->bindParam(':listed', $listed_amount);
$stmt->execute();
$result_stmt = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(count($result_stmt) > 0) {
  foreach($result_stmt as $row) {
    $get_username  			= $row['username'];
    $earning_balance 		= $row['total'];

    ?>
    <div>
      <div><?php echo $get_username; ?></div>
      <div><?php echo $earning_balance; ?></div>
    </div>
    <?php
  }
}
$is_true = $stmt->rowCount() > 0;

if($is_true > 0) {
  echo 'yes';
} else {
  echo 'no';
}

 

Edited June 3, 2018 by imgrooot

[Barand]

All I can say is my query was correct when run against my test tables withe given criteria. I cannot verify that yours is correct without

1. your data
2. knowing the business requirement and conditions (such as the meaning of values in e_type and pricipal columns)
3. knowing the predicted results from that data

But you should be able to.

[imgrooot]

32 minutes ago, Barand said:

All I can say is my query was correct when run against my test tables withe given criteria. I cannot verify that yours is correct without

1. your data
2. knowing the business requirement and conditions (such as the meaning of values in e_type and pricipal columns)
3. knowing the predicted results from that data

But you should be able to.

Well the e_type and principal columns are a means to distinguish the time of payments are in the table. 

I did double check with my original code. It seems like the issue is with the UNION part of your query. I removed the union and just retrieved the "deposit" and the number is correct. I also checked the "withdrawal" amount by replacing the deposit and that number is also correct. 

So the issue is the subtracting the withdrawal from the deposit through the UNION. Something is wrong with that. Are you sure it's correct?

[Barand]

try changing "UNION" to "UNION ALL" in case there are deposits and withdrawals for the same amounts.

Also, LEFT JOIN needs only be an INNER JOIN.

Edited June 4, 2018 by Barand

[imgrooot]

2 hours ago, Barand said:

try changing "UNION" to "UNION ALL" in case there are deposits and withdrawals for the same amounts.

Also, LEFT JOIN needs only be an INNER JOIN.

Yep, UNION ALL does the trick. Works perfectly now. Thank you so much!

Here is the updated code.

$listed_amount = 1000;

$sql = "SELECT u.username
             , SUM(amount) as total
        FROM users u
            INNER JOIN
            (
                SELECT sent_to as user_id
                     , deposit as amount
                FROM earnings
                WHERE e_type = 1 AND status = 1 AND principal = 1
                UNION ALL
                SELECT sent_to
                     , -withdrawal
                FROM earnings
                WHERE e_type = 2 AND status = 1 AND principal = 0
            ) tot USING (user_id)
        WHERE u.ref = 1
        GROUP BY u.user_id
        HAVING total >= :listed";
$stmt = $db->prepare($sql);
$stmt->bindParam(':listed', $listed_amount);
$stmt->execute();
$result_stmt = $stmt->fetchAll(PDO::FETCH_ASSOC);
if(count($result_stmt) > 0) {
  foreach($result_stmt as $row) {
    $get_username  			= $row['username'];
    $earning_balance 		= $row['total'];

    ?>
    <div>
      <div><?php echo $get_username; ?></div>
      <div><?php echo $earning_balance; ?></div>
    </div>
    <?php
  }
}
$is_true = $stmt->rowCount() > 0;

if($is_true > 0) {
  echo 'yes';
} else {
  echo 'no';
}

 

[Barand]

Does your data have any deposit or withdrawal values that are negative in the earnings table?

[imgrooot]

2 hours ago, Barand said:

Does your data have any deposit or withdrawal values that are negative in the earnings table?

You mean if any of the users have an earning balance in the negative (e.g. -100)? No I don't think so. Users are never able to withdraw more than their balance.

But there is more than one type of withdrawal and deposit and I only needed to use the two I listed.

Edited June 4, 2018 by imgrooot

[Barand]

I didn't mean the balances. I was asking if any the values that you are totalling (deposits or withdrawal values) have a negative value. Are there any deposits of -X or any withdrawals of -Y?

Does this find any?

SELECT COUNT(*)
FROM earnings
WHERE (deposit < 0) OR (withdrawal < 0) 

 

[imgrooot]

5 hours ago, Barand said:

I didn't mean the balances. I was asking if any the values that you are totalling (deposits or withdrawal values) have a negative value. Are there any deposits of -X or any withdrawals of -Y?

Does this find any?

SELECT COUNT(*)
FROM earnings
WHERE (deposit < 0) OR (withdrawal < 0) 

 

I see. But no, deposits and withdrawals will never have a value less than 0.