Codeginiter JOIN query with DATE between and LIMIT not working

Question

I am trying to get the details of a persons based on the user input age (Integer). Now my query always returns null Array() whenever I execute the below code. I haven't specified the $postdata array. You can see I have used $postdata['ageto'] and $postdata['agefrom'] are used in calculating $agefrom and $ageto

        $now = new DateTime();

    //Converting _POST age to Date
    $agefrom = date("Y-m-d",strtotime($now->format("Y")-$postdata['ageto'].'-'.$now->format("m").'-'.$now->format("d")));
    $ageto = date("Y-m-d",strtotime($now->format("Y")-$postdata['agefrom'].'-'.$now->format("m").'-'.$now->format("d")));

    $this->db->select('uacc_id, uacc_username, name, dob, city, education');
    $this->db->from('user_accounts as a');
    $this->db->join('personal as b','a.uacc_id = b.pruserid','INNER');
    $this->db->join('profession as c','a.uacc_id = c.puserid','INNER');
    $this->db->join('location as d','a.uacc_id = d.luserid','INNER');
    $this->db->where('dob >= ',$agefrom);
    $this->db->where('dob <= ',$ageto);
    $this->db->limit(10, $offset);
    $query = $this->db->get();
    return $query->result();

I have suspected that my input post is not fetching the data properly. So I have replaced it with my simple query select * from... where dob >.... and it worked well. So there is no problem with _POST variables. I am not sure what I am doing wrong. Can some one help me.

I think you can check your sql command. You can look the site. http://stackoverflow.com/questions/6142099/how-to-print-sql-statement-in-codeigniter-model — lighter, Dec 11 '13 at 01:35
Thanks Lighter.. I got a clue now. Its problem with the POST DATA — vkrams, Dec 11 '13 at 02:16

score 2 · Answer 1 · answered Dec 11 '13 at 01:17

2

I think you have to specify to which table DOB belongs like a.dob. Sorry for the post but I could not comment. Hope this helps.

answered Dec 11 '13 at 01:17

Dirgh

507
4
13

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

The select field should add the table name

$this->db->select('a.uacc_id, a.uacc_username, a.name....); // example

and your where add table name too.

$this->db->where('b.dod >= ', $agefrom); // example

or you can output the sql command.

You can look here

edited May 23 '17 at 12:11

Community

1
1

answered Dec 11 '13 at 01:38

lighter

2,808
3
40
59

Adding the object name to column doesn't found difference – vkrams Dec 11 '13 at 02:15

Codeginiter JOIN query with DATE between and LIMIT not working

2 Answers2