MySQL 'Between' dates not selecting the correct results using PHP (and Codeigniter)

Question

Here is my code (using CodeIgniter):

$now = date('Y-m-d');

$then = strtotime($now . '-1 week');

$then = date('Y-m-d', $then);

$q3 = $this->db->query("SELECT * 
                          FROM posts 
                         WHERE publish_date BETWEEN '$then' AND '$now'");

$data['posts_today'] = $q3->num_rows();

I clearly have posted at least twenty posts this week, but it only displays '1'. Any ideas why?

Thanks!

Answer 1

Does this work for you:

$q3 = $this->db->query("SELECT * 
                          FROM posts 
                         WHERE publish_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) 
                                                AND NOW()");

Potential issues I see:

• is POSTS.publish_date a DATETIME data type?
• if PHP and MySQL are on different hosts, NOW from codeigniter/PHP could be a different time than what's being stored/used in MySQL.

Answer 2

Verify that MySQL expects dates in that format. You might be better off using MySQL's date functions to compose them.

Answer 3

First, offload everything you can to MySQL. The less date math you have to do the better.

Second, Look at what your actual created code looks like. Change this

$q3 = $this->db->query("SELECT * 
                      FROM posts 
                     WHERE publish_date BETWEEN '$then' AND '$now'");

to this

$sql = "SELECT * 
                      FROM posts 
                     WHERE publish_date BETWEEN '$then' AND '$now'");
print "EXECUTING: $sql\n";
$q3 = $this->db->query( $sql );

You're assuming that the SQL you are generating is valid, and it might not be. This is very common when you're using one language to generate code in another. Always print to verify your assumptions.