Jump to content

PHP Course Event Monthly View


FooKelvin

Recommended Posts

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.

 

post-179514-0-46240200-1441270667_thumb.jpg

 

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.

Link to comment
Share on other sites


<?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>

post-3105-0-09116800-1441534256_thumb.png

Link to comment
Share on other sites

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..

Link to comment
Share on other sites

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:

post-179514-0-15694400-1441673472_thumb.jpg

Edited by FooKelvin
Link to comment
Share on other sites

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?

 

post-179514-0-38429700-1441675526_thumb.jpg

Edited by FooKelvin
Link to comment
Share on other sites

  • 4 weeks later...

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.

 

 

 

 

post-179514-0-13933000-1444286561_thumb.png

Link to comment
Share on other sites

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";
}
Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

  • 4 weeks later...

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>";
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.