Is there a way to merge these two tables?

[imgrooot]

This is what I am trying to accomplish.

 

1. I have two tables. T1: Users and T2: Earnings.

2. I want to find a single random user whose deposit amount is greater than the listed amount.

 

Below is my foreach loop within foreach loop trying to do the above. I was wondering if there is a more simple way to do this task? Is there a way to combine these two queries together?

$listed_amount = 1000;

// find a random user
$find_user = $db->prepare("SELECT user_id FROM users WHERE user = :user AND active = :active ORDER BY RAND() LIMIT 1");
$find_user->bindValue(':user', 1);
$find_user->bindValue(':active', 1);
$find_user->execute();
$result_user = $find_user->fetchAll(PDO::FETCH_ASSOC);
if(count($result_user) > 0) {
  foreach($result_user as $row) {
    $user_id   =	$row['user_id'];

    // find that random user's deposit amount
    $get_deposits = $db->prepare("SELECT deposit FROM earnings WHERE sent_to = :sent_to AND status = :status");
    $get_deposits->bindParam(':sent_to', $user_id);
    $get_deposits->bindValue(':status', 1);
    $get_deposits->execute();
    $result_deposits = $get_deposits->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_deposits) > 0) {
      $ub = 0;
      foreach($result_deposits as $key=>$row) {
        $ub+= $row['deposit'];
      }
      $total_deposits = $ub;
    }
    if($total_deposits > $listed_amount) {
      // show the user
    } else {
      // hide the user
    }
  }
}

[Barand]

try

SELECT *
FROM
    (
	SELECT u.user_id
	     , SUM(deposit) as totdeposit
	FROM users as u
	    INNER JOIN
	    earnings as e
	      ON u.user_id = ee.sent_to AND e.status = 1
	WHERE u.active = 1
	HAVING totdeposit > :listedamount
	) as totals
ORDER BY RAND()
LIMIT 1

Edited November 11, 2017 by Barand

[imgrooot]

try

SELECT *
FROM
    (
	SELECT u.user_id
	     , SUM(deposit) as totdeposit
	FROM users as u
	    INNER JOIN
	    earnings as e
	      ON u.user_id = ee.sent_to AND e.status = 1
	WHERE u.active = 1
	HAVING totdeposit > :listedamount
	) as totals
ORDER BY RAND()
LIMIT 1

 

Here is your method with my full query.  Doesn't seem to give me the correct result.  The sum of the deposit seems to be greater than what it should be for that user when i echo "totdeposit". I'll keep trying but in the mean time can you see what i am doing wrong below?

$level_amount = 200;

$find_user = $db->prepare("SELECT * FROM
    (
  SELECT u.user_id
       , SUM(deposit) as totdeposit
  FROM users as u
      INNER JOIN
      partner_earnings as e
        ON u.user_id = e.sent_to AND e.status = 1 AND e.e_type = 1 AND e.principle = 1
  WHERE u.active = 1
  HAVING totdeposit > :listedamount
  ) as totals
ORDER BY RAND() LIMIT 1
");
$find_user->bindParam(':listedamount', $level_amount);
$find_user->execute();
$result_user = $find_user->fetchAll(PDO::FETCH_ASSOC);
if(count($result_user) > 0) {
  foreach($result_user as $row) {
    $user_id      = $row['user_id'];
    $totdeposit   = $row['totdeposit'];
  }
  echo $user_id;
  echo $totdeposit;
}

[Barand]

Sorry, there was a

GROUP BY user_ID

before the HAVING. It must've got lost when I edited.

[imgrooot]

Sorry, there was a

GROUP BY user_ID

before the HAVING. It must've got lost when I edited.

 

 

Ah yes that does the trick.  Below is the updated code.  Now my next question is, what if I want to subtract another form of deposit from the current deposit in the same table?

For e.g. 

The deposit in the original query has e_type as 1 and e.principle as 1, but in the line below it's different. So what I am essentially trying to do is find the sum of deposit type#1 and also find a sum of deposit type#2.  Then I want to get a balance from them after I subtract type#2 from type#1. And then I compare that balance price to level_amount.   

partner_earnings as e
   ON u.user_id = e.sent_to AND e.status = 1 AND e.e_type = 2 AND e.principle = 0

Since I already have type#1 deposit below, how do I add type#2 as shown above and subtract them to get a new balance?

$level_amount = 200;

$find_user = $db->prepare("SELECT * FROM
    (
  SELECT u.user_id
       , SUM(deposit) as totdeposit
  FROM users as u
      INNER JOIN
      partner_earnings as e
        ON u.user_id = e.sent_to AND e.status = 1 AND e.e_type = 1 AND e.principle = 1
  WHERE u.active = 1
  GROUP BY user_id HAVING totdeposit > :listedamount
  ) as totals
 ORDER BY RAND() LIMIT 1
");
$find_user->bindParam(':listedamount', $level_amount);
$find_user->execute();
$result_user = $find_user->fetchAll(PDO::FETCH_ASSOC);
if(count($result_user) > 0) {
  foreach($result_user as $row) {
    $user_id   =	trim($row['user_id']);
    $deposit   =	trim($row['totdeposit']);
  }
  ?>
  <div class="result-single">
    <div class="col-full">
      <div class="n1"><span><?php echo $user_id; ?></span></div>
      <div class="n1"><span><?php echo $deposit; ?></span></div>
    </div>
  </div>
  <?php
}

[Barand]

You could use

   ...
 , SUM( CASE e.type
           WHEN 1 THEN deposit
           WHEN 2 THEN -deposit
           ELSE 0
        END ) as net_total

[imgrooot]

You could use

   ...
 , SUM( CASE e.type
           WHEN 1 THEN deposit
           WHEN 2 THEN -deposit
           ELSE 0
        END ) as net_total

 

And what about the "e.principle = 0" and "e.principle = 1"? How would that fit in the above code?

[Barand]

That's the first time principal=0 has reared its head.

 

Do you want to give the full story so we're not here forever making changes.

 

My pet hate is dialogues that go

 

"This my problem"

"OK, this is the solution."

"Well actually that wasn't the real problem, it's ..."

"OK, do this"

"Can we also do this extra bit...?

 

.... and so on, and on, and on.