Count total sum of rows in while loop

Question

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?

maybe yo're looking for something like this: http://stackoverflow.com/questions/883365/row-count-with-pdo?rq=1 — Alex Slipknot, Mar 10 '17 at 17:34

score 1 · Answer 1 · answered Mar 10 '17 at 17:32

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

I had tried this but forgot to echo it outside.. my bad.. thanks for pointing it out.. :) — , Mar 10 '17 at 17:58

Paul Spiegel · Accepted Answer · 2017-03-11T10:44:38.167

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.

edited Mar 11 '17 at 10:44

answered Mar 10 '17 at 17:40

Paul Spiegel

30,925
5
44
53

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. – Mar 10 '17 at 18:16
@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. – Paul Spiegel Mar 10 '17 at 18:19
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? – Mar 11 '17 at 09:36
@ShubhamJha I added a note. – Paul Spiegel Mar 11 '17 at 10:45

Count total sum of rows in while loop

2 Answers2