show content on specific days of the week and hours
Moderator: General Moderators
show content on specific days of the week and hours
I have a back end where I can write new "Stories" which appear on my Frontpage like on a blog.
Now I would like to add a field in the back end where I can define on which days of the week and at what times of those days this "Stories" should be shown on the Frontpage. So lets say, I want Story A to show on Mondays from 15.00-17.00 and from 22.00-24.00 and I want Story B to show from 08.00-12.00.
Any idea how I could achieve something like that with php and mysql?
Now I would like to add a field in the back end where I can define on which days of the week and at what times of those days this "Stories" should be shown on the Frontpage. So lets say, I want Story A to show on Mondays from 15.00-17.00 and from 22.00-24.00 and I want Story B to show from 08.00-12.00.
Any idea how I could achieve something like that with php and mysql?
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: show content on specific days of the week and hours
This may not be possible depending on your host, but I would do this:
---
1. Create a boolean / bit field with default value 0 called 'active' or some such, for your (i'm assuming here) articles table.
2. Use the crontab to call a script, php or otherwise, which runs every hour and contains the logic to turn off all articles, then turns on the articles that fit the current date time slot. Such a script may look like the following:
Alternately you can provide most the logic to this in mysql alone, which might be the easiest route in terms of length of code involved and akwardness of the solution:
1 - create a table called article_schedule:
Populate this table with an article ID and the day/hour combo for every hour that an article should appear, then you are able to query it and resolve the article id with relative ease:
I would personally opt for the third solution, however only because I would find it easiest to maintain. Your mileage may vary.
-Andy
ETA - Corrected the quotation on "... DEFAULT 0" on the `hour` field for the mysql create table procedure, "DISTINCT" to the query in the final example.
---
1. Create a boolean / bit field with default value 0 called 'active' or some such, for your (i'm assuming here) articles table.
2. Use the crontab to call a script, php or otherwise, which runs every hour and contains the logic to turn off all articles, then turns on the articles that fit the current date time slot. Such a script may look like the following:
Code: Select all
$today_day = date("w"); // number 0-6, i.e. Sunday - Saturday
$current_hour = date("H");
// example 1, using a multidimensional array to solve for current article.
$articles = array(
0 => array(
0 => "article_Sunday_Midnight", // text in quotes here could be the ID of the article in your articles table.
1 => "article_Sunday_One_AM"
// etc.
),
1 => array(
0 => "article_Monday_Midnight",
1 => "article_Monday_One_AM"
// etc.
)
);
// example 2, using switch statements, much bulkier in code length, easier to read perhaps.
$article_id = -1;
switch($today_day)
{
// Sunday
case 0:
switch($today_hour)
{
case 0:
$article_id = "123456";
break;
}
break;
}
1 - create a table called article_schedule:
Code: Select all
CREATE TABLE `article_schedule` (
`ID` INT PRIMARY KEY AUTO_INCREMENT,
`article_id` INT NOT NULL,
`week_day` ENUM ('0','1','2','3','4','5','6') DEFAULT '0',
`hour` ENUM ('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23') DEFAULT '0'
);Code: Select all
$today_day = date("w");
$current_hour = date("H");
$query = "SELECT DISTINCT article_id FROM article_schedule WHERE week_day = '$today_day' AND hour = '$current_hour'";
// loop through results, or whatever you want from here, you now have a list of all article id's that should be shown right now.-Andy
ETA - Corrected the quotation on "... DEFAULT 0" on the `hour` field for the mysql create table procedure, "DISTINCT" to the query in the final example.
Last edited by andyhoneycutt on Fri Jul 10, 2009 10:53 am, edited 1 time in total.
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: show content on specific days of the week and hours
Blah i type too quick for my own good sometimes. You could benefit from a "DISTINCT" on that select statement in the last example.
Re: show content on specific days of the week and hours
This might be a little simpler.
Create two tables: one to hold the story data, the other to keep track of the schedule.
Let's say the current time on the MySQL server is "2009-07-10 10:15:00". Then you can retrieve the current story (story two) with this query:
Edit: This post was recovered from search engine cache.
Create two tables: one to hold the story data, the other to keep track of the schedule.
Code: Select all
DESCRIBE story;
# +-------+------------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +-------+------------------+------+-----+---------+----------------+
# | id | int(10) UNSIGNED | NO | PRI | | AUTO_INCREMENT |
# | title | varchar(64) | NO | | | |
# +-------+------------------+------+-----+---------+----------------+
DESCRIBE story_schedule;
# +------------+------------------+------+-----+---------+-------+
# | Field | Type | Null | Key | Default | Extra |
# +------------+------------------+------+-----+---------+-------+
# | story_id | int(10) UNSIGNED | NO | PRI | | |
# | start_time | datetime | NO | PRI | | |
# | stop_time | datetime | NO | PRI | | |
# +------------+------------------+------+-----+---------+-------+
SELECT * FROM story;
# +----+------------------+
# | id | title |
# +----+------------------+
# | 1 | Sample Story One |
# | 2 | Sample Story Two |
# +----+------------------+
SELECT * FROM story_schedule;
# +----------+---------------------+---------------------+
# | story_id | start_time | stop_time |
# +----------+---------------------+---------------------+
# | 1 | 2009-07-10 09:00:00 | 2009-07-10 10:00:00 |
# | 1 | 2009-07-10 11:00:00 | 2009-07-10 12:00:00 |
# | 2 | 2009-07-10 10:00:00 | 2009-07-10 11:00:00 |
# +----------+---------------------+---------------------+Code: Select all
SELECT st.title FROM story AS st, story_schedule AS sc
WHERE st.id = sc.story_id
AND CURRENT_TIMESTAMP BETWEEN sc.start_time AND sc.stop_time;
# +------------------+
# | title |
# +------------------+
# | Sample Story Two |
# +------------------+
Last edited by McInfo on Wed Jun 16, 2010 2:11 pm, edited 1 time in total.
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: show content on specific days of the week and hours
I like it! My assumption was that the articles/stories table was already in existence.
-Andy
-Andy
Re: show content on specific days of the week and hours
Even if the story/article table already exists, the schedule table can be added without much trouble.
Edit: This post was recovered from search engine cache.
Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 2:12 pm, edited 1 time in total.
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: show content on specific days of the week and hours
Hmm. Maybe I'm missing something, so please bear with me here! Using your solution, McInfo, I believe I would only be able to gather the results for that particular day, and have to reassign times to the articles every day I wish them to run. Please clarify?
Thanks much,
Andy
Thanks much,
Andy
Re: show content on specific days of the week and hours
You're right. My scheduling system does not support recurrent events unless each occurrence is scheduled individually.
Edit: This post was recovered from search engine cache.
Edit: This post was recovered from search engine cache.
Last edited by McInfo on Wed Jun 16, 2010 2:12 pm, edited 1 time in total.
Re: show content on specific days of the week and hours
thanks a lot so far.
i was talking about recurrent events and i also wanna be able to have "story c" show only at 15:15 to 19:30 (not only full hours) from monday to friday and from 15:15 to 23:00 on Saturday. I thought there was an easy way to do this?
i was talking about recurrent events and i also wanna be able to have "story c" show only at 15:15 to 19:30 (not only full hours) from monday to friday and from 15:15 to 23:00 on Saturday. I thought there was an easy way to do this?
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: show content on specific days of the week and hours
I wouldn't suggest that any solution to this problem is extremely difficult, just a bit complicated. You can extend my solution to allow minutes as well by applying the same logic for each sub-division of time you see fit.
Alternately you could alter McInfo's solution by dropping the date part of his date-time field, and adding a weekday column and query it accordingly.
-Andy
Alternately you could alter McInfo's solution by dropping the date part of his date-time field, and adding a weekday column and query it accordingly.
-Andy
Re: show content on specific days of the week and hours
This concept might be helpful for determining whether to display a story depending on what day of the week it is.
Each of the seven days is either active or inactive. An activated day is represented with a one (1). A deactivated day is represented with a zero (0). There are seven places for zeros and ones, so there are 128 possible combinations (2 ^ 7 = 128).
This chart* shows all of the possible combinations.
Notice that Sunday == 1, Monday == 2, Tuesday == 4, Wednesday == 8, Thursday == 16, Friday == 32, and Saturday == 64. Bitwise "anding" can be used to determine if a particular day is active or inactive in a given combination.
For example, if we compare combination 42 with 8, we can determine if Wednesday (8) is active for that combination.
This SQL query returns 8, which means that Wednesday is enabled in combination 42.
This SQL query returns 0, which means that Wednesday is disabled in combination 119.
You can also make multiple comparisons at once.
*For anyone interested, I used this script to build the chart.
Edit: This post was recovered from search engine cache.
Each of the seven days is either active or inactive. An activated day is represented with a one (1). A deactivated day is represented with a zero (0). There are seven places for zeros and ones, so there are 128 possible combinations (2 ^ 7 = 128).
This chart* shows all of the possible combinations.
Code: Select all
SFTWTMS
0 = 0000000 = .. .. .. .. .. .. ..
1 = 0000001 = .. .. .. .. .. .. Su
2 = 0000010 = .. .. .. .. .. Mo ..
3 = 0000011 = .. .. .. .. .. Mo Su
4 = 0000100 = .. .. .. .. Tu .. ..
5 = 0000101 = .. .. .. .. Tu .. Su
6 = 0000110 = .. .. .. .. Tu Mo ..
7 = 0000111 = .. .. .. .. Tu Mo Su
8 = 0001000 = .. .. .. We .. .. ..
9 = 0001001 = .. .. .. We .. .. Su
10 = 0001010 = .. .. .. We .. Mo ..
11 = 0001011 = .. .. .. We .. Mo Su
12 = 0001100 = .. .. .. We Tu .. ..
13 = 0001101 = .. .. .. We Tu .. Su
14 = 0001110 = .. .. .. We Tu Mo ..
15 = 0001111 = .. .. .. We Tu Mo Su
16 = 0010000 = .. .. Th .. .. .. ..
17 = 0010001 = .. .. Th .. .. .. Su
18 = 0010010 = .. .. Th .. .. Mo ..
19 = 0010011 = .. .. Th .. .. Mo Su
20 = 0010100 = .. .. Th .. Tu .. ..
21 = 0010101 = .. .. Th .. Tu .. Su
22 = 0010110 = .. .. Th .. Tu Mo ..
23 = 0010111 = .. .. Th .. Tu Mo Su
24 = 0011000 = .. .. Th We .. .. ..
25 = 0011001 = .. .. Th We .. .. Su
26 = 0011010 = .. .. Th We .. Mo ..
27 = 0011011 = .. .. Th We .. Mo Su
28 = 0011100 = .. .. Th We Tu .. ..
29 = 0011101 = .. .. Th We Tu .. Su
30 = 0011110 = .. .. Th We Tu Mo ..
31 = 0011111 = .. .. Th We Tu Mo Su
32 = 0100000 = .. Fr .. .. .. .. ..
33 = 0100001 = .. Fr .. .. .. .. Su
34 = 0100010 = .. Fr .. .. .. Mo ..
35 = 0100011 = .. Fr .. .. .. Mo Su
36 = 0100100 = .. Fr .. .. Tu .. ..
37 = 0100101 = .. Fr .. .. Tu .. Su
38 = 0100110 = .. Fr .. .. Tu Mo ..
39 = 0100111 = .. Fr .. .. Tu Mo Su
40 = 0101000 = .. Fr .. We .. .. ..
41 = 0101001 = .. Fr .. We .. .. Su
42 = 0101010 = .. Fr .. We .. Mo ..
43 = 0101011 = .. Fr .. We .. Mo Su
44 = 0101100 = .. Fr .. We Tu .. ..
45 = 0101101 = .. Fr .. We Tu .. Su
46 = 0101110 = .. Fr .. We Tu Mo ..
47 = 0101111 = .. Fr .. We Tu Mo Su
48 = 0110000 = .. Fr Th .. .. .. ..
49 = 0110001 = .. Fr Th .. .. .. Su
50 = 0110010 = .. Fr Th .. .. Mo ..
51 = 0110011 = .. Fr Th .. .. Mo Su
52 = 0110100 = .. Fr Th .. Tu .. ..
53 = 0110101 = .. Fr Th .. Tu .. Su
54 = 0110110 = .. Fr Th .. Tu Mo ..
55 = 0110111 = .. Fr Th .. Tu Mo Su
56 = 0111000 = .. Fr Th We .. .. ..
57 = 0111001 = .. Fr Th We .. .. Su
58 = 0111010 = .. Fr Th We .. Mo ..
59 = 0111011 = .. Fr Th We .. Mo Su
60 = 0111100 = .. Fr Th We Tu .. ..
61 = 0111101 = .. Fr Th We Tu .. Su
62 = 0111110 = .. Fr Th We Tu Mo ..
63 = 0111111 = .. Fr Th We Tu Mo Su
64 = 1000000 = Sa .. .. .. .. .. ..
65 = 1000001 = Sa .. .. .. .. .. Su
66 = 1000010 = Sa .. .. .. .. Mo ..
67 = 1000011 = Sa .. .. .. .. Mo Su
68 = 1000100 = Sa .. .. .. Tu .. ..
69 = 1000101 = Sa .. .. .. Tu .. Su
70 = 1000110 = Sa .. .. .. Tu Mo ..
71 = 1000111 = Sa .. .. .. Tu Mo Su
72 = 1001000 = Sa .. .. We .. .. ..
73 = 1001001 = Sa .. .. We .. .. Su
74 = 1001010 = Sa .. .. We .. Mo ..
75 = 1001011 = Sa .. .. We .. Mo Su
76 = 1001100 = Sa .. .. We Tu .. ..
77 = 1001101 = Sa .. .. We Tu .. Su
78 = 1001110 = Sa .. .. We Tu Mo ..
79 = 1001111 = Sa .. .. We Tu Mo Su
80 = 1010000 = Sa .. Th .. .. .. ..
81 = 1010001 = Sa .. Th .. .. .. Su
82 = 1010010 = Sa .. Th .. .. Mo ..
83 = 1010011 = Sa .. Th .. .. Mo Su
84 = 1010100 = Sa .. Th .. Tu .. ..
85 = 1010101 = Sa .. Th .. Tu .. Su
86 = 1010110 = Sa .. Th .. Tu Mo ..
87 = 1010111 = Sa .. Th .. Tu Mo Su
88 = 1011000 = Sa .. Th We .. .. ..
89 = 1011001 = Sa .. Th We .. .. Su
90 = 1011010 = Sa .. Th We .. Mo ..
91 = 1011011 = Sa .. Th We .. Mo Su
92 = 1011100 = Sa .. Th We Tu .. ..
93 = 1011101 = Sa .. Th We Tu .. Su
94 = 1011110 = Sa .. Th We Tu Mo ..
95 = 1011111 = Sa .. Th We Tu Mo Su
96 = 1100000 = Sa Fr .. .. .. .. ..
97 = 1100001 = Sa Fr .. .. .. .. Su
98 = 1100010 = Sa Fr .. .. .. Mo ..
99 = 1100011 = Sa Fr .. .. .. Mo Su
100 = 1100100 = Sa Fr .. .. Tu .. ..
101 = 1100101 = Sa Fr .. .. Tu .. Su
102 = 1100110 = Sa Fr .. .. Tu Mo ..
103 = 1100111 = Sa Fr .. .. Tu Mo Su
104 = 1101000 = Sa Fr .. We .. .. ..
105 = 1101001 = Sa Fr .. We .. .. Su
106 = 1101010 = Sa Fr .. We .. Mo ..
107 = 1101011 = Sa Fr .. We .. Mo Su
108 = 1101100 = Sa Fr .. We Tu .. ..
109 = 1101101 = Sa Fr .. We Tu .. Su
110 = 1101110 = Sa Fr .. We Tu Mo ..
111 = 1101111 = Sa Fr .. We Tu Mo Su
112 = 1110000 = Sa Fr Th .. .. .. ..
113 = 1110001 = Sa Fr Th .. .. .. Su
114 = 1110010 = Sa Fr Th .. .. Mo ..
115 = 1110011 = Sa Fr Th .. .. Mo Su
116 = 1110100 = Sa Fr Th .. Tu .. ..
117 = 1110101 = Sa Fr Th .. Tu .. Su
118 = 1110110 = Sa Fr Th .. Tu Mo ..
119 = 1110111 = Sa Fr Th .. Tu Mo Su
120 = 1111000 = Sa Fr Th We .. .. ..
121 = 1111001 = Sa Fr Th We .. .. Su
122 = 1111010 = Sa Fr Th We .. Mo ..
123 = 1111011 = Sa Fr Th We .. Mo Su
124 = 1111100 = Sa Fr Th We Tu .. ..
125 = 1111101 = Sa Fr Th We Tu .. Su
126 = 1111110 = Sa Fr Th We Tu Mo ..
127 = 1111111 = Sa Fr Th We Tu Mo SuFor example, if we compare combination 42 with 8, we can determine if Wednesday (8) is active for that combination.
Code: Select all
42 = 0101010 = .. Fr .. We .. Mo .. Monday + Wednesday + Friday
8 = 0001000 = .. .. .. We .. .. .. Wednesday
-----------------------------------
8 = 0001000 = .. .. .. We .. .. .. Result of bitwise anding (Wednesday is active)Code: Select all
SELECT (42 & 8) FROM DUAL;
# Result: 8Code: Select all
SELECT (119 & 8) FROM DUAL;
# Result: 0Code: Select all
42 = 0101010 = .. Fr .. We .. Mo .. Monday + Wednesday + Friday
40 = 0101000 = .. Fr .. We .. .. .. Wednesday + Friday
-----------------------------------
40 = 0101000 = .. Fr .. We .. .. .. Result of bitwise anding (Wednesday and Friday are active)Code: Select all
42 = 0101010 = .. Fr .. We .. Mo .. Monday + Wednesday + Friday
56 = 0111000 = .. Fr Th We .. .. .. Wednesday + Thursday + Friday
-----------------------------------
40 = 0101000 = .. Fr .. We .. .. .. Result of anding (Wednesday and Friday are active, but Thursday is not)Code: Select all
<?php
header('Content-Type: text/plain');
$dotw = array
( 1 => array('..', 'Su', 'Sunday')
, 2 => array('..', 'Mo', 'Monday')
, 4 => array('..', 'Tu', 'Tuesday')
, 8 => array('..', 'We', 'Wednesday')
, 16 => array('..', 'Th', 'Thursday')
, 32 => array('..', 'Fr', 'Friday')
, 64 => array('..', 'Sa', 'Saturday')
);
// Days of the week in reverse
echo " SFTWTMS\n";
for ($i = 0; $i < 128; $i++) {
$binary = '';
$days = '';
for ($j = 64; $j >= 1; $j /= 2) {
$bit = (($i & $j) / $j);
$binary .= $bit;
$days .= $dotw[$j][$bit] . ' ';
}
echo str_pad($i, 3, ' ', STR_PAD_LEFT)
. ' = '
. $binary
. ' = '
. $days
. "\n";
}
?>
Last edited by McInfo on Wed Jun 16, 2010 2:17 pm, edited 1 time in total.
- andyhoneycutt
- Forum Contributor
- Posts: 468
- Joined: Wed Aug 27, 2008 10:02 am
- Location: Idaho Falls
Re: show content on specific days of the week and hours
Very nice addition McInfo. I do like that idea! While you can do the same with hours, I see no way to fully attribute a binary representation of the minutes. You could do quarter-hours for certain, though.