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

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

i tried this, but still i get empty data, i even tried it like this $this->db->where("DATE(txndate)", $sid);
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.

Collectives™ on Stack Overflow

codeigniter fetching date by comparing date in sql table not working

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related