PHP Course Event Monthly View

FooKelvin · September 3, 2015

Hello, How to show the table in PHP application?

I have 2 database here:

Coursetbl (Consist of all the course)
Eventtbl (Consist of only course that already happen or planning)

So the sample data in Course is this:

+---------------------------------+
|cid  |courseName     |courseSize-|
|---------------------------------|
|c01  |Microsoft Excel|    20     |
|---------------------------------+
|c02  |      PHP      |    20     |
|---------------------------------+
|c03  |Microsoft Word |    20     |
|---------------------------------+
|c04  |      CSS      |    20     |
|---------------------------------+
|c05  |    ASP .NET   |    20     |
|---------------------------------+
|c06  |  JavaScript   |    20     |
|---------------------------------+
|c07  |  Buz Writing  |    20     |
|---------------------------------+

Here is the sample data for Eventtbl data:

+-----+---------------+-----------+-----------+-----------------------
|eid  |courseName     |StartDate  |EndDate    |Remarks               |
|---------------------------------+-----------+----------------------+
|e01  |Microsoft Excel|2015-01-02 |2015-01-05 |Request extra material|
|---------------------------------+-----------+----------------------+
|e02  |Microsoft Excel|2015-01-09 |2015-01-10 |NULL                  |
|---------------------------------+-----------+----------------------+
|e03  |Microsoft Word |2015-01-10 |2015-01-15 |Request extra food    |
|---------------------------------+-----------+----------------------+
|e04  |      CSS      |2015-04-21 |2015-04-25 |NULL                  |
|---------------------------------+-----------+----------------------+
|e06  |  Buz Writing  |2015-05-20 |2015-05-21 |Request extra drinks  |
|---------------------------------+-----------+----------------------
|e07  |Microsoft Excel|2015-05-20 |2015-05-23 |NULL                  |
|---------------------------------+-----------+----------------------+
|e08  |  Buz Writing  |2015-05-22 |2015-05-25 |Request extra trainer |
|---------------------------------+-----------+----------------------+

The course table include all the course, and the event table course will appear when there is an event, and most of them are repeated.

So the output that i plan to do is, have a look the attachment.

For your info :

Nov-Jan = Q1

Feb-Apr = Q2

May-July = Q3

Aug-Oct = Q4

The Report Show Fiscal Year 2015 Report.

If the event didn't happen, the column show empty, if the event is happen, then the column is show the start date. If there is a same event happen in a months, the date should listed down.

Barand · September 3, 2015

There is a solution to similar problem here

http://forums.phpfreaks.com/topic/298003-data-display-in-wrong-column/?do=findComment&comment=1520058

FooKelvin · September 6, 2015

Hi Barand,

is that mean, i need to create another table to store, months? e.g: January, February........December

The event table did have the date, but there is a possibility to have no event in that month.

Barand · September 6, 2015



<?php

$db = new mysqli(HOST,USERNAME,PASSWORD,'test');


$newarray = [11=>[],12=>[],1=>[],2=>[],3=>[],4=>[],5=>[],6=>[],7=>[],8=>[],9=>[],10=>[]];

$data = [];

$trows = '';


/**********************************************************

* store data in array by course

***********************************************************/

$sql = "SELECT c.cid

          , c.courseName

          , e.startDate

          , MONTH(e.startDate) as month

        FROM  course c

        LEFT JOIN eventtbl e USING (cid)

        ORDER BY cid,startDate";

$res = $db->query($sql);

while (list($cid, $cn, $sd, $m) = $res->fetch_row()) {

    if (!isset($data[$cid])) {

        $data[$cid]['name'] = $cn;

        $data[$cid]['events'] = $newarray;

    }

    if ($sd) $data[$cid]['events'][$m][] = date('j/m/Y', strtotime($sd));

}


/**********************************************************

* create table from array data

***********************************************************/

foreach ($data as $cid=>$cdata) {

    $trows .= "<tr><td>$cid</td><td class='cn'>{$cdata['name']}</td>";

    foreach ($cdata['events'] as $dates) {

        $trows .=  "<td class='dt'>" . join('<br>', $dates) . "</td>";

    }

    $trows .= "</tr>\n";

}

