3

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;

} all records

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';
5

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.

  • i modified my code like this $select = db_select('pastpayments', 'x'); $select->fields('x'); foreach ($entry as $field => $value) { $select->condition('paiddate', array('2017/03/01', '2017/03/30'), 'BETWEEN'); } return $select->execute()->fetchAll(); } stil its showing all records :( – vijoy Apr 24 '17 at 6:48
  • The use of between will be the same as your previous version with the <= & >=. The problem must lie somewhere else in your code. Possibly your foreach loop is adding enough different conditions that it is covering all your records? I would try printing the SQL that's in your $select object so you can make sure it is what you expect. See drupal.stackexchange.com/a/52761/10729 for more information on printing the SQL string. – rooby Apr 25 '17 at 23:21
1

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;

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.