What I have:

I have table with a unique date column.

I pull data based on a month and year from a GET parameter.

DB::table('budgets')->whereMonth('created_at', $request->get('month'))->whereYear('created_at', $request->get('year'))->get()->first();

I do a check to see if a previous month is available in table

$pm = DB::table('budgets')->whereMonth('created_at', $prev_month)->whereYear('created_at', $prev_year)->count();
if ($pm > 0) {
  $prev_month = date('m', strtotime($_GET['month']. ' - 1 month'));
  $prev_year = date('m', strtotime($_GET['month']. ' - 1 month'));
  <a href="{{ url('dashboard?month='.$prev_month.'&year='.$prev_year) }}">Previous Month</a>
}

This works great... if there is a previous month i.e. 1 month behind.

My question is:

How can I check what the nearest/next date is?

Example:

If I was already on '2021-07-01', how would I know the next 'previous' date is '2020-12-01' and visa versa?

CodePudding user response：

The solution as per @CBroe advice:

<?php 
$this_date = date('Y-m-d', strtotime($_GET['month'].'-'.$_GET['year'].'-01'));
$pm = DB::table('budgets')->where('created_at', '<', $this_date)->orderby('created_at', 'desc')->get()->first(); // Find dates less than the one searching for and order it descending order and get only the first row.

if ($pm) {
  $prev_month = date('m', strtotime($pm->created_at));
  $prev_year = date('Y', strtotime($pm->created_at)); ?>

  <a href="{{ url('dashboard?month='.$prev_month.'&year='.$prev_year) }}">Previous Month</a>

<?php } ?>

CodePudding user response：

You can check the nearest/next date this way

$next_date = DB::table('budgets')
              ->where('created_at', '<', $date)
              ->orderByDesc('created_at')
              ->first()
              ->value('created_at');