Calendar in Table or Function to Print?

ndorfnz · Dec 27, 2005

Hello everyone.

Creating "weekly log" of sort.

The user enters data into a form, then submits it to a table.

This tables contains the date the user entered the data and all the other information.

The results of this table are displayed in "weekly" increments (Sunday - Saturday or Monday - Sunday)

Here's my question...

If the user HAS NOT entered any information for a particular day I still want to print out the "date" (ie: Sunday, December 25, 2005)

If the user HAS entered data then I simply display the information in the appropriate date.

How do I go about printing the days the user has not entered any information.
Is there some sort of calendar function I should be using to have this "calendar"?

Not sure if having 2 separate tables (a calendar table and the user entered data table) is the correct way to go. Feels like something should exist to handle the "calendar" piece of it...

Much appreciate the input!

devinemke · Dec 27, 2005

a very simple example (just guessing here at your data structure)

$today = date('l');

if ($today == 'Monday') {$start = strtotime('today');}
else {$start = strtotime('last Monday');}

if ($today == 'Sunday') {$end = strtotime('today');}
else {$end = strtotime('next Sunday');}

$start_date = date('Y-m-d', $start);
$end_date = date('Y-m-d', $end);

$result = mysql_query("SELECT * FROM table WHERE date BETWEEN '$start_date' AND '$end_date'") or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$array[$row['date']] = $row['text'];}

while ($start <= $end)
{
	$date = date('Y-m-d', $start);
	echo $date . '<br>';
	if (isset($array[$date])) {echo $array[$date]['text'] . '<br>';}
	$start = strtotime($date . '+1 day');
}

Installer · Dec 27, 2005

Or:

I'd put each user entry and its entry date into the same row of a table, along with the user's name. Then do something like this:

echo '<table border="1"><tr>';
$sunday_ts = strtotime('-' . date('w') . ' days');
for ($day = 0; $day < 7; $day++) {
    $current_day = strtotime('+' . $day . ' days', $sunday_ts);
    $date_field = date('Y-m-d', $current_day);
    echo '<td>' . $date_field . '<br />';
    $query = "SELECT * FROM data_table " .
             "WHERE user = '" . $user . "' " .
             "AND entry_date = '" . $date_field . "'";
    $result = mysql_query($query);
    while ($row = mysql_fetch_assoc($result)) {
        echo $row['entry'] . '<br />';
    }
}
echo '</tr></table>';

ndorfnz · Dec 27, 2005

Appreciate the feedback, but I guess my main question is:

My users can start their week on Sunday or Monday. Given this variable I am planning on having 2 separate calendar tables. (This is where I am having a gut feeling there is an easier way!) It appears I am going to have store the "calendar" in a table (meaning a MySQL table).

Can I dynamically generate a calendar to print a date without having to store the "dates" in a table (ie: MySQL)

If the user has entered data for the date, pretty simple to print the data from that table including the date.

The issue is if they have NOT, do I still have to utilize a MySQL Table or is there some other way to print this date???

The weekly calendar is such that it contains the <<Previous and Next>>

So a user that starts on Sunday, the week would display Sunday thru Saturday. Clicking the Previous or Next would take the user to the previous week while ONLY display Sunday thru Saturday still.

ie: If the current day is "today" 12/27/2005, the calendar would still start on Sunday, 12/25/2005 and display thru Saturday, 12/31/2005.

So if no data has been entered for say Thursday, Friday or Saturday, the dates still need to appear...

Much appreciate the knowledge and guidance!!!

ndorfnz · Dec 27, 2005

This is the best example I could find:

You can see something is entered on "SUNDAY" but yet the other "days of the week" are still being printed.

http://www.omaharun.org/calendar/week.php?LocationID=&min=1

So what do ya think? Are those other dates stored in a table of some sort or being genereated on the fly?

Thanks!

Installer · Dec 27, 2005

I think that example is probably done (database-wise) the way I outlined. I also think you're trying to make it harder than it is, and that we've given you a pretty good start. As for storing the dates in a table: at the least you're going to need the user's name, the entry, and the date of the entry, else how will you know who, what, and when. As for displaying the calendar, just use the date and time functions to do the heavy lifting.

If you think that makes any sense, run this test script (adding db connection code, of course):

