CODEWITHSUNDEEP
phpsqldate
Phil
Phil

Reputation: 103

Only run an SQL query during the week

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?

Upvotes: 0

Views: 1034

Answers (1)

ADyson
ADyson

Reputation: 61983

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.)

Upvotes: 1

Related Questions