Selecting Dates within next 30 days

Hatch

I'm trying to figure out a mysql statement that will select all the dates in the date field (which is a date type) in my table calendar that are within the next 30 days. This is what I have so far but doesn't seem to work yet.

$query = "SELECT date FROM calendar WHERE TO_DAYS(NOW()) - TO_DAYS(NOW() + 30) <= 30";

Maybe there's an easier way that I don't know of yet.

khaldryck

Not really sure where it's wrong as i always use timestamp instead of date field, but maybe using "between" instead of your where clause. never used to_days either.

something like that :

$query = "SELECT date FROM calendar HAVING TO_DAYS(date) between TO_DAYS(NOW()) and TO_DAYS(NOW() + 30)";

Hatch

I don't understand what "HAVING TO_DAYS(date)" means. What I want to do is display all the dates within the next 30 days.

BlackCatt

I don't think you want to be using the TO_DAYS() function. That returns the number of days since year 0. What you should try to use is the DATE_ADD() function. The syntax looks like "DATE_ADD(date,INTERVAL expr type)".

For your example it would work like

$query = "SELECT date FROM calendar WHERE [B]date >= NOW() and date <= DATE_ADD(NOW(), INTERVAL 30 DAYS)[/B]";

or using BETWEEN would look like

$query = "SELECT date FROM calendar WHERE [B]date BETWEEN NOW() and DATE_ADD(NOW(), INTERVAL 30 DAYS)[/B]";

both should give the same result.

So any value in your "date" column that is between NOW() (inclusive) and 30 days from NOW() (inclusive) will be returned.

Hatch

Now if I wanted to grab all the dates though from the date column then I would do this right?

$query = "SELECT * FROM calendar WHERE date >= NOW() and date <= DATE_ADD(NOW(), INTERVAL 30 DAYS)";

Hatch

Ok...here is the code I have that I tried...

$query = "SELECT * FROM calendar WHERE date >= NOW() and date <= DATE_ADD(NOW(), INTERVAL 30 DAYS)";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i = 0; i < $num_results; $i++)
{
	$row = mysql_fetch_array($result);
	printDate($row["id"]);
}

Here is the error that I got...
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Any Ideas?

BlackCatt

Originally posted by Hatch
Now if I wanted to grab all the dates though from the date column then I would do this right?
$query = "SELECT * FROM calendar WHERE date >= NOW() and date <= DATE_ADD(NOW(), INTERVAL 30 DAYS)";
[/B]

Not exactly. That would just return to you all of the columns in the calendar table, who's date column is between NOW() and NOW() + 30 days.

If you want all the date values from the calendar regardless of when that date value is try.

$query = "SELECT date FROM calendar";

This returns all the data from the date column from calendar regardless of the date. (thats confusing 😉)

Hatch

That would just return to you all of the columns in the calendar table, who's date column is between NOW() and NOW() + 30 days.

Yah that is what I meant 😉 I want all dates that are in that range. I just didn't want just ONE date in that range.

Hatch

Ok...here is the code I have that I tried...

$query = "SELECT * FROM calendar WHERE date >= NOW() and date <= DATE_ADD(NOW(), INTERVAL 30 DAYS)";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);

for ($i = 0; i < $num_results; $i++)
{
    $row = mysql_fetch_array($result);
    printDate($row["id"]);
}

Here is the error that I got...
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

Any Ideas?

BlackCatt

I think that your mysql_query statement is missing a db connection parameter. So

$result = mysql_query($query);

should be

$result = mysql_query($query, $objConn);

where $objConn is your mysql db connection object.

Hatch

Well I do have code that I've been using alot that is just above that code I showed in the above post that connects to the database. I know that works because I've been using it alot.

BlackCatt

Yeah your right. It should be using whatever db connection you have previously defined as the default.

If you can, take the query out and try it in PhpMyAdmin if you can or directly against MySQL. Make sure there isn't any problem with that.

Hatch

Okay...I added

or die("Error".mysql_error());

that to the end of the

mysql_query($query);

and that pinpointed the error to

30 Days)"

which is suppose to be day instead of days. So I changed that and now I run the script and it hangs and then timesout after 30 seconds.

Any ideas?

saidbakr

I think, The word date which naming your field, may be regarded as a reserved word.

Hatch

Here's all the code for when it timesout and the output

Code:

$query = "SELECT * FROM calendar WHERE date >= NOW() and date <= DATE_ADD(NOW(), INTERVAL 30 DAY)";
//$query = "SELECT * FROM calendar WHERE date BETWEEN NOW() and DATE_ADD(NOW(), INTERVAL 30 DAY)";
$result = mysql_query($query) or die("Invalid Query ".mysql_error());
$num_results = mysql_num_rows($result);
echo $num_results;

for ($i = 0; i < $num_results; $i++)
{
	$row = mysql_fetch_array($result);
	echo $row["id"];
}

The Output:

38910
Fatal error: Maximum execution time of 30 seconds exceeded on line 17

...and line 17 is

for ($i = 0; i < $num_results; $i++)

It's saying $num_results is 38910??? I only have like 4 rows in the table right now. And three of them are within the next 30 days.

Hatch

I think, The word date which naming your field, may be regarded as a reserved word.

I changed the name of the field from "date" to "dateEntry" and also changed the query to match

$query = "SELECT * FROM calendar WHERE dateEntry >= NOW() and dateEntry <= DATE_ADD(NOW(), INTERVAL 30 DAY)";

and I still get the same problem :-\

BlackCatt

Hmmm... that is strange that it is saying you have that many rows in there if you don't. Just as a double check you could try this query

SELECT count(dateEntry) as cnt FROM calendar WHERE dateEntry >= NOW() and dateEntry <= DATE_ADD(NOW(), INTERVAL 30 DAY)

and echo the cnt value back to the screen.

This query just counts the records that are returned, much like the mysql_num_rows except the value is just passed back as a column that I named "cnt".

You may also want to just verify how many total rows you have in that table by running the same query without the WHERE clause.

SELECT count(dateEntry) as cnt FROM calendar

Hatch

Ok I tried those select statements and they returned the correct numbers. So I decided to try the original select statement I used above and echo the number but this time I commented out the for loop and it echoed the correct number of dates that were within the next 30 days!

So there is something wrong with that for loop :-\

So here is what I have so far...

$query = "SELECT * FROM calendar WHERE dateEntry >= NOW() and dateEntry <= DATE_ADD(NOW(), INTERVAL 30 DAY)";
$result = mysql_query($query) or die("Invalid Query ".mysql_error());
$num_results = mysql_num_rows($result);
echo $num_results;

/*for ($i = 0; i < $num_results; $i++)
{
	$row = mysql_fetch_array($result);
	echo $row["id"];
}
*/

That for loop is commented out right now and it will echo the correct $num_results (which happens to be 3). But when I take the comments out and include the for loop it will stall for 30 seconds and then display that large number for $num_results and give me the error. You'd think it would echo that number first and you'd be able to see it while it got stuck in the for loop.

Hatch

LOL! Okay get this.

I spotted in that for loop that I forgot a "$" next to the i in "i < $num_results".

So I fixed that and now it doesn't hang at all. It executes in about 1 second BUT it still echos $num_results as 38910 and doesn't echo $row["id"]; that's in the for loop.

Hatch

I knew it would be one of those stupid deals. Finally figured out what "38910" meant after I put in some <br> and used that thing you call a brain 😛

$num_results 3
id of 1st 8
id of 2nd 9
id of 3rd 10

Thanks for all your help getting this to work. I appreciate all the help ALOT. I learned alot.