create a calendar with month view

[The_FedEx_Guy]

Hi,

I would like to know how I can create a calendar month view and populate my records into the relevant boxes.

My database:

CREATE TABLE IF NOT EXISTS `jobs` (
  `job_id` int(8) NOT NULL auto_increment,
  `job_day` varchar(10) collate utf8_unicode_ci NOT NULL,
  `job_date` varchar(10) collate utf8_unicode_ci NOT NULL,
  `job_start_time` varchar(10) collate utf8_unicode_ci NOT NULL,
  `job_finish_time` varchar(10) collate utf8_unicode_ci NOT NULL,
  `job_gross_hrs` varchar(6) collate utf8_unicode_ci NOT NULL,
  `job_break_hrs` varchar(6) collate utf8_unicode_ci NOT NULL,
  `job_total_hrs` varchar(6) collate utf8_unicode_ci NOT NULL,
  `job_job_role` varchar(255) collate utf8_unicode_ci NOT NULL,
  `job_got_signature` varchar(5) collate utf8_unicode_ci default NULL,
  `job_mileage` varchar(20) collate utf8_unicode_ci default NULL,
  `client_id` varchar(8) collate utf8_unicode_ci NOT NULL,
  `staff_id` varchar(8) collate utf8_unicode_ci default NULL,
  PRIMARY KEY  (`job_id`),
  KEY `client_id` (`client_id`),
  KEY `staff_id` (`staff_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

I know my job_date is not in DATE format, that is due to my lack of knowledge of how to use DATE functions

I would ideally like to use a grid to display the date and the jobs within it.

[David_Powers]

The_FedEx_Guy wrote:

I know my job_date is not in DATE format, that is due to my lack of knowledge of how to use DATE functions

If you want to build calendar functionality, you need to learn how to use the DATE data type and related functions. Otherwise, you're just wasting your time.

[The_FedEx_Guy]

Ok, so if changed my date column to DATE

And the dates are stored as YYYY/MM/DD

How would I go about creating the month view for the current month?

Is there a good book or website I can go through?

[David_Powers]

SELECT * FROM mytable

WHERE MONTH(job_date) = MONTH(NOW())

A good place to learn about the MySQL date and time functions is in the online manual: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html.

[The_FedEx_Guy]

I have changed the column format to DATE

looks good, but the dates are not showing up in the format I choose:

<?php echo date("d-m-Y", $row_jobs_list_mon_mon['job_date']); ?>

Humans do not find the YYYY-MM-DD format very clear.

[David_Powers]

The_FedEx_Guy wrote:

the dates are not showing up in the format I choose:

<?php echo date("d-m-Y", $row_jobs_list_mon_mon['job_date']); ?>

That's because the PHP date() function requires a Unix timestamp. MySQL dates are stored in YYYY-MM-DD format.

You need to use the MySQL DATE_FORMAT() function to format your dates. The URL I gave you before has the full details, but the way to get your date formatted the way you want is this:

SELECT *, DATE_FORMAT(job_date, '%d-%m-%Y') AS formatted_date

FROM mytable

WHERE MONTH(job_date) = MONTH(NOW())

The formatted date will be in your recordset as $row_jobs_list_mon_mon['formatted_date'].

Humans do not find the YYYY-MM-DD format very clear.

Correction: SOME humans don't find it clear. YYYY-MM-DD is the normal way of presenting the date in China and Japan (and possibly other countries). The reason it's used by MySQL is because it's the ISO recommended format (largest unit first, smallest last). If you visit the MySQL date and time functions page I pointed to earlier, you'll see that you can format the date in many different ways with DATE_FORMAT(). Once you make the effort to learn about dates in MySQL, you'll find them very convenient.

[Lon_Winters]

I would also suggest to grab on of the many PHP open source calendar apps that are out there - you may end up using one, or, at least you can see how they do it to give you some ideas on how it's done.

[The_FedEx_Guy]

Hi,

Thanks, I have already got one, looks good but I cant seem to populate the correct day's jobs into the correct calendar square.

I'm using this:

http://www.phptoys.com/e107_plugins/content/content.php?content.33.3

[The_FedEx_Guy]

have come up with this:

But I have a problem, the client name keeps going into every box.

What have I doen wrong?

<?php require_once('../Connections/db.php'); ?>
<?php

mysql_select_db($database_db, $db);
$query_jobs_grid = "SELECT * FROM jobs LEFT JOIN clients ON jobs.client_id = clients.client_id LEFT JOIN staff ON jobs.staff_id = staff.staff_id WHERE MONTH(job_date) = MONTH(NOW())";
$jobs_grid = mysql_query($query_jobs_grid, $db) or die(mysql_error());
$row_jobs_grid = mysql_fetch_assoc($jobs_grid);
$totalRows_jobs_grid = mysql_num_rows($jobs_grid);
?>
<?

$output = '';
$month = $_GET['month'];
$year = $_GET['year'];
   
if($month == '' && $year == '') {
    $time = time();
    $month = date('n',$time);
    $year = date('Y',$time);
}

$date = getdate(mktime(0,0,0,$month,1,$year));
$today = getdate();
$hours = $today['hours'];
$mins = $today['minutes'];
$secs = $today['seconds'];
$job_date = $row_jobs_grid['job_date'];

if(strlen($hours)<2) $hours="0".$hours;
if(strlen($mins)<2) $mins="0".$mins;
if(strlen($secs)<2) $secs="0".$secs;

$days=date("t",mktime(0,0,0,$month,1,$year));
$start = $date['wday']+1;
$name = $date['month'];
$year2 = $date['year'];
$offset = $days + $start - 1;

if($month==12) {
    $next=1;
    $nexty=$year + 1;
} else {
    $next=$month + 1;
    $nexty=$year;
}

if($month==1) {
    $prev=12;
    $prevy=$year - 1;
} else {
    $prev=$month - 1;
    $prevy=$year;
}

if($offset <= 28) $weeks=28;
elseif($offset > 35) $weeks = 42;
else $weeks = 35;

$output .= "
<table class='cal' cellspacing='1'>
<tr>
    <td colspan='7'>
        <table class='calhead'>
        <tr>
            <td>
                <a href='javascript:navigate($prev,$prevy)'><img src='calLeft.gif'></a> <a href='javascript:navigate(\"\",\"\")'><img src='calCenter.gif'></a> <a href='javascript:navigate($next,$nexty)'><img src='calRight.gif'></a>
            </td>
            <td align='right'>
                <div>$name $year2</div>
            </td>
        </tr>
        </table>
    </td>
</tr>
<tr class='dayhead'>
    <td>Sun</td>
    <td>Mon</td>
    <td>Tue</td>
    <td>Wed</td>
    <td>Thu</td>
    <td>Fri</td>
    <td>Sat</td>
</tr>";

$col=1;
$cur=1;
$next=0;

for($i=1;$i<=$weeks;$i++) {
    if($next==3) $next=0;
    if($col==1) $output.="<tr class='dayrow'>";
     
    $output.="<td valign='top' onMouseOver=\"this.className='dayover'\" onMouseOut=\"this.className='dayout'\">";

    if($i <= ($days+($start-1)) && $i >= $start) {
        $output.="<div class='day'><b";

        if(($cur==$today[mday]) && ($name==$today[month]) && ($job_date==$today[$date])) $output.=" style='color:#C00'";

        $output.=">$cur </b><br>" .$row_jobs_grid['client_name']."</div></td>";

        $cur++;
        $col++;
       
    } else {
        $output.=" </td>";
        $col++;
    } 
       
    if($col==8) {
        $output.="</tr>";
        $col=1;
    }
}

$output.="</table>";
 
echo $output;

?>

<?php
mysql_free_result($jobs_grid);
?>

[The_FedEx_Guy]

I've understood the DATE method but I cant put it in a calendar format

[The_FedEx_Guy]

hi, is there another solution instead of dynamically creating the calendar?

As I cant seem to add any information to it.