Check if Database row is posted before the 1st (month start), Check if Database row is posted before the 1st (month start) and do ma |
Check if Database row is posted before the 1st (month start), Check if Database row is posted before the 1st (month start) and do ma |
sanoj96 |
Sep 15 2019, 02:46 PM
Post
#1
|
Advanced Member Group: Members Posts: 118 Joined: 18-September 12 Member No.: 17,803 |
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. |
CharlesEF |
Sep 15 2019, 03:43 PM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
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). This post has been edited by CharlesEF: Sep 15 2019, 03:44 PM |
sanoj96 |
Sep 16 2019, 09:09 AM
Post
#3
|
Advanced Member Group: Members Posts: 118 Joined: 18-September 12 Member No.: 17,803 |
Thanks! Seems like this did help!
|
CharlesEF |
Sep 16 2019, 01:51 PM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,981 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Glad it helped. Your SELECT SUM() should work also. COUNT only counts the records. This way MySQL does all the math
This post has been edited by CharlesEF: Sep 16 2019, 02:01 PM |
Lo-Fi Version | Time is now: 25th April 2024 - 01:35 AM |