Solved

PHP and MySQL date conversion

Posted on 2007-03-27
3
269 Views
Last Modified: 2013-12-13
Hello,

I have been working on a project and it is about winding down, but I just wanted to add one more thing that has been in the back of my mind for a while, but I just can't figure out the best way to do it.

Basically I have written a database with a front-end in PHP that keeps track of meeting times and attendance from previous meetings for an organization. All the attendance is kept in just a LONG TEXT, and everyone's name is always formatted the same way and just all thrown together, separated by commas, in the database. This makes it easy to find who came to every meeting, and I have set up a page to list out the dates of each meeting any one user attended. It is kind of ugly though and when you go to one-two meetings a week for a year there becomes a lot of information. Basically I want to make it a little bit more visual, and show a sort of calender, and on every day that some person x attended a meeting, it will be obvious by shading the background a different color or something.

For example, take a look at the following code, it is just a bunch of embedded tables in HTML, but it sort of shows the format that I am shooting for. If I made it so if you attended a meeting, it was colored in on that day, someone can quickly see exactly when they went to meetings, and still see the date, and not just have to read a big list of data.

-------------------------------------------------

<HTML>
<HEAD>
<TITLE>Test Page</TITLE>
</HEAD>

<BODY>
<table width="152" height="185" border="1">
    <tr>
      <td width="154"><table width="140" border="0" align="center">
        <tr>
          <td> <div align="center">MONTH</div></td>

        </tr>
        <tr>
          <td><table width="140" border="0">
            <tr>
              <td>S</td>
              <td>M</td>
              <td>T</td>

              <td>W</td>
              <td>R</td>
              <td>F</td>
              <td>S</td>
            </tr>
          </table></td>
        </tr>

      </table></td>
    </tr>
    <tr>
      <td><table width="140" border="0" align="center">
        <tr>
                  <td>1</td>
                  <td>2</td>
                  <td>3</td>

                  <td>4</td>
                  <td>5</td>
          <td>6</td>
                  <td>7</td>
        </tr>
        <tr>
                  <td>8</td>

                  <td>9</td>
                  <td>10</td>
                  <td>11</td>
                  <td>0</td>
          <td>0</td>
                  <td>0</td>

        </tr>
        <tr>
                  <td>0</td>
                  <td>0</td>
                  <td>0</td>
                  <td>0</td>
                  <td>0</td>

          <td>0</td>
                  <td>0</td>
        </tr>
        <tr>
                  <td>0</td>
                  <td>0</td>
                  <td>0</td>

                  <td>0</td>
                  <td>0</td>
          <td>0</td>
                  <td>0</td>
        </tr>
        <tr>
          <td>0</td>

                  <td>0</td>
                  <td>0</td>
                  <td>0</td>
                  <td>0</td>
          <td>0</td>
          <td>0</td>

                </tr>
      </table></td>
    </tr>
</table>
</BODY>
</HTML>

-------------------------------------------------


What I can't figure out is the best way to do this, and especially, if I do it this way, how to line up the days to the proper week for the given month in any year. Does anyone have any suggestions on how to do this? Does anyone know of any other way to make the information display in a easy to read more creative format?

Some useful information, all the attendances and meetings have a timestamp, which is just put in as a string as the UNIX time() function when the attendance is being added to the database. It is not stored as the CURRENT_TIMESTAMP in the MySQL database. It is possible for this to change if need be.

Thanks in advance for any help that you can give.

JB
0
Comment
Question by:simpsons17371
3 Comments
 
LVL 16

Expert Comment

by:AdamRobinson
ID: 18800604
Why not just download one of the many freeware PHP Calendaring modules?  

You should be able to fairly easily integrate one with your MySQL database.
0
 
LVL 14

Accepted Solution

by:
raja_ind82 earned 500 total points
ID: 18800781

Create the table "test" and change the database name and run this code.

calender.php
***************************************
<?php
// Heres our nice array
$year_array = array("2005", "2006", "2007", "2008", "2009", "2010");
$month_array = array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
$day_array = array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat");
$conn = mysql_connect("localhost", "root", "");
mysql_select_db("DBNAME");
//$db = odbc_connect("dataprovider","","");
// Set month to current month and year to current year
$month = date("n");
$year = date("Y");

// If user wants to view a month
if (!empty($_GET["month"]) && is_int(intval($_GET["month"])) && $_GET["month"] >= 1 && $_GET["month"] <= 12)
{
   $month = $_GET["month"];
}

// If user wants to view certain year
if (!empty($_GET["year"]) && is_int(intval($_GET["year"])) && $_GET["year"] >= 0)
{
   $year = $_GET["year"];
}

// Get days in month
$days_in_month = cal_days_in_month(CAL_GREGORIAN, $month, $year);

// Get beginning day
$beg_day = jddayofweek(cal_to_jd(CAL_GREGORIAN, $month, 1, $year), 0);
?>
<html>
<head>
<title></title>
<style type="text/css">
<!--
#calendar {
   border-collapse: collapse;
}

