Jump to content

Is there a way to merge these two tables?


Recommended Posts

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
    }
  }
}
Link to post
Share on other sites

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 by Barand
  • Like 1
Link to post
Share on other sites

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;
}
Link to post
Share on other sites

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
}

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.