Get records where "enddate" is within 30 days.

General PHP Help
#1

I’m trying to pull records from my database where the field “enddate” is within 30 days of today’s date.

I’ve been poking at it for a few weeks and can’t seem to make it work.

Anyone have any ideas of how I can get it to display it correctly?

Here is the code:
[php]enddate < DATE_ADD(NOW(),INTERVAL 30 DAY)[/php]

#2

Forward
select * from yourTable WHERE date_column BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)

Backward

select * from yourTable where date_column >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

#3

For some reason that is not working either.

I need the query to pull any records where the enddate is within 30 days, but has not passed yet.

(If it passes it obviously won’t pull the record because it does not meet the criteria of the SQL statement.)

#4

What is the type for your date your date column? Should be date or datetime

#5

This field is a datetime field.

#6

This seems to still not work. I’ve tried both of the codes you submitted. More than likely I’m not explaining it correctly.

My field “enddate” as a datetime formatted as: MM-DD-YYYY HH:MM

Basically I have a document (we’ll call that notifications.php) that searches for a record with a timestamp of 30 days (upcoming only) from the database. Currently when it finds a record that is upcoming within 30 days, it the script echo’s a CSS alert. What I’m not seeing is once the enddate has past, it should no longer see the record, so the alert should no longer be present.

But for some reason it’s not “disappearing”.

Here is my current SQL Query:

[php]
SELECT * FROM notifications WHERE status = ‘Current’ AND startdate >= NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) AND enddate < NOW()
[/php]

I feel like I have the general idea, but for some reason I can’t seem to get it to return any records.

#7

Kevin’s SQL statement should work for what you are describing. However, are you basing the query off of the end date or the start date?

[php]select *
from
notifications
WHERE
status = ‘Current’
AND
endate
BETWEEN
NOW()
and
DATE_add(now(), interval 30 DAY);[/php]

#8

Both actually.

The “startdate” (datetime) must be with 30 days of today’s date, and the “enddate” must not have yet passed the datetime in “enddate”.

Basically what I’m looking for is that if the start and end are between a certain window, it will return a record, and display the notification.

#9

So something that first qualifies the start date, then filters the end dates. This may work,

[php] select *
from
notifications
WHERE
status = ‘Current’
AND
startdate
BETWEEN
NOW()
and
DATE_add(now(), interval 30 DAY)
AND
enddate <= NOW();[/php]

#10

Sorry for the delay, was away from my desk for a few.

I’ve decided to just post the entire script. Basically it’s a notifications script that checks the database to see if any issues are currently going on at any given moment, the website (not currently on the web) reloads the page “notifications.php” every 5 seconds.

[php]

<?php $servername = "XXXXX"; $username = "XXXXX"; $password = "XXXXX"; $dbname = "XXXXX"; // Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "SELECT * FROM announcements WHERE date BETWEEN (NOW() - INTERVAL 30 DAY) AND NOW() AND published = 'on'"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) { ?> <?php // $timenow = date('h:i:sa', time()); // $chktime = date('h:i:sa', strtotime($row['date']));; ?>
Network Announcement:
<?php if (strlen($row['announcement']) <=140) { echo strip_tags($row['announcement']); } else { echo stripslashes(substr(strip_tags($row['announcement']), 0, 140)) . '...';} ?>
 <?php }} ?> <?php $sql = "SELECT * FROM notifications WHERE status = 'In Progress' || status = 'Investigating' || status = 'Reported' AND enddate >= now()"; $result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) { ?>

Possible Issue:
<?php if (strlen($row['description']) <=140) { echo strip_tags($row['description']); } else { echo stripslashes(substr(strip_tags($row['description']), 0, 140)) . '...';} ?>
<?php }} ?> <?php $sql = "SELECT * FROM notifications WHERE status = 'Offline' AND startdate <= now()"; $result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) { ?>

Confirmed Offline:
<?php if (strlen($row['description']) <=140) { echo strip_tags($row['description']); } else { echo stripslashes(substr(strip_tags($row['description']), 0, 140)) . '...';} ?>
<?php }} ?> <?php $sql = "SELECT * FROM notifications WHERE status = 'Current' AND startdate BETWEEN (NOW() and DATE_add(now(), interval 30 DAY)) AND enddate <= NOW()"; $result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) { ?>

Scheduled Maintenance:
<?php if (strlen($row['description']) <=140) { echo strip_tags($row['description']); } else { echo stripslashes(substr(strip_tags($row['description']), 0, 140)) . '...';} ?>
<?php }} ?> <?php $sql = "SELECT * FROM notifications WHERE status = 'Fixed' AND enddate > DATE_ADD(NOW(), INTERVAL 12 HOUR)"; $result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) { while($row = mysqli_fetch_assoc($result)) { ?>

Issue Resolved:
<?php if (strlen($row['description']) <=140) { echo strip_tags($row['description']); } else { echo stripslashes(substr(strip_tags($row['description']), 0, 140)) . '...';} ?>
<?php }} ?>
[/php]
#11

Did it fix your issue?

I would handle this on a RestFul API service.

#12

It did not fix my issue, which was the reason I posted the entire script.

I would rather continue to use my original code since the infrastructure for it is already in place and it wouldn’t really require a rewrite of the code.

I’ve also just noticed that none of the “notifications” on the script “disappear” after the enddate date/time has passed. The record for some reason remains visible in the script. Maybe I’ve written a few of the queries incorrectly. The site this is for relies heavily on this script being updated every 5 seconds, and due to the complexity of the queries involved, I feel that a Restful API method may cache or use stagnate information, which defeats the entire purpose of having the script update automatically.

#13

I’ll look at it further tomorrow. If you have some sample data for me to test against, pm me.

The entire script wouldn’t need to update. You would be using individual ajax calls that would be returning json data to populate the notification areas. I think you are missing some of the nicer points of RestFul, but that isn’t the issue at hand.

#14

Here’s a sample insert query. It gives you an idea of the table structure.

ID is an INT, Title/Description/Status are VARCHAR(255 max), and startdate/enddate/lastupdate are all DATETIME.

INSERT INTO `notifications` (`id`, `title`, `description`, `startdate`, `enddate`, `status`, `lastupdate`) VALUES
(1, 'Test', 'Description', '2015-05-14 13:00:00', '2015-05-15 14:30:00', 'Scheduled', '2015-05-15 14:25:00');
#15

I had to do some modifications to your script, but did get it working. The SQL statement I used was almost there, it just needed a different time operator for the last filter.

[php]“SELECT * FROM notifications WHERE status = ‘Current’ AND startdate BETWEEN (NOW() and DATE_add(now(), interval 30 DAY)) AND enddate >= NOW()”;[/php]

I would still look at refactoring your code for simplicity. Things like this:
[php]

<?php }} ?> <?php [/php] Are not needed. Separating the HTML from the PHP would also make things easier. Your not actually looking at a lot of work to accomplish this and it would save you time in making the application easily scalable in the future.
#16

I will give this a try when I am able, unfortunately I’m not at a workstation and don’t have FTP access at the moment.

I appreciate the help you’ve given me, I will post an update when I am able to give this a try.

Sponsor our Newsletter | Privacy Policy | Terms of Service