Solved

Get next month from records

Posted on 2010-09-14
15
275 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
 

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now