?>

<html>

<head>

<title>Example</title>

</head>

<style type='text/css'>

table {

    border-collapse: collapse;

}

tr {

    vertical-align: top;

}

td, th {

    font-family: sans-serif;

    font-size: 9pt;

    padding: 3px;

}

td.cn {

    width: 120px;

}

td.dt {

    width: 70px;

    text-align: right;

}

</style>

<body>

<table border='1'>

<tr>

    <th rowspan='2'>Course</th>

    <th rowspan='2'>Course Name</th>

    <th colspan='3'>Q1</th>

    <th colspan='3'>Q2</th>

    <th colspan='3'>Q3</th>

    <th colspan='3'>Q4</th>

</tr>

<tr>

    <th>November</th><th>December</th><th>January</th>

    <th>February</th><th>March</th><th>April</th>

    <th>May</th><th>June</th><th>July</th>

    <th>August</th><th>September</th><th>October</th>

</tr>

<?=$trows?>

</table>

</body>

</html>

FooKelvin · September 7, 2015

Hi Barand,

I not sure what is the problem, the page show nothing.

$sql = "SELECT SELECT c.cid, c.courseName, e.startDate, MONTH(e.startDate) as month FROM  course c LEFT JOIN evtTable e ON c.cid=e.cid ORDER BY cid,startDate";
$stmt = sqlsrv_query($conn, $sql);

while (list($cid, $cn, $sd, $m) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$cid])) {
        $data[$cid]['name'] = $cn;
        $data[$cid]['events'] = $newarray;
    }
    if ($sd) $data[$cid]['events'][$m][] = date('j/m/Y', strtotime($sd));
}

/**********************************************************
* create table from array data
***********************************************************/
foreach ($data as $cid=>$cdata) {
    $trows .= "<tr><td>$cid</td><td class='cn'>{$cdata['name']}</td>";
    foreach ($cdata['events'] as $dates) {
        $trows .=  "<td class='dt'>" . join('<br>', $dates) . "</td>";
    }
    $trows .= "</tr>\n";
}

I change the "USING" statement to "ON" statement, because i get "Incorrect syntax near 'USING'." error message.

And i try to change mysql to sqlsrv.

Maybe i have change something wrong. please help..

Barand · September 7, 2015

Datetime functions differ greatly between sqlsrv and mysql. I'd start by looking there.

Check sqlsrv_error array after calling the query

Edited September 7, 2015 by Barand

FooKelvin · September 7, 2015

Oh, there is error on the select statement. after i fixed it, the error gone, the page show nothing.

When i remove the

$newarray = [11=>[],12=>[],1=>[],2=>[],3=>[],4=>[],5=>[],6=>[],7=>[],8=>[],9=>[],10=>[]];
$data = [];
$trows = '';

the output show me,the table body without data.

Barand · September 7, 2015

If your php version < 5.4 change

[]

to

array()

in those lines.

FooKelvin · September 8, 2015

If your php version < 5.4 change

[]

to

array()

in those lines.

Is that what you mean?

$newarray = array(11 => "",12 => "",1 => "",2 => "",3 => "",4 => "",5 => "",6 => "",7 => "",8 => "",9 => "",10 => "");
print_r ($newarray);
$data = array();
$trows = '';

and the output is:

Edited September 8, 2015 by FooKelvin

FooKelvin · September 8, 2015

If your php version < 5.4 change

[]

to

array()

in those lines.

Sorry, should be

$newarray = array(11 => array(),12 => array(),1 => array(),2 => array(),3 => array(),4 => array(),5 => array(),6 => array(),7 => array(),8 => array(),9 => array(),10 => array());
print_r ($newarray);
$data = array();
$trows = '';

Am i Right?

Edited September 8, 2015 by FooKelvin

Barand · September 8, 2015

Does it help if you change

<?=$trows?>

to

<?php echo $trows; ?>

FooKelvin · September 9, 2015

Does it help if you change
<?=$trows?>
to
<?php echo $trows; ?>

Hi Barand,

It's working now. The date display in the correct months in my database, but the format is incorrect.

FooKelvin · September 10, 2015

