Coding help to show the total count

[Sysadmin20]

Hi guys

So I have a table script and I'm fetching the records from a table. 

1st column is a date

2nd column is an int

There is a date search selection on top. So if I select a date range and list the records, it will filter and display the records. If I never do a date selection means it will show that months records by default  - 

else{
	$query=mysqli_query($conn, "SELECT created_at, count(*) AS total, number_click,  DATE_ADD(LAST_DAY(created_at),INTERVAL 1 DAY) as 'first_date_of_month' FROM `records`    WHERE DATE_FORMAT(created_at, '%Y-%m-%d') BETWEEN 'first_date_of_month' AND NOW() GROUP BY created_at ORDER BY created_at ASC") or die(mysqli_error());
	while($fetch=mysqli_fetch_array($query)){
?>
<tr>
	<td><?php echo $fetch['created_at']?></td>
	<td><?php echo $fetch['total']?></td>
</tr>

 

and it's working great. 

Now what I want to do is, at the bottom, I want to show the SUM of 1 column.. 

For example - 

Over here, I want to show a text Total number of records : XX

Need to replace the XX with the SUM of that 2nd column.. 

My coding is a little confusing and it's not a standard format because I'm still learning. 

I did some online research and I tried using this after the closing PHP tags defining a variable named $cont -

 

   <tfoot>
                                        <tr>
                                            <th style="text-align:right">Total number of clicks:</th>
                                           <!-- <th></th> -->

                                           <th><?php echo $cont; ?></th>

                                        </tr>
                                    </tfoot>

 

It's working great but it's working only when I do a search using the search button because $cont; variable is defined within the PHP form code. So I if refresh the page I'm getting an error "undefined variable" and I'm not sure how to make it available outside the PHP code. 

If it makes it any easier, I've uploaded the code here - https://pastebin.com/75FusdLJ

 

What I wanted to do now is, add a text after that table and show the total SUM of that 2nd column regardless of the Search button press. 

If anyone could share the modified version of the code or at least let me know which portion I should replace with which code I'd really appreciate that. 

 

Thanks.

Edited February 15, 2023 by Sysadmin20

[Barand]

I am still scratching my head wondering how the hell that query produced those results.

You don't write php in one long line so why do it with sql? Make it readable...

    $query=mysqli_query($conn, "SELECT created_at
                                     , count(*) AS total
                                     , number_click
                                     , LAST_DAY(created_at) + INTERVAL 1 DAY as first_date_of_month 
                                FROM `records`    
                                WHERE DATE_FORMAT(created_at, '%Y-%m-%d') BETWEEN 'first_date_of_month' AND NOW() 
                                GROUP BY created_at 
                                ORDER BY created_at ASC
                                ") or die(mysqli_error());

1. Your first_date_of_month column will actually contain the first date of next month so the WHERE clause (BETWEEN '2023-03-01' AND NOW() )  would not produce anything until NOW() is on or after the first of March. (When using BETWEEN A AND B, then A has to be <= B)
2. You are not allowed to use a column alias in a WHERE clause, so it shouldn't even run.

[Sysadmin20]

10 hours ago, Barand said:

I am still scratching my head wondering how the hell that query produced those results.

You don't write php in one long line so why do it with sql? Make it readable...

    $query=mysqli_query($conn, "SELECT created_at
                                     , count(*) AS total
                                     , number_click
                                     , LAST_DAY(created_at) + INTERVAL 1 DAY as first_date_of_month 
                                FROM `records`    
                                WHERE DATE_FORMAT(created_at, '%Y-%m-%d') BETWEEN 'first_date_of_month' AND NOW() 
                                GROUP BY created_at 
                                ORDER BY created_at ASC
                                ") or die(mysqli_error());

1. Your first_date_of_month column will actually contain the first date of next month so the WHERE clause (BETWEEN '2023-03-01' AND NOW() )  would not produce anything until NOW() is on or after the first of March. (When using BETWEEN A AND B, then A has to be <= B)
2. You are not allowed to use a column alias in a WHERE clause, so it shouldn't even run.

Hi @Barand

Thanks for the reply. I know it's not formatted properly because I'm just using notepad for now until I get the functionalities right.. 

You;re right. I didn't check the query properly. Modified to - 

SELECT 
  created_at, 
  count(*) AS total, 
  number_click, 
  last_day(
    curdate() - interval 1 month
  ) + interval 1 day as 'first_date_of_month' 
FROM 
  `records` 
WHERE 
  DATE_FORMAT(created_at, '%Y-%m-%d') BETWEEN 'first_date_of_month' 
  AND NOW() 
GROUP BY 
  created_at 
ORDER BY 
  created_at ASC

 

and it's showing properly now - 

 

But in any case, SQL is not an issue here. I just need to create a new footer section to show the SUM of the total column. Any ideas?

[kicken]

Just sum the results as you read them and generate your table.

$total = 0;
while($fetch=mysqli_fetch_array($query)){
   $total += $fetch['total']; 
   //...
}
//Display your final row with $total as the sum.

 

[gizmola]

I agree with kicken, but at least with MySQL there are some group by modifiers you can use with your GROUP BY queries, one of which is 'WITH ROLLUP'.

With Rollup will produce additional summary value rows for each subgrouping.  Of course to use this you need to understand how to identify these rollup rows when you fetch them.   Since you only have one GROUP by in your case, there would be one final row with the total count, which you could take advantage of if you wanted to, as an alternative to using a PHP variable.

Again, incrementing a variable in a fetch loop is a tried and true solution, but knowing about the group by modifiers is one more tool in your toolbox, which never hurts.

You do need a fairly recent version of MySQL ( >= 8.0.12) to use WITH ROLLUP along with a group ORDER BY as you have in your code.

[Barand]

FYI - ROLLUP is certainly available in version 5.7

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.36-log |
+------------+


mysql> SELECT classid as class
    ->      , COUNT(*) as students
    -> FROM student_class
    -> WHERE semesterid = 12
    -> GROUP BY classid WITH ROLLUP;
+-------+----------+
| class | students |
+-------+----------+
|     1 |       17 |
|     2 |       18 |
|     3 |       20 |
|     4 |       23 |
|     5 |       22 |
|     6 |       27 |
|     7 |       24 |
|     8 |       20 |
|     9 |       21 |
|    10 |       27 |
|    11 |       25 |
|    13 |        5 |
|    14 |        5 |
|    16 |        3 |
|    17 |        5 |
|    19 |       17 |
|  NULL |      279 |    <--- ROLLUP total
+-------+----------+

 

[Sysadmin20]

21 hours ago, kicken said:

Just sum the results as you read them and generate your table.

$total = 0;
while($fetch=mysqli_fetch_array($query)){
   $total += $fetch['total']; 
   //...
}
//Display your final row with $total as the sum.

 

Thanks. This is exactly what I found online, and it was working but only when I did a search using that search option i.e. within that buttons POST request code.

 

                  <thead>
                                        <tr>
                                            <th>Date</th>
                                            <th>Number of clicks</th>
                                        </tr>
                                    </thead>
                                    <?php

                                    //new record
                                    $cont = 0;

                                        if ($result = $conn->query($sql)) {
                                            while ($row = $result->fetch_assoc()) {
                                                echo "<tr>";
                                                    echo "<td>".$row['created_at']."</td>";
                                                    echo "<td>".$row['total']."</td>";
                                                echo "</tr>";
                                                //new record
                                                $cont += $row['total'];
                                            }
                                        }
                                    ?>
                                    <tfoot>
                                        <tr>
                                            <th style="text-align:right">Total number of clicks:</th>
                                           <!-- <th></th> -->

                                           <th><?php echo $cont; ?></th>

                                        </tr>
                                    </tfoot>
                                </table>
                            </div>

 

This worked great. But as you can see above, the variable is within the PHP tags starting like this - 

 <?php 
                    if(isset($_POST['search'])){

 

That means when I hit the search button, the total is working just fine. But when I first visit that page there are no post requests and by default I have an "else" query to show some default records like this -

else{
	$query=mysqli_query($conn, "SELECT created_at, count(*) AS total, number_click,  DATE_ADD(LAST_DAY(created_at),INTERVAL 1 DAY) as 'first_date_of_month' FROM `records`    WHERE DATE_FORMAT(created_at, '%Y-%m-%d') BETWEEN 'first_date_of_month' AND NOW() GROUP BY created_at ORDER BY created_at ASC") or die(mysqli_error());
	while($fetch=mysqli_fetch_array($query)){
?>
<tr>
	<td><?php echo $fetch['created_at']?></td>
	<td><?php echo $fetch['total']?></td>
</tr>

 

This is where I'm having the problem. When I first load the site, it's throwing an "undefined variable" error because I'm not doing any button press. 

So I removed this variable from my code file for now. If it's not a trouble, can you take a look at https://pastebin.com/75FusdLJ and let me know how exactly I should define this variable so that the total number will be displayed even without a POST request? 

Edited February 16, 2023 by Sysadmin20

[gizmola]

12 hours ago, Barand said:

FYI - ROLLUP is certainly available in version 5.7

 

Yes, it just isn't valid if you also use an ORDER BY, at least until 8.0.12.