Counting database for previous month across the new year roll over PHP SQL

1 Views

I have had a custom wordpress plugin that runs a report for the database entries from the previous few months, and it worked all year, but now that the new year has happened, it shows no results because it is counting back a few months but then keeping it for 2016.

For example, I load the page on Jan 1st 2016, and it shows the results for December 2016 instead of 2015 when I am using the following method for getting last month’s data.

Read More
$today = date("Y-m-d H:i:s");   
$month_current_start = date("Y-m-")."1 0:0:0";
$currentmonth = date("m");
$lastmonthnum = $currentmonth - 1;
$last_month_start = date("Y-").$lastmonthnum."-1 0:0:0";
$last_month_end = date("Y-").$lastmonthnum."-31 0:0:0";

so then I have an SQL that says something like 

$var = $wpdb->get_var( "SELECT Count(*) FROM `table` WHERE table.timestamp BETWEEN '$last_month_start' AND '$last_month_end' AND table.amount = 1200" );

Any tips on how to fix this query so that it knows that last month is 2015?

I’m no professional so I’d love beginner-style help 🙂

I can make it work manually but i’d like the code to work for the next few months automatically (since the actual plugin calculates the last four months).

Post Views: 1

Related posts

2 comments

  1. Easiest way would be to convert last month to a time stamp, and use that for creating the format you have in your DB. An example based on what you have so far would be:

    $today = date("Y-m-d H:i:s");
$month_current_start = date("Y-m-")."1 0:0:0";
$ltime = strtotime("-1 month");
$last_month_start = date("Y-m",$ltime)."-1 0:0:0";
$last_month_end = date("Y-m",$ltime)."-31 0:0:0";

    What this does, is it makes a timestamp for 1 month ago from when it is called in the line $ltime = strtotime("-1 month"); From there, it makes the format for the first day and last day of the month (assuming there are always 31 days of course ^^) based on the timestamp, which you’ll use to provide the year and the month.

    PHP Sandbox with the code example and it’s output if you’d want to play around with how strtotime works.

  2. In mysql you have the option to use year_month, see this for reference.

    1. Get the current year and month using your code
    2. update your query accordingly

    or you can have your sql server do all the work for you by using the date_add function

    Update your question with the db server you are using, then I can write the new query for you.

Comments are closed.