Automatic Display of events each week

[weemel]

I know how to display a list of upcoming events from a SQL database, but am wanting to display the events based on the date, eg. events happening this week only. Could someone tell me how to do this or point me in the right direction please? Geez there's so much to learn....

[willis1480]

well, I would have each event in mysql have a date column. You then would then query your dates for only dates that are within your dates. Here is something that I use:
//the date $array{"sun"}{0} is just date in Y-n-d notation go to this link to learn more:
[php.net...]

$date_start=date("Y-n-d",$array{"sun"}{0});
$date_end=date("Y-n-d",$array{"sun"}{0}+6*24*60*60);

$query="SELECT * FROM `calendar` WHERE 1 AND `date` >= '".$date_start."' AND `date` <= '".$date_end."' ORDER BY `date` ASC";

$result=mysql_query($query);

[edited by: jatar_k at 5:50 pm (utc) on Nov. 8, 2004]
[edit reason] linked it up [/edit]

[weemel]

Thanks, willis1480.

I didn't manage to find any information about what $array{"sun"}{0} means from your link, so i tried the following method:

<?php
$timenow = "NOW()";
$oneweeklater = "NOW() - INTERVAL 7 DAY)";
$week = "SELECT *, date_format(calendar_events.StartTime, '%l:%i%p')as starttime, date_format(calendar_events.EndTime, '%l:%i%p') as endtime
FROM calendar_events
WHERE 1 AND StartTime >= '" . $timenow . "'
AND StartTime <= '" . $oneweeklater . "';
$thisweek = mysql_query($week);
while ($w = mysql_fetch_assoc($thisweek))
{echo "$w[starttime]";
echo "$w[endtime]";}
?>

Apparently there is an unexpected T_variable at the echo lines, but i suspect the problem is to do with the query. Don't know what's wrong with it though! Can anyone help...?

Would also be grateful if someone would tell me what the WHERE 1 part means. Thanks!

[dreamcatcher]

This line doesnt look right to me:

$oneweeklater = "NOW() - INTERVAL 7 DAY)";

You have a closing bracket, but no opening one.

A PHP error doesnt always mean the error is on that specific line.

dc :)

[weemel]

Thanks.

Fixed that; it still doesn't work! Help...!

[dreamcatcher]

Change the echo lines to:

echo $w['starttime'];
echo $w['endtime'];

Its a process of elimination.

:)

[weemel]

Yup, did that. It then said that there was an unexpected $ in the?> line (which there wasn't, obviously). I still think it probably has something to do with the query...?

[eggy ricardo]

Look on the few lines prior to the error line. That error could be due to not closing quotation marks or a bracket or similar.

Added: Also don't forget to make sure you have all your statements ended with semicolons etc.

[dreamcatcher]

Yep, a 'an unexpected $' error is most probably a missing brace.

[bloke in a box]

The '$week =..' line is missing an ending "!

[dreamcatcher]

bloke in a box, that line is ok. :) Its spread across different lines, but closes ok.

[bloke in a box]

I wasn't referring to just that line as such :), I meant the whole statement.

Unless I am really going mad, you are still missing a "?

[eggy ricardo]

Yes i think there may be a missing " actually where Bloke mentioned. It is a bit difficult to tell with all the " and ' flying around.

Added: i think the last two lines of that block could just be:

WHERE 1 AND StartTime >= $timenow 
AND StartTime <= $oneweeklater[b]"[/b];

Correct me if i'm wrong - i'm not having a very clear thinking day today :)

[bloke in a box]

$week = "SELECT *, date_format(calendar_events.StartTime, '%l:%i%p')as starttime, date_format(calendar_events.EndTime, '%l:%i%p') as endtime 
FROM calendar_events 
WHERE 1 AND StartTime >= '" . $timenow . "' 
AND StartTime <= '" . $oneweeklater . "'[b]"[/b]; 

I think.

[dreamcatcher]

Apologies, Bloke in a Box, that is indeed a good call. :) I thought you meant directly at the end of the first line. I didnt see the " in your post.

[weemel]

Thanks, all! Yeah the problem was in the " at the end, but it still left an invalid mysql query (and quite messy too). Here's the code i ended up with, in case anyone's interested. Managed to cut all the confusing variables out. There's numerous ways of doing it but kept getting mixed up with how the date was meant to be formatted!

$startyear = "date_format(calendar_events.StartTime, '%Y-%m-%d')";
$week = "SELECT *, date_format(calendar_events.StartTime, '%l:%i%p') as starttime, date_format(calendar_events.EndTime, '%l:%i%p') as endtime,
date_format(calendar_events.StartTime, '%a, %e %b:') as eventdate
FROM calendar_events
WHERE $startyear >= NOW()
AND $startyear <= DATE_ADD(NOW(), INTERVAL 7 DAY)
ORDER BY StartTime";
$thisweek = mysql_query($week);
while ($w = mysql_fetch_array ($thisweek)) {
echo "$w[eventdate] <br />";
echo "Start:" . "$w[starttime] <br />";
echo "Finish:" . "$w[endtime] <br /> <br />";}

[weemel]

I still don't understand the meaning of WHERE 1 (which i didn't use) though? Guess that's intruding into mysql territory...

[ergophobe]

I'm guessing it's just a typo that keeps getting repeated after the first instance. If it doesn't give you an error, I suppose it would always evaluate to true and therefore be redundant.