#calheader {
   font-weight: bolder;
   font-size: 24pt;
   text-align: center;
   border: 1px solid gray;
}

#bottom {
   border: 1px solid gray;
   text-align: center;
}

#calendar th {
   background: #CC99FF;
   text-align: center;
   width: 14%;
   border: 1px solid gray;
}


#calendar td {
   width: 14%;
   border: 1px solid gray;
   height: 70px;
}

.nodate {
   background: #CCCCCC;
}

select {
   background: #CCCC99;
}

input.text {
   background: #CCCC99;
}

input.button {
   background: #CCCC99;
   border: 1px outset black;
}
//-->
</style>
</head>
<body>
<table id="calendar" width="600" cellpadding="2">
<tr>
     <td colspan="<?php echo(count($day_array)); ?>" id="calheader"><?php echo($month_array[$month - 1] . " " . $year); ?></td>
</tr>
<tr>
<?php
for ($i = 0;$i < count($day_array);$i++)
{
     echo("<th>" . $day_array[$i] . "</th>");
}
?>
</tr>
<tr>
<?php
for ($i = 0;$i < $beg_day;$i++)
{
   echo("<td class=\"nodate\">&nbsp;</td>");
}

$check_row = 7 - $beg_day;

for ($i = 1;$i <= $days_in_month;$i++)
{
   if ($check_row == 0)
   {
      echo("</tr>\n<tr>");
      $check_row = 7;
   }

   $allevents =checkEvents ("$month/$i/$year");
   $passeddate = "$i-$month-$year";
   $thebg = (count($allevents)>0) ? "#FFFF00" : "";
   $extra="";
   for ($count=0; $count<count($allevents); $count++)
   {
           $extra .= "<a href=\"#\" onclick=\"window.open ('query.php?id=".$allevents[$count]["id"]."','','width=200,height=200,scrollbars=1'); return false;\">".$allevents[$count]["short_desc"]."</a><br>\n";
   }
   echo "<td valign=\"top\" bgcolor=\"$thebg\"><span class=\"date\">$i</span><br />$extra</td>";
   $check_row--;
}

$blocks_left = 7 - (($beg_day + $days_in_month) % 7);

if ($blocks_left != 7)
{
   for ($i = 0;$i < $blocks_left;$i++)
   {
      echo("<td class=\"nodate\"></td>");
   }
}
?>
</tr>
<!-- <tr><td colspan="7" align="center"><a href="add.php">Add New Event</a></td></tr> -->
<tr>
     <td colspan="7" id="bottom">
     <form name="browse" action="<?php echo($_SERVER['PHP_SELF']); ?>" method="get">
     <select name="month">
     <?php
     for ($i=1; $i<=count($month_array); $i++)
     {
          $s = ($i==$month) ? " selected=\"true\"" : "";
          echo "<option value=\"$i\"$s>".$month_array[$i-1]."</option>\n";
     }
     ?>
     </select>&nbsp;
     <select name="year">
     <?php
     for ($i = 1;$i <=count($year_array);$i++)
     {
             $s = ($year_array[$i-1] == $year) ? " selected=\"true\"" : "";
          echo "<option value=\"" . ($year_array[$i-1]) . "\" $s>".$year_array[$i-1]."</option>\n";
     }
     ?>
     </select>&nbsp;
     <input type="submit" class="button" value="Go!">
     </form>
     </td>
</tr>
</table>
</body>
</html>

<?
function checkEvents ($thedate)
{
     global $db;
       $event = array();         
         $thedate=dateconvert($thedate,1);         
     $result = mysql_query("SELECT event_id, short_description FROM test WHERE date='$thedate'") or die('Problem with the SELECT query.');
       $i=0;
     while ($row = mysql_fetch_array($result))
       {
             $event[$i]["id"] = $row["event_id"];
            $event[$i]["short_desc"] = stripslashes($row["short_description"]);
            $i++;
       }
     
     return $event;
}

function dateconvert($date,$func)
{
            if ($func == 1)
            { //insert conversion
                  list($month,$day, $year) = split('[/.-]', $date);
                  $date = "$year-$month-$day";
                  return $date;
            }
            if ($func == 2)
            { //output conversion
                  list($year, $month, $day) = split('[-.]', $date);
                  $date = "$month/$day/$year";
                  return $date;
            }            
}

?>

use this table structure for the description displayed in the calender:
CREATE TABLE `test` (
  `event_id` int(11) NOT NULL,
  `short_description` varchar(100) NOT NULL,
  `date` date NOT NULL
)

Regards,
M.Raja
0
 
LVL 1

Author Comment

by:simpsons17371
ID: 18803510
Thanks M. Raja for all of that.

With just a little bit of modification that is going to be exactly what I was looking for.

JB
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compute age Html 2 23
Making Table Thru ASP Response.write 5 19
How to pass values to HTML-5 attribute dynamically? 24 44
Why is my $_POST not going to results page 10 32
This article discusses how to create an extensible mechanism for linked drop downs.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will learn how to count occurrences of each item in an array.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question