Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Server-side Scripting _ Check if Database row is posted before the 1st (month start)

Posted by: sanoj96 Sep 15 2019, 02:46 PM

Hello,

So i am currently working on a little "Donation" goal script in PHP. Donations is recived via Paypal, and posted to a database with some info, Message, Amount donated, Payment date, etc

I have managed to sort out the donations made before the month start
ex:(
2019-09-08 15:20:52 payment_date
25.00$ mc_gross
2019-10-01 23:59:59 - FirstNextMonth
)
This is the PHP i have for that

CODE

                //DataBase Stuff
        /*This is something i used for a test */ $donated = $db_conn->query("SELECT sum(mc_gross) FROM payment") or die($db_conn->error);

        $fetchPayments = $db_conn->query("SELECT * FROM payment") or die($db_conn->error);

                //Date and Time stuff

        $theFirst = new DateTime('first day of next month');
        $firstLast = new DateTime('first day of this month');
        $first = $theFirst->format("Y-m-d 23:59:59");
        $firstThis = $firstLast->format("Y-m-d 00:00:01");

                // The code and stuff

        while($row = mysqli_fetch_array($fetchPayments))
        {
            $pdate = $row["payment_date"];
            if($firstThis <= $pdate) {
                //For Testing Checking if correct months are displayed on the list.
                echo $row["payment_date"]. " payment_date<br>";
                echo $row["mc_gross"]. "$ mc_gross<br>";
                echo $first . " - FirstNextMonth<br><br>";
                echo $row["sum(mc_gross)"];
            }
        }

So my problem is, that when i try to get the total amount of the donations this month, it displayes the total amount donated overall.
is there a quick and easy way to sort them out and only display donations made this month, and then start on new each month on the 1st ?
I have tried to use

CODE

$row["sum(mc_gross"];

to get the sum of the donation. but that only gives me a 0.
have tried to do a few other things to with out any luck.

Any suggestions will be helpfull!

Thanks in advanced.

Posted by: CharlesEF Sep 15 2019, 03:43 PM

Why not limit the database results? What I mean is, use the WHERE clause, to return only the dates you want. If you want a range of dates then do, WHERE date_column >= $start_date AND date _column <= $end_date. This way you only get the dates you want to sum.

Otherwise you will have to add date logic to your PHP script (to only process the dates you want).

Posted by: sanoj96 Sep 16 2019, 09:09 AM

Thanks! Seems like this did help!

Posted by: CharlesEF Sep 16 2019, 01:51 PM

Glad it helped. Your SELECT SUM() should work also. COUNT only counts the records. This way MySQL does all the math