Page 1 of 1
show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 10:14 am
by silver
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?
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 10:49 am
by andyhoneycutt
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:
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;
}
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:
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'
);
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:
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.
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.
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 10:52 am
by andyhoneycutt
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
Posted: Fri Jul 10, 2009 11:06 am
by McInfo
This might be a little simpler.
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 |
# +----------+---------------------+---------------------+
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:
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 |
# +------------------+
Edit: This post was recovered from search engine cache.
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 11:15 am
by andyhoneycutt
I like it! My assumption was that the articles/stories table was already in existence.
-Andy
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 11:25 am
by McInfo
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.
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 11:52 am
by andyhoneycutt
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
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 12:21 pm
by McInfo
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.
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 12:30 pm
by silver
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?
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 1:16 pm
by andyhoneycutt
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
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 2:30 pm
by McInfo
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.
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 Su
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.
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)
This SQL query returns 8, which means that Wednesday is enabled in combination 42.
Code: Select all
SELECT (42 & 8) FROM DUAL;
# Result: 8
This SQL query returns 0, which means that Wednesday is disabled in combination 119.
Code: Select all
SELECT (119 & 8) FROM DUAL;
# Result: 0
You can also make multiple comparisons at once.
Code: 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)
*For anyone interested, I used this script to build the chart.
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";
}
?>
Edit: This post was recovered from search engine cache.
Re: show content on specific days of the week and hours
Posted: Fri Jul 10, 2009 3:30 pm
by andyhoneycutt
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.