Solved

Get next month from records

Posted on 2010-09-14
15
289 Views
Last Modified: 2013-12-12
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
Comment
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
  • Learn & ask questions
15 Comments
 
LVL 4

Expert Comment

by:trencH87
ID: 33672735

<?php

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

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

Open in new window

0
 
LVL 2

Expert Comment

by:regality
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";

// for 1 month ahead:

$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:

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

Open in new window

0
 
LVL 3

Expert Comment

by:wuff
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:katlees
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

by:regality
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

by:katlees
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

by:regality
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";

Open in new window

0
 
LVL 2

Expert Comment

by:regality
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";

Open in new window

0
 

Author Comment

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

Expert Comment

by:regality
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.
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;
}

Open in new window

0
 
LVL 2

Expert Comment

by:regality
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;
}

Open in new window

0
 

Author Comment

by:katlees
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');
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;
}


$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";

}


}

?>

Open in new window

0
 
LVL 2

Accepted Solution

by:
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

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


$currentdate = date('Y-m-d');
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;
}

$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

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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
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.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question