phpmysqldatecodeigniterquery-builder

SELECT record for a specific user with a datetime value falling on today's date using CodeIgniter's query builder


I have a field in my db table as creater_date as datetime and having values stored in the form of 2013-09-13 02:12:44

Now I have to compare today-date(no time) with creater_date table field .

I tried this below code but it is showing error:

 function check_existing_User_weightStatus($u_id)
 {
    $this->load->database();
    $this->load->helper('date');  
    $today = date('Y-m-d');
    $array = array(
        'creater_id' => $u_id,
        DATE('creater_date') => $today
    );
    $this->db->where($array); 
    $query = $this->db->get('user_weight');
    if ($query->num_rows() > 0) {
        return true;
    } else {
        return false;
    } 
 }

Getting error :

 A Database Error Occurred
 
 Error Number: 1064
 
 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13 Sep 2013 02:48:27 +0530Asia/Calcuttaam30Asia/Calcutta13_13am30Asia/Calcutta '' at line 4
 
 SELECT * FROM (`user_weight`) WHERE `creater_id` = '3235' AND `2013-09-13T02:48:27+05:30Fri,` 13 Sep 2013 02:48:27 +0530Asia/Calcuttaam30Asia/Calcutta13_13am30Asia/Calcutta '2013-09-13'
 
 Filename: D:\xampp\htdocs\webapp\system\database\DB_driver.php
 
 Line Number: 330

I do not want to compare with the time, I only want to compare with today's date. The field creater_date (eg. 2013-09-13 02:12:44) is a datetime datatype, but only the date part is relevant to my query.


Solution

  • The problem is that your code generates a faulty SQL syntax, like the error shows.

    I'm not an expert with codeIgniter, but here's how to do a normal query directly, that's probably what you want to do:

    function check_existing_User_weightStatus($u_id)
     {
        $today = date('Y-m-d');
        $this->load->database();
        $query = $this->db->query("SELECT * FROM `user_weight` WHERE `creater_id` = '$u_id' AND DATE(`created_date`) = '$today'");
    
        if ($query->num_rows() > 0) {
            return true;
        } else {
            return false;
        } 
     }
    

    The error in your code is occurring at this line

    $array = array('creater_id' => $u_id,DATE('created_date') => $today);
    

    I'm pretty sure this is not how the where clause will be done, so you might lookup the codeIgniter docs ! to find the right way to do that ! (You're not telling the where clause to use AND, OR, etc.. operators)