codeigniter fetching date by comparing date in sql table not working

Question

I have simple form in CodeIgniter with a date field and a submit button. I also have an SQL table where there are few values and date column values looks like: 21-11-2022 16:19:14, 21-11-2022. 15:51:41.

As the date saved in table is like date and time and my input type date gives only date, I did the following code in my model to get all the values within a date like below:

$this->db->select('userid, roll, amount, txnnumber, bankref, txntype, txndate, authstatus, feetype');
$date1=$sid." 00:00:00";
$date2=$sid." 11:59:59";
$this->db->where('txndate <=',$date1);
$this->db->where('txndate >=',$date2);

However this doesn't give me any data. Can anyone please tell me how to fix this?

Dave · Accepted Answer · 2022-11-21 13:14:46Z

1

The poster left out a few critical bits of information regarding his form values and how things are stored in his database. His form date format is mm/dd/yyyy and his database format is dd-mm-yyyy hh:mm:ss in a varchar column.

To correctly compare the dates both the input form value and the database value needed to be converted to UNIX timestamps so they could be compared.

$this->db->select('userid, roll, amount, txnnumber, bankref, txntype, txndate, authstatus, feetype 
WHERE 
UNIX_TIMESTAMP(CONCAT(SUBSTR(txndate,7,4),SUBSTR(txndate,4,2),SUBSTR(txndate,1,2))) <= $entered_date 
OR 
UNIX_TIMESTAMP(CONCAT(SUBSTR(txndate,-4),SUBSTR(txndate,4,2),SUBSTR(txndate,1,2))) => $entered_date');

edited Nov 21, 2022 at 13:14

answered Nov 21, 2022 at 12:05

Dave

5,21717 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ZNO Over a year ago

i tried this, but still i get empty data, i even tried it like this $this->db->where("DATE(txndate)", $sid);

Dave Over a year ago

If you are trying to look for things within a certain date range then you need a single WHERE to check the date range in one shot. I modified my answer to reflect that.

ZNO Over a year ago

i tried this, but it gives me site cant be reached error

Dave Over a year ago

That sounds completely unrelated.

ZNO Over a year ago

the error came when i added this where statment

|

Collectives™ on Stack Overflow

codeigniter fetching date by comparing date in sql table not working

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related