check if two dates are equal in codeigniter with Active Record

Question

I want to check if two dates are equal, its $query_array['datepicker'] and absensi_tanggal. They have the different format, so i CONVERT the absensi_tanggal.

Do SQL automatically convert the date? i tried not to convert it, but the query is not working, it always return 0 row.

So i tried this code, but its not working.

$q = $this->db->select('*', 'CONVERT(VARCHAR(10), absensi_tanggal, 101) AS temp')->from('msabsensi')->limit($limit, $offset)
             ->join('msumat', 'msabsensi.umat_id = msumat.umat_id')
             ->join('mskelas', 'mskelas.kelas_id = msumat.kelas_id');

$q->where('CONVERT(VARCHAR(10), absensi_tanggal, 101)', $query_array['datepicker']);
$result['rows'] = $q->get()->result();

This is the error i got (i think the CONVERT AS that i did in select() is not working) :

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 'VARCHAR(10), absensi_tanggal, 101) '04/01/2013' LIMIT 10' at line 5

SELECT * FROM (`msabsensi`) JOIN `msumat` ON `msabsensi`.`umat_id` = `msumat`.`umat_id` JOIN `mskelas` ON `mskelas`.`kelas_id` = `msumat`.`kelas_id` WHERE CONVERT(VARCHAR(10), absensi_tanggal, 101) '04/01/2013' LIMIT 10

Filename: C:\xampp\htdocs\ci_gabdb\system\database\DB_driver.php

Line Number: 330

Any help is appreciated, Thanks :D

Possible duplicate of: [Using column alias in WHERE clause of MySQL query produces an error](http://stackoverflow.com/questions/942571/using-column-alias-in-where-clause-of-mysql-query-produces-an-error) — hakre, May 11 '13 at 07:24

score 1 · Accepted Answer · edited May 23 '17 at 10:32

1

The problem is that temp is not used as alias of any column in this query and you are referring it in your where clause

$this->db->select('*')
->from('msabsensi')->limit($limit, $offset)
->join('msumat', 'msabsensi.umat_id = msumat.umat_id')
->join('mskelas', 'mskelas.kelas_id = msumat.kelas_id')
//->where('temp = '.$query_array['datepicker']) // here
->where('absensi_tanggal = DATE_FORMAT( STR_TO_DATE(  "' .$query_array['datepicker'] . '",  \'%m/%d/%Y\' ) ,  \'%Y-%m-%d\' )') 
->get()->num_rows();

Edit

Check Problem with alias

Using column alias in WHERE clause of MySQL query produces an error

edited May 23 '17 at 10:32

Community

1
1

answered May 11 '13 at 04:53

Yogesh Suthar

30,424
18
72
100

Sorry, i made a mistake in copying the code. Please see my updated question. Your solution is not working, it always return 0 rows. Thanks for your help – Blaze Tama May 11 '13 at 05:12
@BlazeTama Sorry for late reply. I just read mysql documentation that we can't use alias in where clause. See edited answer. – Yogesh Suthar May 11 '13 at 05:21
@BlazeTama Try edited query.May it will solve your problem. – Yogesh Suthar May 11 '13 at 05:24
Yes, i forgot about that part :D Thanks for your help, but its still not working..now it seems i have some mistake with the SELECT statement. Please see my updated question, i updated the code. Thanks – Blaze Tama May 11 '13 at 05:52
I also updated the error, its a different error – Blaze Tama May 11 '13 at 05:54
@BlazeTama Can you give me what kind of data present in `absensi_tanggal` column and `$query_array['datepicker']` with their date format? – Yogesh Suthar May 11 '13 at 06:01
@BlazeTama See edited query. And also you forgot `=` in where clause. – Yogesh Suthar May 11 '13 at 06:09
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/29765/discussion-between-blaze-tama-and-yogesh-suthar) – Blaze Tama May 11 '13 at 06:20
Thank you very much for your huge help, its too bad i can't give more than 1 upvote – Blaze Tama May 11 '13 at 06:53

check if two dates are equal in codeigniter with Active Record

1 Answers1