Hi Barand,

I Found a solution.

if ($sd) $data[$cid]['events'][$m][] = date_format(($sd), 'd/m/y');

Thank You

Barand · September 10, 2015

I always used the "ReturnDatesAsStrings=true" option when connecting to sql server.

FooKelvin · September 10, 2015

wow~ another new thing to learn..thank you

FooKelvin · October 8, 2015

Hi Barand,

I would like to expand to have some function in this table.

For convenience purpose, is good to build a feature to allow user to add the planning start date in the same page.

So, I plan to have a "add" icon inside each of the column. Have a look with the attachment.

Currently i added some code to have an "add" icon.

What i propose is, when user click the "add" icon, a screen will pop up and request for some information, for example,

Course ID, Course Name, Start Date and End Date.

Below are some of the changes:

$trows .=  "<td class='dt'>" . join('<br>', $dates) . "<div class='add_more|$cid'>+</a></div>";

The reason i put in

<div class='add_more|$cid'>

is to make each column to have a course id. But how I to get the months name for each column? The purpose of get the months name is to pass to the pop up screen date picker to allow user to choose date for the particular month only. For example, User click "add" icon for November, so in the pop up screen, the date picker should only allow user to choose November month only.

Barand · October 8, 2015

I'd make use of data attributes.

<div class='add_more' data-id='$cid' data-month='$m'>

FooKelvin · October 8, 2015

Hi Barand,

The inspect element shows:

when i try to

var_dump($m)

it show NULL

Barand · October 8, 2015

I was using "$m" as an example of a month number variable

Barand · October 8, 2015

I've rewritten this section to use the [$m] in the arrays

/**********************************************************
* create table from array data
***********************************************************/
foreach ($data as $cid=>$cdata) {
    $trows .= "<tr><td>$cid</td><td class='cn'>{$cdata['name']}</td>";
    foreach ($cdata['events'] as $m => $dates) {
        $trows .=  "<td class='dt'>" . join('<br>', $dates) . 
                    "<div class='add_more' data-id='$cid' data-month='$m'>+</div></td>";
    }
    $trows .= "</tr>\n";
}

FooKelvin · October 8, 2015

I've rewritten this section to use the [$m] in the arrays

/**********************************************************
* create table from array data
***********************************************************/
foreach ($data as $cid=>$cdata) {
    $trows .= "<tr><td>$cid</td><td class='cn'>{$cdata['name']}</td>";
    foreach ($cdata['events'] as $m => $dates) {
        $trows .=  "<td class='dt'>" . join('<br>', $dates) . 
                    "<div class='add_more' data-id='$cid' data-month='$m'>+</div></td>";
    }
    $trows .= "</tr>\n";
}

Barand Awesome!

Tell me if my explanation wrong,

$cdata['events'] as $m

This statement is convert start date to months?

Then each $m will loop for each column?

Barand · October 8, 2015

The month number was fetched in the original query. For each course we then created an events array where $m was the key and the value was an array of course dates in that month.

if ($sd) $data[$cid]['events'][$m][] = date('j/m/Y', strtotime($sd));

I changed it use the month key and values when looping through the array, instead of just the values.

Originally it was

foreach ($cdata['events'] as $dates)

which I changed to

foreach ($cdata['events'] as $m => $dates)

so we now had the value for the month that we now needed

Edited October 8, 2015 by Barand

FooKelvin · November 5, 2015

Hi Barand,

in this line of code, i try to wrap the each dates in the wrapper, the reason i do that is i wanted to use jquery to check those dates, if the dates is from previous year, the jquery will set it to display:none. But no matter how i change the $date cannot be wrapped in the <div>. or the multiple date wrap in the same <div>

$trows .= "<td class='dt'><div class='dateWrapper'>". join('<br>', $dates) ."</div>";

FooKelvin · November 5, 2015

I think i found a solution

 $trows .= "<td class='dt'><div class='dateWrapper'><a>". join('<a>',$dates) .'</div>'.

Barand · November 5, 2015

Exclude dates that you don't want to display in the original query. Don't select them then hide them.

Sign In

PHP Course Event Monthly View

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information