Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get next month from records

Posted on 2010-09-14
15
Medium Priority
?
291 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

926 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