php - count total num rows inside while loop

Question

I've got this piece of code:

  $i=0;
      $start_date = date("Y/m/d");
      $end_date = date('Y/m/d', strtotime($start_date . " -7 days"));



      while($days7=mysql_fetch_assoc($q)): 
          $next_date = strtotime($i--." days", strtotime($start_date));
          $date = date("Y/m/d",$next_date); 
      #Let's get the latest click combined from the latest 7 days
          $combined7=mysql_query("SELECT sum(value) FROM `xeon_stats_clicks` WHERE user='".$userdata['username']."' AND typ='4' AND data='$date' ORDER BY data DESC LIMIT 8") or die(mysql_error());              


      print mysql_num_rows($combined7);

      endwhile;

I need to see how many rows that $combined7 is getting. Currently, I am using print mysql_num_rows($combined7); but that just prints out: 1 1 1 1 1 (The number '1' for each row)

How can I count the total number?

(P.S. $i have to be set to 0)

NOTICE: DO *NOT* use `mysql_\*` as it has been deprecated. Use `mysqli_\*` or PDO instead. ALSO, check out [Bobby Tables](http://bobby-tables.com/) — UnholyRanger, Sep 26 '13 at 12:23
wouldn't you only be getting one row since you're getting a `SUM` back? So wouldn't you want to add a `COUNT` to the statement, then check the second column returned for the count? — UnholyRanger, Sep 26 '13 at 12:27
can't you execute this as a single query? anyhow what is the meaning of `mysql_num_rows($combined7)` as it will be always 1 (your query will never return more than 1 row) — bansi, Sep 26 '13 at 12:29
First, please don't use mysql_ functions, they are deprecated and will be completely removed from newer versions of php. Try mysqli or PDO instead (I recommend PDO) they are safer (specially PDO) for sql injections, and more optimized. Second, I'm not sure what are you trying to achieve but I feel like you should just make "data" column a datetime type if it's not yet and query using "... WHERE ... AND data BETWEEN AND ' This way you will get all the info in one single query, instead of doing 7 queries in a loop. Maybe if you explain more I can guide you — aleation, Sep 26 '13 at 12:31
https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection — lowtechsun, Nov 11 '19 at 12:59

score 21 · Accepted Answer · answered Sep 26 '13 at 12:21

21

Simple:

$counter = 0;
while(..) {
      $counter++; // or $counter = $counter + 1;
}

echo $counter;

define the variable outside the loop.

answered Sep 26 '13 at 12:21

q0re

1,401
19
32

score 1 · Answer 2 · answered Sep 26 '13 at 12:40

Here is your original query:

          $combined7=mysql_query("SELECT sum(value) FROM `xeon_stats_clicks` WHERE user='".$userdata['username']."' AND typ='4' AND data='$date' ORDER BY data DESC LIMIT 8")

By adding the COUNT command, it will count the amount of rows that were considered in the SUM:

SELECT SUM(value), COUNT(value) FROM...

Then, when you get the MYSQL_RESULT back, you need to fetch the data:

$data = mysql_fetch_array($combined7);

This will then have the following array:

Array(
    [0] = SUM
    [1] = COUNT
)

NOTICE: mysql_* has been deprecated. Please use mysqli_* or PDO instead

Kalpit · Answer 3 · 2013-09-26T12:29:32.393

0

i didn't get your question proper.. but i think you want to count total updated row

 $sum=0;
 while(){
 $sum += mysql_num_rows($combined7); //here it will add total upadted row in $sum...
 print $sum; // if you want to print every time total
 }
 print $sum; // if you want to print only one time total

edited Sep 26 '13 at 12:29

answered Sep 26 '13 at 12:21

Kalpit

4,906
4
25
43

1

`print $sum;` should be outside the `while` loop – bansi Sep 26 '13 at 12:23

Md. Sahadat Hossain · Answer 4 · 2013-09-26T12:36:06.953

0

you should define a variable with value 0 before while. then you increment value of this variable inside the while. then you print this variable after end of while.

      $start_date = date("Y/m/d");
      $end_date = date('Y/m/d', strtotime($start_date . " -7 days"));
      $sn = 0;
      while($days7=mysql_fetch_assoc($q)): 
          $next_date = strtotime($i--." days", strtotime($start_date));
          $date = date("Y/m/d",$next_date); 
      #Let's get the latest click combined from the latest 7 days
          $combined7=mysql_query("SELECT sum(value) FROM `xeon_stats_clicks` WHERE user='".$userdata['username']."' AND typ='4' AND data='$date' ORDER BY data DESC LIMIT 8") or die(mysql_error());              


      $sn += mysql_num_rows($combined7);

      endwhile;
      print $sn;

edited Sep 26 '13 at 12:36

answered Sep 26 '13 at 12:23

Md. Sahadat Hossain

3,210
4
32
55

'Try This' is NEVER an 'answer'. Why don't you explain what you changed and why. – UnholyRanger Sep 26 '13 at 12:24
@UnholyRanger I update my answer. sorry for my bad English. Thanks for your mention. – Md. Sahadat Hossain Sep 26 '13 at 12:28
@SalaUddin Remove the `$i` not needed. – Phorce Sep 26 '13 at 12:33
@user1326876 I remove `$i` yes you are right `$i` is not needed. – Md. Sahadat Hossain Sep 26 '13 at 12:36

php - count total num rows inside while loop

4 Answers4