PHP SQL Query results issue with dates

Question

I am having a problem with the code below.

For some reason it displays only dates from October, November and December.

The dates have the same format... see below:

DATE            RESULT

2013-02-01      Does Not Display
2013-03-01      Does Not Display
2013-04-01      Does Not Display
2013-05-01      Does Not Display
2013-06-01      Does Not Display
2013-07-01      Does Not Display
2013-08-01      Does Not Display
2013-09-01      Does Not Display
2013-10-01      Displays OK
2013-11-01      Displays OK
2013-12-01      Displays OK

Here is the query code:

$events = array();
$query = "SELECT title, DATE_FORMAT(date,'%Y-%m-%d') AS date FROM mytable WHERE user_id = '$session_user_id' AND date LIKE '$year-$month%' AND active = 1";
$result = mysql_query($query,$db_link) or die('cannot get results!');
while($row = mysql_fetch_assoc($result)) {

$events[$row['date']][] = $row;

}

Anyone have any ideas of why this could be happening?

Let me guess... $year = 2013 and $month = 1? Think about what will be the rendered query: date LIKE '2013-1%'. — mellamokb
– mellamokb, Commented Feb 1, 2013 at 13:47
The PHP code is completely irrelevant here. What does the generated SQL code look like? — Álvaro González
– Álvaro González, Commented Feb 1, 2013 at 13:47

Augusto · Accepted Answer · 2013-02-01 13:51:41Z

2

I guess you're using month = 1. You're getting 10, 11 and 12 only, since they are the only months that start with 1.

answered Feb 1, 2013 at 13:51

Augusto

8176 silver badges18 bronze badges

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

Comments

Arnold Daniels · Accepted Answer · 2013-02-01 13:52:16Z

1

Based on @mellamokb's comment. Instead of using LIKE use BETWEEN. This also makes the query faster.

$query = "SELECT title, DATE_FORMAT(date,'%Y-%m-%d') AS date FROM mytable WHERE user_id = '$session_user_id' AND date BETWEEN '$year-$month-1' AND '" . date("Y-m-t", strtotime("$year-$month-1")) . "' AND active = 1";

answered Feb 1, 2013 at 13:52

Arnold Daniels

16.6k6 gold badges55 silver badges84 bronze badges

Collectives™ on Stack Overflow

PHP SQL Query results issue with dates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related