CODEWITHSUNDEEP
phpmysqlcodeigniter
Syaiful Amri
Syaiful Amri

Reputation: 35

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

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

Upvotes: 2

Views: 2182

Answers (1)

dap6000
dap6000

Reputation: 36

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

Upvotes: 1

Related Questions