Solved

# Get next month from records

Posted on 2010-09-14
287 Views
I have this SQL statement and it pulls all future events. I need to get dates in this month, next month, and the next month in three seperate queries.

How would I adjust this to only show this month?
How would I adjust to only show next month?
How would I adjust to show 2 months from now?

\$currentdate = date('Y-m-d');

\$event_query = "SELECT * FROM php_event_events WHERE dt >= '\$currentdate'";
0
Question by:katlees
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 4

Expert Comment

ID: 33672735

``````<?php

// Next month:
\$nextmonth = date("Y-m-d",strtotime("+1 months"));

// The month after:
\$nextmonth = date("Y-m-d",strtotime("+2 months"));
?>
``````
0

LVL 2

Expert Comment

ID: 33672752
The following should do the trick:
``````\$thisMonth = date("Y-m") . "-01";

\$query = "SELECT * FROM php_events
WHERE now() > date('\$thisMonth')
AND now() < date('\$thisMonth') + INTERVAL '1' MONTH";

\$query = "SELECT * FROM php_events
WHERE now() > date('\$thisMonth') + INTERVAL '1' MONTH
AND now() < date('\$thisMonth') + INTERVAL '2' MONTH";

// and of course for 2 months ahead:

\$query = "SELECT * FROM php_events
WHERE now() > date('\$thisMonth') + INTERVAL '2' MONTH
AND now() < date('\$thisMonth') + INTERVAL '3' MONTH";

// or, if you wanted to follow the DRY principal, put it in a function:

\$query = "SELECT * FROM php_events
WHERE now() > date('\$thisMonth') + INTERVAL '\$monthsAhead' MONTH
AND now() < date('\$thisMonth') + INTERVAL '" . \$monthsAhead + 1 . "' MONTH";
return \$query;
}
``````
0

LVL 3

Expert Comment

ID: 33672836
// Set up date variables
\$currentdate = date('Y-m-d');
\$nextmonthdate = date('Y-m-d',strtotime('+1 months'));
\$next2monthsdate = date('Y-m-d',strtotime('+2 months'));
\$next3monthsdate = date('Y-m-d',strtotime('+3 months'));

// 1. Only this month
\$event_query = "SELECT * FROM php_event_events WHERE dt >= '\$currentdate' AND dt < '\$nextmonthdate'";

// Only next month
\$event_query = "SELECT * FROM php_event_events WHERE dt >= '\$nextmonthdate' AND dt < '\$next2monthsdate'";

// 2 months from now
\$event_query = "SELECT * FROM php_event_events WHERE dt >= '\$next2monthsdate' AND dt < '\$next3monthsdate'";
0

Author Comment

ID: 33672921
wuff.. yours was the best one.. the other two didn't work. Only thing is....

For Only this month, it is showing the next 30 days.. I need just September Events
For NExt month, I need just October Events
For the next month, just Novement events

When October comes, it would then show October, November, December..
0

LVL 2

Expert Comment

ID: 33673685
Are you sure you used the code I pasted in? I tested it and it worked perfectly on my system. How did you integrate it into your code?
0

Author Comment

ID: 33674104
regality.. i did this..

\$currentdate = date('Y-m-d');
\$thisMonth = date("Y-m") . "-01";

\$query = "SELECT * FROM php_events
WHERE now() > date('\$thisMonth')
AND now() < date('\$thisMonth') + INTERVAL '1' MONTH";

I get an sql error
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/bhharley/public_html/calendarnew.php on line 165

Line 165 is if (mysql_num_rows(\$event_query_result) > 0)

My question is where do you see what field is between those dates? the dt field holds the dates of the events.
0

LVL 2

Expert Comment

ID: 33674411
Sorry, that was my bad. I used a shorter table name, and left in the now()s that I used for testing. Your code should look like this:
``````\$query = "SELECT * FROM php_event_events
WHERE dt > date('\$thisMonth')
AND dt < date('\$thisMonth') + INTERVAL '1' MONTH";
``````
0

LVL 2

Expert Comment

ID: 33674422
Oh crap, I hate when you realize a mistake 2 seconds after posting. The greater than should be greater than or equals:
``````\$query = "SELECT * FROM php_event_events
WHERE dt >= date('\$thisMonth')
AND dt <  date('\$thisMonth') + INTERVAL '1' MONTH";
``````
0

