CODEWITHSUNDEEP
phpmysqlcodeigniter
Victoria's Secret
Victoria's Secret

Reputation: 49

How can I fetch data on codeigniter using current date?

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.

Upvotes: 0

Views: 753

Answers (4)

Victoria's Secret
Victoria's Secret

Reputation: 49

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();

}

Upvotes: 0

Martijn Gastkemper
Martijn Gastkemper

Reputation: 667

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();
}

Upvotes: 1

Dum
Dum

Reputation: 1501

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);

Upvotes: 1

PHP Geek
PHP Geek

Reputation: 4033

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();
}

Upvotes: 0

Related Questions