$query = "CREATE TABLE test_data_table (user TEXT, entry_date DATE, entry TEXT)";
$result = mysql_query($query)
    or die('Snafu with query: ' . $query;

$query = "INSERT test_data_table (user, entry_date, entry) " . 
         "VALUES ('A', '2005-12-25', 'entry1'), 
                 ('A', '2005-12-26', 'entry1'), 
                 ('B', '2005-12-25', 'entry1'), 
                 ('A', '2005-12-26', 'entry2'), 
                 ('A', '2005-12-27', 'entry1'), 
                 ('A', '2005-12-28', 'entry1'), 
                 ('C', '2005-12-25', 'entry1'), 
                 ('A', '2005-12-30', 'entry1'), 
                 ('C', '2005-12-25', 'entry1'), 
                 ('A', '2005-12-31', 'entry1'), 
                 ('A', '2005-12-31', 'entry2')"; 
$result = mysql_query($query)
    or die('Snafu with query: ' . $query;

$user = 'A';
echo '<table border="1"><tr>'; 
$sunday_ts = strtotime('-' . date('w') . ' days'); 
for ($day = 0; $day < 7; $day++) { 
    $current_day = strtotime('+' . $day . ' days', $sunday_ts); 
    $date_field = date('Y-m-d', $current_day); 
    echo '<td>' . $date_field . '<br />'; 
    $query = "SELECT entry FROM test_data_table " . 
             "WHERE user = '" . $user . "' " . 
             "AND entry_date = '" . $date_field . "'"; 
    $result = mysql_query($query) 
        or die('Snafu with query: ' . $query;
    while ($row = mysql_fetch_assoc($result)) { 
        echo $row['entry'] . '<br />'; 
    } 
} 
echo '</tr></table>';

Basically it's all right there. Add the other functionality you want one piece at a time.

ndorfnz · Dec 27, 2005

Yes, I agree I am making it harder than it is and my gut tells me that!! :bemused:

Thanks much for your help! I think I just needed to step through it a little slower!

Installer · Dec 27, 2005

This script will give you "Previous/Next" functionality. It's not a pretty display, but that's just a matter of html and/or css.

$query = "DROP TABLE IF EXISTS test_data_table";
$result = mysql_query($query);

$query = "CREATE TABLE test_data_table (user TEXT, entry_date DATE, entry TEXT)";
$result = mysql_query($query)
    or die('Snafu with query: ' . $query);

$query = "INSERT test_data_table (user, entry_date, entry) " . 
         "VALUES ('A', '2005-12-25', 'entry'), 
                 ('A', '2005-12-26', 'entry'), 
                 ('B', '2005-12-25', 'entry'), 
                 ('A', '2005-12-26', 'entry'), 
                 ('A', '2005-12-27', 'entry'), 
                 ('A', '2005-12-28', 'entry'), 
                 ('C', '2005-12-25', 'entry'), 
                 ('A', '2005-12-30', 'entry'), 
                 ('C', '2005-12-25', 'entry'), 
                 ('A', '2005-12-31', 'entry'), 
                 ('A', '2005-12-31', 'entry'), 
                 ('A', '2006-01-06', 'entry'), 
                 ('B', '2006-01-03', 'entry'), 
                 ('A', '2006-01-03', 'entry'), 
                 ('A', '2005-12-11', 'entry'), 
                 ('A', '2005-12-19', 'entry'), 
                 ('A', '2005-12-20', 'entry'), 
                 ('A', '2005-12-24', 'entry'), 
                 ('A', '2006-01-04', 'entry')"; 
$result = mysql_query($query)
    or die('Snafu with query: ' . $query);

$user = 'A';
if (!isset($_GET['base'])) {
    $base_time = time();
} else {
    $base_time = $_GET['base'];
}
echo '<table border="1"><tr>'; 
$sunday_ts = strtotime('-' . date('w', $base_time) . ' days', $base_time); 
for ($day = 0; $day < 7; $day++) { 
    $current_day = strtotime('+' . $day . ' days', $sunday_ts); 
    $date_field = date('Y-m-d', $current_day); 
    echo '<td>' . $date_field . '<br />'; 
    $query = "SELECT entry FROM test_data_table " . 
             "WHERE user = '" . $user . "' " . 
             "AND entry_date = '" . $date_field . "'"; 
    $result = mysql_query($query) 
        or die('Snafu with query: ' . $query);
    while ($row = mysql_fetch_assoc($result)) { 
        echo $row['entry'] . '<br />'; 
    } 
} 
echo '</tr></table><br />';
echo '<a href="' . $_SERVER['PHP_SELF'] . '?base=' . strtotime('-7 days', $base_time) . '">Previous</a><br />';
echo '<a href="' . $_SERVER['PHP_SELF'] . '?base=' . strtotime('+7 days', $base_time) . '">Next</a><br />';

ndorfnz · Dec 27, 2005

Many thanks for your guidance!

I am now in the process of successfully writing my code!

Could you elaborate a little what exactly is happening below:


$sunday_ts = strtotime('-' . date('w', $base_time) . ' days', $base_time); 

and here

$current_day = strtotime('+' . $day . ' days', $sunday_ts);

I looked at the strtotime function, but how you are using it is a little confusing to me! I'm going to modify it to enable start of week to be Monday as well... and then check the user pref within a loop before moving onward.

Again, I cannot say enough how much your input is appreciate!

Calendar in Table or Function to Print?

Nndorfnz

Ddevinemke

IInstaller

Nndorfnz

Nndorfnz

IInstaller

Nndorfnz

IInstaller

Nndorfnz