Author Comment

ID: 33676999
regality... will that only show september months in the current month one?
0

LVL 2

Expert Comment

ID: 33677065
Yes, that will show everything from september 1 - september 30
To show months further ahead than that just change the interval to higher, or as I recommened earlier, just put it in a function:
``````\$event_query = getQuery(0);

// Pass in 0 for the current month
// Pass in 1 for the next month, etc.
\$thisMonth = date('Y-m') . "-01";
\$query = "SELECT * FROM php_event_events
WHERE dt >= date('\$thisMonth') + INTERVAL '\$monthsAhead' MONTH
AND dt <  date('\$thisMonth') + INTERVAL '" . \$monthsAhead + 1 . "' MONTH";
return \$query;
}
``````
0

LVL 2

Expert Comment

ID: 33677243
Sorry again, I apologize but I made a slight error in that function. It needs paranthesis around the plus op in order to work:
``````function getQuery(\$monthsAhead) {
\$thisMonth = date('Y-m') . "-01";
\$query = "SELECT * FROM php_event_events
WHERE dt >= date('\$thisMonth') + INTERVAL '\$monthsAhead' MONTH
AND dt <  date('\$thisMonth') + INTERVAL '" . (\$monthsAhead + 1) . "' MONTH";
return \$query;
}
``````
0

Author Comment

ID: 33681816
I get this error

mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/bhharley/public_html/calendar.php on line 169

The whole code is listed below with your stuff included.

Line 69 on the error is if (mysql_num_rows(\$event_query_result) > 0)
``````<?php

\$currentdate = date('Y-m-d');
\$thisMonth = date('Y-m') . "-01";
\$query = "SELECT * FROM php_event_events
WHERE dt >= date('\$thisMonth') + INTERVAL '\$monthsAhead' MONTH
AND dt <  date('\$thisMonth') + INTERVAL '" . (\$monthsAhead + 1) . "' MONTH";
return \$query;
}

\$event_query_result = mysql_query(\$query);

if (mysql_num_rows(\$event_query_result) > 0)

{

while (\$event_row = mysql_fetch_assoc(\$event_query_result))

{

\$event_id = \$event_row['ID'];

\$event = \$event_row['title'];
\$description = \$event_row['description'];
\$event_time = \$event_row['startTime'];
\$event_date = \$event_row['dt'];

\$event_time = \$event_row['startTime'];

\$event_time1 = mysql2timestamp1(\$event_date);

\$event_display_date = date("D, F j, o", \$event_time1);

echo "<div class=\"event_name\">\$event_display_date</div>\n";

echo "<div class=\"event_desc\">\$event</div> <P>\n";

}

}

?>
``````
0

LVL 2

Accepted Solution

regality earned 500 total points
ID: 33683155
on line 12, right before you call mysql_query add this line:

\$query = getQuery(0);

0

Author Comment

ID: 33685068
I have it switched to this and it isn't pulling any records

\$currentdate = date('Y-m-d');
\$thisMonth = date('Y-m') . "-01";
\$query = "SELECT * FROM php_event_events
WHERE dt >= date('\$thisMonth') + INTERVAL '\$monthsAhead' MONTH
AND dt <  date('\$thisMonth') + INTERVAL '" . (\$monthsAhead + 1) . "' MONTH";
return \$query;
}

\$query = getQuery(0);

\$event_query_result = mysql_query(\$query);

if (mysql_num_rows(\$event_query_result) > 0)

{

while (\$event_row = mysql_fetch_assoc(\$event_query_result))

{

\$event_id = \$event_row['ID'];

\$event = \$event_row['title'];
\$description = \$event_row['description'];
\$event_time = \$event_row['startTime'];
\$event_date = \$event_row['dt'];

\$event_time = \$event_row['startTime'];

\$event_time1 = mysql2timestamp1(\$event_date);

\$event_display_date = date("D, F j, o", \$event_time1);

echo "<div class=\"event_name\">\$event_display_date</div>\n";

echo "<div class=\"event_desc\">\$event</div> <P>\n";

}

}
0

Author Comment

ID: 33685116
Guess I should have some records that match the query... thanks, it worked great
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will wâ€¦
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll