How to use database BETWEEN query?

Question

I need to fetch the database records for particular date using database query , i tried some queries to fetch but it showing all records in table .

controller.php

public static function load($entry = array()){
$maxDate = \Drupal::request()->query->get('set_start_date');
$minDate = \Drupal::request()->query->get('set_end_date');
 $select = db_select('pastpayments', 'x');
 $select->fields('x');
 foreach ($entry as $field => $value) {
 $select->condition('paiddate', $maxDate, "<=");
 $select->condition('paiddate', $minDate, ">=");
}
 return $select->execute()->fetchAll();
}  

public function entryList() {
$content = array();

$content['message'] = array(
  '#markup' => $this->t('Generate a list of all entries in the database. There is no filter in the query.'),
);

$rows = array();
$headers = array(t('Member Id'), t('Name'), t('Paid Date'), t('Paid Date'));

foreach ($entries = $this->load() as $entry) {
  // Sanitize each entry.
  $rows[] = array_map('Drupal\Component\Utility\SafeMarkup::checkPlain', (array) $entry);
}
$content['table'] = array(
  '#type' => 'table',
  '#header' => $headers,
  '#rows' => $rows,
  '#empty' => t('No entries available.'),
);
// Don't cache this page.
$content['#cache']['max-age'] = 0;

return $content;

}

how i can fetch the table records for particular date ranges

database query

mysql> select * from pastpayments where paiddate >='2017/03/01' and paiddate <='2017/03/30';

Answer 1

You can use BETWEEN like this:

$query->condition('myfield', array(5, 10), 'BETWEEN');

In your case:

$query->condition('paiddate', array('2017/03/01', '2017/03/30'), 'BETWEEN');

For more information see the Conditional Clauses documentation.

It depends on how your dates are stored as to what min and max values you should pass in.

For example it could be a unix timestamp or it could be an actual date. I would recommend doing a more generic select on the data to find out the format of the dates and then replicate that format in the values that you are passing in.

Answer 2

My answer

public static function load($entry = array()) {
  $maxDate = \Drupal::request()->query->get('set_start_date');
  $minDate = \Drupal::request()->query->get('set_end_date');

 $select = db_select('pastpayments', 'x');
 $select->fields('x');
 $select->condition('paiddate', array($maxDate, $minDate), 'BETWEEN');
     $ans=$select->execute()->fetchAll();
     return $ans;

}