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
Solved

Get next month from records

Posted on 2010-09-14
15
282 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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 …
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 count occurrences of each item in an array.

839 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