Mysql query return empty result in Codeigniter but working fine in native PHP

Question

I can't figure out why my query always return empty result in Codeigniter. The same query is works as expected when run in SQL box in phpmyadmin.

My table (users) look something like this:

id | name        | registration
-------------------------------
 1 | John        | 2016-06-09 12:00:08
 2 | Mike        | 2016-06-12 12:45:23
 3 | Greg        | 2016-06-13 11:50:05
 4 | Anthony     | 2016-06-14 02:34:08
 5 | Adam        | 2016-06-14 04:34:18
 6 | Steven      | 2016-06-15 13:55:34
 7 | Joe         | 2016-06-16 07:23:45
 8 | David       | 2016-06-17 06:19:31
 9 | Richard     | 2016-06-17 11:20:27
10 | Jack        | 2016-06-17 12:00:08

and so on. I just want to get a list of all people whose registration date is in the last 7 days. 'registration' column is of DATETIME type

Here's my code in CI model using Query Builder. Already autoload database library

   function get_user()
   {
    $query = $this->db->select('name')
                  ->where('registration','BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()')
                  ->get('users');
    return $query->result();
   }

It's return empty result without error. Next I try to put query directly like this

    function get_user()
    {
     $query = $this->db>query('SELECT name FROM users WHERE registration BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()');
    return $query->result();       
    }

Also return empty result without error. So out of curiosity I tried to run the same query using native PHP outside Codeigniter. Here's the code :

   <?php
   $link = mysqli_connect("localhost", "myusername", "mypassword", "mydatabase");

   if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
   }

   $query = "SELECT name FROM users WHERE registration BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()";
   $result = mysqli_query($link, $query);
   while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
   echo $row["name"];
   echo "<br>";
   }

   mysqli_free_result($result);
   mysqli_close($link);
   ?>

And voila...it works and return result as expected. So any idea what's the problem with Codeigniter in this case? Any input will be appreciated. Thanks all

It looks like the problem is in the date selection. Still don't know why it's not working in CI. If I create query that not involving date it just works fine — Syaiful Amri
– Syaiful Amri, Commented Jul 2, 2016 at 4:34

dap6000 · Accepted Answer · 2016-07-01 15:46:16Z

1

I'm assuming this is CodeIgniter 3.x. Here's the relevant page in the docs.

http://www.codeigniter.com/user_guide/database/query_builder.html#looking-for-specific-data

The gist is where() doesn't behave like you want it to here. If you give it two strings it treats the first as a column name and the 2nd as a value and compares for equality. You wanna use the "custom string" variation in point #4 in the docs.

Or, since you seem comfortable writing your own SQL strings, you could also consider going that route and not using the CI query builder at all. $this->db->query() works well. And it even supports bindings with ?s in the query string and an array of value to replace them with. I don't think it supports named bindings like PDO does.

http://www.codeigniter.com/user_guide/database/queries.html

answered Jul 1, 2016 at 15:46

dap6000

364 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Syaiful Amri Over a year ago

Yes i'm using CI 3. Thanks for pointing out. I'm not aware of "custom string" describet in poin#4 in the docs. But even use that still give me empty result.

Collectives™ on Stack Overflow

Mysql query return empty result in Codeigniter but working fine in native PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related