Error no 1054 unknown column billdate in where clause

Question

I Tried raw query in a controller(codeigniter) and got a error of unknown column $newdate in where clause ($newDate and $ newDate2 are the two variables that is i got input from the user and save it in a variable $newDate and $newDate2)..Here is my code

Controller code:

$startdate = $this->input->post('SDate');
    $enddate = $this->input->post('EDate');
    $date = str_replace('/', '-', $startdate);
    $newDate = date("Y-m-d", strtotime($date));
    $date2 = str_replace('/', '-', $enddate);
    $newDate2 = date("Y-m-d", strtotime($date2));
    $data['startdate'] = $startdate;
    $data['enddate'] = $enddate;
    $query=$this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE `billdate` >= `$newDate2`AND `billdate` <= `$newDate` ORDER by `vno`')->result_array();
        $data['query'] = $query;

Error screenshot

Try ' instead of ` `billdate` >= '$newDate2' AND `billdate` <='$newDate' — JIJOMON K.A, Nov 20 '18 at 05:21
Also don't use variables in single quotes, they won't be variables. https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php — user3783243, Nov 20 '18 at 05:24
@JIJOMONK.A tried getting Parse error: syntax error, unexpected '$newDate2' error — dhara, Nov 20 '18 at 05:25
@dhara Please update the question with any changes you've made. — user3783243, Nov 20 '18 at 05:25

score 2 · Accepted Answer · answered Nov 20 '18 at 05:34

Please Try this code,

I change some quotes

$query=$this->db->query("SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE `billdate` >= '$newDate2' AND `billdate` <= '$newDate' ORDER by `vno`")->result_array();

or

$query=$this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE `billdate` >= "$newDate2" AND `billdate` <= "$newDate" ORDER by `vno`')->result_array();

score 0 · Answer 2 · edited Nov 20 '18 at 05:36

0

You are using $newDate a php Date Variable as column in your Query. Just Change the Query.

$query=$this->db->query('SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE `billdate` >= '.$newDate2.' AND `billdate` <= '.$newDate.' ORDER by `vno`')->result_array();

OR:

$query=$this->db->query("SELECT `vno`,`Prdtname`,`Qty`,`bundle` FROM purchaseitem WHERE `billdate` >= $newDate2 AND `billdate` <= $newDate ORDER by `vno`")->result_array();

edited Nov 20 '18 at 05:36

user3783243

5,368
5
22
41

answered Nov 20 '18 at 05:29

Prem Sarojanand

178
12

1

`$newDate` is a string, not an integer. – user3783243 Nov 20 '18 at 05:33

Error no 1054 unknown column billdate in where clause

2 Answers2