codeigniter fetching date by comparing date in sql table not working-CodePudding

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?

CodePudding user response：

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