php and mysql display daily income between date_begin and date_end

Question

I have a table which looks kind of like this:

$table_name = 'transactions';

+---------------------+--------+
| date                | amount |
+---------------------+--------+
| 2019-05-01 03:11:34 | 5      |
| 2019-05-01 04:26:26 | 10     |
| 2019-05-01 07:27:34 | 10     |
| 2019-05-01 19:22:47 | 20     |
| 2019-05-02 00:42:10 | 5      |
| 2019-05-02 04:27:31 | 10     |
| 2019-05-02 05:14:03 | 5      |
| 2019-05-02 08:39:24 | 5      |
| 2019-05-03 12:45:29 | 15     |
| 2019-05-03 12:48:23 | 15     |
| 2019-05-03 13:50:14 | 5      |
| 2019-05-03 16:58:08 | 20     |
| 2019-05-04 01:27:28 | 10     |
| 2019-05-04 11:53:15 | 20     |
| 2019-05-04 15:48:51 | 5      |
| 2019-05-04 15:59:48 | 5      |
| 2019-05-05 01:55:40 | 10     |
| 2019-05-05 05:32:53 | 15     |
| 2019-05-05 11:56:56 | 15     |
| 2019-05-05 12:09:26 | 10     |
+---------------------+--------+

For this example the table has exactly 4 records per day. But the real table can have more or less per day of course.

What I need is a list of all days between a certain time span, and the sum of amount i.e. income at each day.

Example:

Input:

$date_begin = '2019-05-02 00:00:00';
$date_end = '2019-05-04 23:59:59';

For these parameters the script should output the following:

Secondly (not so important. Please focus on the first task): It would be helpful to display the name of the week day of each day. I guess I would have to use a library for that. Or is php natively able to determine the day-name of past dates?

So, it would look like this:

2019-05-02
Thursday
25

2019-05-03
Friday
55

2019-05-04
Saturday
40

I really tried to solve it for myself first and was searching for examples. But each I found was either more complicated than what I needed, or too different for me to be able to adopt it properly.

Thanks

EDIT:

I tried one of the suggestions in the replies. But all I get is a blank page. Can anyone tell me where I am wrong?

<?php

$conn = mysqli_connect($dbserver, $dbuser, $dbpw, $dbname) or die("Connection failed: " . mysqli_connect_error());
$conn->query("SET NAMES 'utf8'");
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }  

$table_name = 'transactions';

$date_begin = '2019-05-02 00:00:00';
$date_end = '2019-05-05 23:59:59';

$sql = "SELECT DATE(`date`), SUM(`amount`) FROM $table_name WHERE `date` BETWEEN '$date_begin' AND '$date_end' GROUP BY DATE(`date`) ORDER BY `date`";

$rs_result = mysqli_query($conn, $sql);

?>

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Income daily</title>
<meta name="robots" content="noindex, nofollow">
</head>

<body>

<?php

while ($row = mysqli_fetch_assoc($rs_result)) {

echo '
'.$row['date'].'

<br>

'.$row['amount'].'

<br><br>

';

}

?>


</body>
</html>

Please don't worry about the security for now. I will take care of that afterwards.

i believe you are looking for a simple query like `SELECT DATE(date), SUM(amount)... WHERE date BETWEEN '2019-05-02 00:00:00' AND '2019-05-04 23:59:59' GROUP BY DATE(date) ORDER BY DATE(date)`.. if not see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) as a image isn't a good source data. — Raymond Nijland, Jun 21 '19 at 15:54

AbraCadaver · Answer 1 · 2019-06-21T17:09:47.433

0

The query reads kind of like how you would describe what you want. You haven't shown any code or specified a MySQL API so see here for safe ways of querying How can I prevent SQL injection in PHP?:

SELECT DATE(`date`), SUM(`amount`) FROM $table_name
    WHERE `date` BETWEEN '$date_begin' AND '$date_end'
    GROUP BY DATE(`date`) ORDER BY `date`

Then when fetching rows and outputting just use date('l') to get the textual representation of the day.

edited Jun 21 '19 at 17:09

answered Jun 21 '19 at 15:54

