0

I know how to run an SQL query against a database based on dates, but how would I get my server to only run that query at specific times or on specific days?

I have an SQL database with activities (edit: these activities are to be displayed on a monitor as digital signage, so without any user input). Some activities run every weekday and some just on a specific day. I've put the every day activities as having the same date - 1900-01-01 - and at the moment I have an SQL Query that always displays those entries, as well as another query that only display entries from the current date:

<tbody>
   <?php
            require_once('../include/dbconn.php');
            $result = $conn->prepare("SELECT * FROM daily WHERE Date_For='1900-01-01' ORDER BY id ASC");
            $result->execute();
            for($i=0; $row = $result->fetch(); $i++){


    ?>
            <tr>
                    <td><label><center><?php echo $row['Child']; ?></center></label></td>
                    <td><label><center><?php echo $row['Activity']; ?></center></label></td>

            </tr>
            <?php } ?>





    <?php
            require_once('../include/dbconn.php');
            $result = $conn->prepare("SELECT * FROM daily WHERE DATE(Date_For) = CURDATE() ORDER BY id ASC");
            $result->execute();
            for($i=0; $row = $result->fetch(); $i++){

    ?>
            <tr>
                    <td><label><center><?php echo $row['Child']; ?></center></label></td>
                    <td><label><center><?php echo $row['Activity']; ?></center></label></td>

            </tr>
            <?php } ?>
    </tbody>

I know I could just add the Daily Activities in with the date they're actually running on, and add a time aspect in the database, but I just wanted to check if there was a way to write one set of code to regulate access, rather than adding more work to data entry?

Phil
  • 103
  • 7
  • 2
    if you only want some code to run at certain times, then either a) detect the current date when the code executes, and decide whether to continue or not, or b) control when the code executes by means of a Cron Job or scheduled task. – ADyson Mar 20 '20 at 11:31
  • But the requirement is still a little unclear I think, anyway? Do you want it so that an activity will only be returned from the query if it occurs on today's date? (if so, why - can people not search for what's happening tomorrow, instead?) Or you just don't want to run the query at all on certain days? Your wording is a bit muddled, unfortunately. – ADyson Mar 20 '20 at 11:32
  • Sorry, I missed off that it was to be used in Digital Signage, so people wouldn't be able to search. The first part of your reply "detect the current date when the code executes, and decide whether to continue or not" is basically what I'm asking. – Phil Mar 20 '20 at 11:51
  • Ok so in PHP you can get the current date very easily. Then if you have a list somewhere of days (e.g. days of the week, or days of the month, whatever the schedule needs to be) on which you want it to run (or not run, if you prefer), you can compare whether the current date matches any of them or not. Does that help? – ADyson Mar 20 '20 at 11:55
  • But to be honest, from your code and description, I can't work out which of the two queries you would be wanting to suppress, or on which days, and why? Can you explain more clearly what you're trying to achieve? I'm thinking there could be a better way, depending on the overall requirement. – ADyson Mar 20 '20 at 11:56
  • The first query is for a static date - FROM daily WHERE Date_For='1900-01-01' - and everyday it will return the same results, as the query and database won't change, whatever date it's running. The second query is against dates that change - FROM daily WHERE DATE(Date_For) = CURDATE() - so if an activity has yesterday or tomorrows date, it won't show. It's a bit like this question: https://stackoverflow.com/questions/1981036/php-script-to-execute-at-certain-times , but relating to executing SQL queries. – Phil Mar 20 '20 at 12:03
  • I understand that. But that still doesn't explain which one you don't want to run, or at which times, or why. Are you thinking that it's wasteful to run the "every day" one repeatedly because the results are always the same (apart from when your activity programme changes, maybe)? How long does it take to execute that query? Milliseconds, I would guess? I think you might be fretting over nothing much. – ADyson Mar 20 '20 at 12:04
  • Anyway you know you could just make this all one query anyhow? `SELECT * FROM daily WHERE Date_For='1900-01-01' OR Date_For = CURDATE() ORDER BY id ASC` – ADyson Mar 20 '20 at 12:07
  • P.S. for future reference, if you are ever running multiple queries on the same page, you don't need to write `require_once('../include/dbconn.php');` multiple times - once your connection is set up, you can re-use it for as many queries as you like within your script. it's not efficient to keep creating a new connection for every query. And require_**once** will ensure that the dbconn script is only loaded and executed once anyway, so the line is even more redundant. – ADyson Mar 20 '20 at 12:10
  • P.P.S. I note in the answer below you've got a `` in your page. So it's telling it to refresh every 5 minutes. If this is just data about pre-programmed daily activities, does any of the data actually change that frequently? Is there actually other data on the same page which gets updated so often that you need a 5-minute update? I'm saying this because if you're genuinely concerned about load on your database, another way to improve it would be reduce the refresh frequency to a more realistic timescale. – ADyson Mar 20 '20 at 12:17
  • The query is simply matching Dates (24 hours). If the activity was "Maths: 0900-1000", that could be an activity that would be shown 'everyday', and so be marked in the database with 1900-01-01. Except you wouldn't go in on a weekend, so it wouldn't need to be displayed. An activity like "Read page 15-20" would only be for one 'particular day', so that would have a particular date, and be shown on that date. Putting the two queries together would mean having to have an escape for 'particular day' activities that would happen at the weekend, as you would also cover the 1900 events too. – Phil Mar 20 '20 at 12:19
  • 300 seconds is probably too short, but, yes the information would change. If you're just querying dates, you'd still be displaying activities that had ended, but where on the same date. If I had a way to query when to execute the code, I could then add times to the database and it would then drop off activities that had already happened. – Phil Mar 20 '20 at 12:29
  • 1
    Good point about weekends, I'd forgotten your "every day" activities only ran on weekdays. `SELECT * FROM daily WHERE (Date_For='1900-01-01' AND DAYOFWEEK(CURDATE()) <> 1 AND DAYOFWEEK(CURDATE()) <> 7) OR Date_For = CURDATE() ORDER BY id ASC` should deal with that. (Assuming your culture settings in SQL mean that Sundays are day 1, and Saturdays are day 7. Adjust the numbers if not. Now the "every day" activities will not be returned from the query if today is saturday or sunday. Easier and neater (slightly) than detecting the date in PHP. – ADyson Mar 20 '20 at 14:11
  • That's it; I hadn't come across the the '<> 7' before. Thanks; sometimes the hardest part of finding an answer is finding the question to get the answer you're hoping for. – Phil Mar 20 '20 at 14:50
  • `<>` just means "not equal to" :-). In MySQL you can also write `!=` to mean the same thing – ADyson Mar 20 '20 at 14:51

1 Answers1

1

You can combine the two queries into one for slightly better efficiency, and also you can put extra sections in the WHERE clause to ensure the "every weekday" events don't show up when the query runs at weekends:

SELECT 
  * 
FROM 
  daily 
WHERE 
  (
    Date_For='1900-01-01' 
    AND DAYOFWEEK(CURDATE()) <> 1 
    AND DAYOFWEEK(CURDATE()) <> 7
  ) 
  OR Date_For = CURDATE() 
ORDER BY 
  id ASC

(Assuming your culture settings in SQL mean that Sundays are day 1, and Saturdays are day 7. Adjust the numbers if not.)

ADyson
  • 57,178
  • 14
  • 51
  • 63