0

Okay so I have a query like this one

$get_downlines = "SELECT * FROM referrals WHERE ref_upline = :rupline";
$get_downlines = $pdo->prepare($get_downlines);
$get_downlines-> bindValue(':rupline', $sessionid);
$get_downlines-> execute();

while($fetch_downlines = $get_downlines->fetch()){
   $rdownline = $fetch_downlines['ref_downline'];

    $dr = "SELECT * FROM `ads_viewed` WHERE av_user = :user";
    $dr = $pdo->prepare($dr);
    $dr-> bindValue(':user', $rdownline);
    $dr-> execute();
    echo $dr_count = $dr->rowCount();
}

The code above gives me the row counts as say 3456 (all are separate counts like 3,4,5,6). Now I want to sum up all these rows here and get the result as 3+4+5+6 = 18. And assign it to a global variable which can be used anywhere outside while loop (if possible). How can this be done?

Improve this question
3
  • maybe yo're looking for something like this: stackoverflow.com/questions/883365/row-count-with-pdo?rq=1 Commented Mar 10, 2017 at 17:34
  • btw, your query doesn't have a bind for :direct Commented Mar 10, 2017 at 17:40
  • @Fred-ii- Thanks for pointing it out :) Commented Mar 10, 2017 at 18:03

2 Answers 2

Reset to default
1

You could do the following:

$get_downlines = "SELECT * FROM referrals WHERE ref_upline = :rupline";
$get_downlines = $pdo->prepare($get_downlines);
$get_downlines-> bindValue(':rupline', $sessionid);
$get_downlines-> bindValue(':direct', "direct");
$get_downlines-> execute();

$totalrows;

while($fetch_downlines = $get_downlines->fetch()){
   $rdownline = $fetch_downlines['ref_downline'];

    $dr = "SELECT * FROM `ads_viewed` WHERE av_user = :user";
    $dr = $pdo->prepare($dr);
    $dr-> bindValue(':user', $rdownline);
    $dr-> execute();
    echo $dr_count = $dr->rowCount();
    $totalrows+= $dr->rowCount();
}
echo $totalrows;

First you create the $totalrows; variable outside of the loop. You can increment this variable with the amount of rows in your query using $totalrows += $dr->rowCount(); inside of the while loop

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I had tried this but forgot to echo it outside.. my bad.. thanks for pointing it out.. :)
0

This can be done with a single query:

$stmt = $pdo->prepare("
    SELECT COUNT(*) AS cnt
    FROM referrals 
    JOIN ads_viewed ON ads_viewed.av_user = referrals.ref_downline
    WHERE ref_upline = :rupline
");
$stmt->execute(['rupline' => $sessionid]);
$dr_count = $stmt->fetchColumn();

Note: Everytime you execute an SQL query in a while-fetch-loop you probably better use a JOIN. And everytime you use SELECT * just to get the number of rows, you are wasting resources and should use COUNT(*) instead.

Improve this answer

4 Comments

this was good if I had to count with a single user from the referrals table but since there are multiple users here and I need to count row from ads_viewed from each of them I need to use while loop here.
@ShubhamJha where do you see, that i limit it to a single user? Did you try it? It should return the same result as the accepted answer - but with less code and more efficiently.
oh sorry my bad.. yes it works.. learned something new.. can you edit your answer a little so that I can remove the negative voting?
@ShubhamJha I added a note.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.