AbraCadaver

78,200
7
66
87

This will not result into the correct amounts as `GROUP BY DATE(date)` should be used as the topicstarter is looking for daily amounts and the date column is a DATETIME type. – Raymond Nijland Jun 21 '19 at 15:56
And... why not? – AbraCadaver Jun 21 '19 at 15:58
1

I see now I think. Thanks. – AbraCadaver Jun 21 '19 at 16:01
1

No problem also `ORDER BY DATE(date)` .. And i also should mention the SQL injection which is possible in your code example.. – Raymond Nijland Jun 21 '19 at 16:04
@Raymond Nijland Thanks for clarifying. Do you have an alternative suggestion? – DanielM Jun 21 '19 at 16:30
Could you please either abstract from PHP or replace the SQL injection with parameters? You might think you are interpolating dates, but OP might change this to `$_POST` and think that it is acceptable. Let's not insinuate to anyone that it is ok to put variable data into SQL like this. – Dharman Jun 21 '19 at 16:57

score 0 · Answer 2 · 2019-06-22T05:12:44.527

0

First Run following code in SQL: Problem was your start and end date. You assign month 06 but db contains dates with month 05.

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Now Put This Code in your file:

<?php
    $conn = mysqli_connect($dbserver, $dbuser, $dbpw, $dbname) or die("Connection failed: " . mysqli_connect_error());
    $conn->query("SET NAMES 'utf8'");
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }  

    $table_name = 'transactions';

    $date_begin = '2019-05-01 00:00:00';
    $date_end = '2019-05-02 23:59:59';

    $sql = "SELECT DATE(`date`) as date, SUM(`amount`) as amount FROM transaction WHERE (date > '$date_begin' AND date < '$date_end') GROUP BY `date`; ";


    $rs_result = mysqli_query($conn, $sql);

    ?>

    <!doctype html>
    <html>
    <head>
    <meta charset="utf-8">
    <title>Income daily</title>
    <meta name="robots" content="noindex, nofollow">
    </head>

    <body>

    <?php

    while ($row = mysqli_fetch_assoc($rs_result)) {

    echo ''.$row['date']."<br>".date("l", strtotime($row['date'])).'<br>'.$row['amount'].'<br><br>';

    }

    ?>


    </body>
    </html>

edited Jun 22 '19 at 05:12

answered Jun 21 '19 at 19:59

I changed the dates in my post now, but my live table actually contains data in this time period. It is bigger than the example table in my post, so this wasn't the problem. Also: When trying to execute your first line in SQL in phpMyAdmin I get this error: "#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation" What is your line even supposed to do? Thx – DanielM Jun 21 '19 at 23:57
Though, after correcting your misspelling of "transactions" in your $sql i.e. replacing it with $table_name, your script actually gave me an output. But not the one which is needed. It simply lists all individual records/transactions, even multiple per day. But I need only one result per day which sums all amounts for each day. – DanielM Jun 22 '19 at 00:11
This solution actually worked, but only after I renamed the new "date" to something else. Like $sql = "SELECT DATE(`date`) as date_new, SUM(`settle_amount`) as income FROM $table_name WHERE (date > '$date_begin' AND date < '$date_end') GROUP BY `date_new`; "; – DanielM Jun 22 '19 at 19:21
If you correct that in your post, I'll mark it as solved. – DanielM Jun 22 '19 at 19:22

score 0 · Answer 3 · answered Dec 01 '22 at 00:01

0

Please try this

 $db = mysqli_connect($dbserver, $dbuser, $dbpw, $dbname);

 $date_begin = '2019-05-02 00:00:00';
 $date_end = '2019-05-05 23:59:59';
 
 mysqli_query($db, "SELECT SUM(amount) AS totaltransactions FROM 
 transactions WHERE date  BETWEEN 
'$date_begin' AND '$date_end'");

 $row = mysqli_fetch_assoc($result);

 echo  $sumTransactions = $row['totaltransactions'];

answered Dec 01 '22 at 00:01

UMESKIA SOFTWARES

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 03 '22 at 14:46

php and mysql display daily income between date_begin and date_end

3 Answers3