Using PHP to show the current week's sermon

[bddotnet]

I would like to know how to display the current sermon each week using PHP/MySQL. I have the sermons and the date in a mysql database.

www.wschristianchurch.org/?pg=sundaymorning is where the current month's sermons are posted. I would like to post the sermon for the current week on the homepage.

How?

[tbonekkt]

You would basically do your mysql query based on a date range. Not hard at all.SELECT * FROM `table` WHERE field BETWEEN '2006-08-28' AND '2006-09-03'Naturally you would need to figure out the dates and replace in the above query.

[bddotnet]

Thanks Tom! How would I modify that to display the current week's sermon automatically without changing the query?

[cc1030]

How do you code that page you referenced (www.wschristianchurch.org/?pg=sundaymorning)? Is that hard-coded, or are those sermons displayed by reading the MySQL database? Also, this may sound like a simple question, but how do you want to define "the current week" -- beginning on Sunday and ending on Saturday?

[tbonekkt]

Being that it's a church, the week would begin on Monday and end on Sunday. So basically you would use PHP to get the current day of week and evaluate based on that.

[bddotnet]

The page displays the information from a MySQL database.

I want the week to begin on Monday and end on Sunday, just as Tom indicated, but I want the query to automatically switch sermons based on the current date.

Why doesn't this work?


$current_date_month = date("Y-m");
$nextweek = date(("d")+7);
$list_query = mysql_query("SELECT * FROM morningsermons where date BETWEEN '$current_date_month' AND '$nextweek'");

[cc1030]

Well, this isn't pretty, and there are probably better ways of doing this, but this should work. I don't know much about MySQL queries, so I'm assuming that the date format posted by Tom is correct (year-month-date):

// Get beginning date of date (Monday) into variable $week_begin
// and ending date of week (Sunday) into variable $week_end
$date_format = 'Y-m-d';
$day_length = (24 * 60 * 60);
$now_vars = getdate();
$into_today = (($now_vars['hours'] * 60 * 60) + ($now_vars['minutes'] * 60) + $now_vars['seconds']);
$begin_offset = ($now_vars['wday'] - 1);
if ($begin_offset < 0) $begin_offset = 6;
$begin_offset = ($begin_offset * $day_length);
$day_begin = ($now_vars[0] - $into_today);
$week_begin = ($day_begin - $begin_offset);
$week_end = ($week_begin + (6 * $day_length));

$list_query = mysql_query("SELECT * FROM morningsermons where date BETWEEN '$week_begin' AND '$week_end'");

[tbonekkt]

I don't know much about MySQL queries, so I'm assuming that the date format posted by Tom is correct (year-month-date):What I posted as an assumption based on "the norm". Naturally, it all depends on how he formatted the data.

[jmucchiello]

Why not

SELECT sermon_text FROM sermons
WHERE sermon_date <= now()
ORDER BY sermon_date DESC
LIMIT 1,1

The last sermon in the database should be this week's sermon and if you do put future sermons into the DB, the where clause will make sure they aren't displayed prematurely.