Solved

Get next month from records

Posted on 2010-09-14
15
280 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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 …

813 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

17 Experts available now in Live!

Get 1:1 Help Now