How can I fetch data on codeigniter using current date?

Question

This is my first question around here so be nice :)

I am trying to display data using Codeigniter and MySQL based on the current date.

Down below, there's my function from the Model.

When I use this format it works:

public function get_tables(){
    $this->db->where('date','2020-04-07');
    $this->db->order_by('time', 'ASC');  
    $query=$this->db->get('tables');
    return $query->result();
}

But when I try to use the CURDATE() function, it shows me an error on this line.

public function get_tables(){
    $this->db->where(date_format('date',"YYYY-MM-DD"),'CURDATE()', TRUE);
    $this->db->order_by('time', 'ASC');  
    $query=$this->db->get('tables');
    return $query->result();
}

I'll be glad to hear why isn't it working, any suggestions to improve it will be welcomed.

Does this answer your question? [How to get current date in codeigniter](https://stackoverflow.com/questions/15830364/how-to-get-current-date-in-codeigniter) — nbk, Apr 07 '20 at 20:55

Martijn Gastkemper · Answer 1 · 2020-04-08T20:01:36.007

Your date_format call is wrong. The first parameter must be an actual date. Not the string "date". I think you're mixing PHP and MySQL.

By using date_format as second parameter you create a string of the current date with PHP and give it to MySQL.

public function get_tables(){
    $this->db->where('date', date_format(new DateTime(), 'YYYY-MM-DD'));
    $this->db->order_by('time', 'ASC');  
    $query=$this->db->get('tables');

    return $query->result();
}

~~CURDATE() is a MySQL function and I don't think it's possible to give raw MySQL to where().~~

edit: Dum answered this question. It's possible to use CURDATE() with Codeigniter:

public function get_tables(){
   $this->db->where('date', `CURDATE()`, false);
   $this->db->order_by('time', 'ASC');  
   $query=$this->db->get('tables');

   return $query->result();
}

score 1 · Answer 2 · answered Apr 08 '20 at 05:48

From Codeigniter documentation

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names.

So, You should use

$this->db->where(date_format(new DateTime(),"YYYY-MM-DD"),'CURDATE()', FALSE);

score 0 · Answer 3 · answered Apr 08 '20 at 04:30

0

use date() funtion

public function get_tables(){
$date=date("Y-m-d H:i:s");
    $this->db->where(date_format,$date);
    $this->db->order_by('time', 'ASC');  
    $query=$this->db->get('tables');
    return $query->result();
}

answered Apr 08 '20 at 04:30

PHP Geek

3,949
1
16
32

score 0 · Answer 4 · edited Aug 09 '23 at 12:58

Thank you, everyone, I combined all of your answers with this answer:

how curdate function could return date in mm/dd/yyyy format php

and it worked!!

Posting the last version of my Model function for displaying data from DB based on the current date:

public function get_tables()
{

 $this->db->where('date =','DATE(CURDATE())',FALSE);
 $this->db->order_by('time', 'ASC');  
 $query=$this->db->get('tables');
 return $query->result_array();

}

How can I fetch data on codeigniter using current date?

4 Answers4