Foreach array (sum array + condition)

FooKelvin · November 25, 2016

Hi,

I have a list of tracking, separate by module. Example

Certificate 1
- MS1
- MS2
- MS3
- MS4
- MS5
- MS6
Certificate 2
- MS7
- MS8
- MS9
- MS10
- MS11
- MS12

Each module have their own scoring. There is also another filed to track the status.

In order to entitle for the certificate 1, they need to pass all the module under certificate 1. the passing condition >= 50.

Eg: In order to get Certificate 1, the marks of MS1 - MS6 should >=50.

What i facing now,

i failed to sum the marks
the status of certificate display in the wrong column.

here is my code:

$newarray = array(1 => array(), 2 => array(), 3 => array(), 4 => array(), 5 => array(), 6 => array(), 7 => array(), 8 => array(), 9 => array(), 10 => array(), 11 => array(), 12 => array());
$data = array();
$trows = '';
$sql = "SELECT  eid,[Name]
      ,[course]
      ,[marks]
      ,[id]
      ,[courselvl]
      FROM [scview]";

$stmt = sqlsrv_query($conn, $sql);

while (list($eid, $name, $course, $marks, $id, $courselvl,$a) = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    if (!isset($data[$eid])) {
        //$data[$eid][$qid] = $newArray2;
        $data[$eid]['name'] = $name;
        $data[$eid]['module'] = $newarray;
    }
    $item = $marks; 
    $data[$eid]['module'][$id][] = $item;
}
foreach ($data as $id => $mdata) {
    $trows .= "<tr><td>{$mdata['name']}</td>";
    foreach ($mdata['module'] as $id => $marks) {
        $trows .= "<td>" . join(';<br>', $marks) . "</td>";
    }
    $trows .="<td>Entitle For Cert 1</td>";
    $trows .="<td>Entitle For Cert 2</td>";
    $trows .= "</tr>\n";
};
?>
<table border='1' id="example" style="width:100%">
    <thead> 
        <tr>
            <th>Name</th>
            <th>MS1</th>
            <th>MS2</th>
            <th>MS3</th>
            <th>MS4</th>
            <th>MS5</th>
            <th>MS6</th>
            <th>Certificate</th>
            <th>MS7</th>
            <th>MS8</th>
            <th>MS9</th>
            <th>MS10</th>
            <th>MS11</th>
            <th>MS12</th>
             <th>Certificate2</th>
        </tr>
    </thead>              
    <tbody>
        <?php echo $trows; ?>
    </tbody>
</table>
</body>

Here is my DB. I have 2 DB, and i join them.

course table:

id	course
1	MS1
2	MS2
3	MS3
4	MS4
5	MS5
6	MS6
7	MS7
8	MS8
9	MS9
10	MS10
11	MS11
12	MS12

enrollment table:

Name	course	marks	eid
Lim	MS7	40	e2
Lim	MS8	50	e2
Lim	MS9	66	e2
Lim	MS10	76	e2
Lim	MS11	56	e2
Lim	MS12	55	e2
Lim	MS1	40	e2
Lim	MS2	50	e2
Lim	MS3	66	e2
Lim	MS4	76	e2
Lim	MS5	56	e2
Lim	MS6	55	e2
Foo	MS1	60	e1
Foo	MS2	60	e1
Foo	MS3	76	e1
Foo	MS4	76	e1
Foo	MS5	86	e1
Foo	MS6	75	e1
Foo	MS7	30	e1
Foo	MS8	30	e1
Foo	MS9	36	e1
Foo	MS10	36	e1
Foo	MS11	66	e1
Foo	MS12	65	e1

After Join table, and i run the query Output:

Name	course	marks	id	courselvl	eid
Lim	MS7	40	7	MS7	e2
Lim	MS8	50	8	MS8	e2
Lim	MS9	66	9	MS9	e2
Lim	MS10	76	10	MS10	e2
Lim	MS11	56	11	MS11	e2
Lim	MS12	55	12	MS12	e2
Lim	MS1	40	1	MS1	e2
Lim	MS2	50	2	MS2	e2
Lim	MS3	66	3	MS3	e2
Lim	MS4	76	4	MS4	e2
Lim	MS5	56	5	MS5	e2
Lim	MS6	55	6	MS6	e2
Foo	MS1	60	1	MS1	e1
Foo	MS2	60	2	MS2	e1
Foo	MS3	76	3	MS3	e1
Foo	MS4	76	4	MS4	e1
Foo	MS5	86	5	MS5	e1
Foo	MS6	75	6	MS6	e1
Foo	MS7	30	7	MS7	e1
Foo	MS8	30	8	MS8	e1
Foo	MS9	36	9	MS9	e1
Foo	MS10	36	10	MS10	e1
Foo	MS11	66	11	MS11	e1
Foo	MS12	65	12	MS12	e1

###############################################################################################

The output that display in browser:

Expected Output:

Thank You

Barand · November 25, 2016

I would have expected your course data to contain a reference to the certificate level (1 or 2)

FooKelvin · November 26, 2016

I would have expected your course data to contain a reference to the certificate level (1 or 2)

Hi Barand,

You mean

Certificate 1>MS1-MS6

Certificate 2 >MS7-MS12

?

Edited November 26, 2016 by FooKelvin

Barand · November 26, 2016

Noooo! That is not the way to store data in a RDBMS.

I mean like this, normalizing your data

mysql> select * from course;
+-----------+--------+-----------+
| course_id | course | certlevel |
+-----------+--------+-----------+
|         1 | MS1    |         1 |
|         2 | MS2    |         1 |
|         3 | MS3    |         1 |
|         4 | MS4    |         1 |
|         5 | MS5    |         1 |
|         6 | MS6    |         1 |
|         7 | MS7    |         2 |
|         8 | MS8    |         2 |
|         9 | MS9    |         2 |
|        10 | MS10   |         2 |
|        11 | MS11   |         2 |
|        12 | MS12   |         2 |
+-----------+--------+-----------+

mysql> select * from student;
+------------+------+
| student_id | name |
+------------+------+
|          1 | foo  |
|          2 | lim  |
+------------+------+

mysql> select * from enrolment;
+--------------+------------+-----------+-------+
| enrolment_id | student_id | course_id | marks |
+--------------+------------+-----------+-------+
|            1 |          2 |         7 |    40 |
|            2 |          2 |         8 |    50 |
|            3 |          2 |         9 |    66 |
|            4 |          2 |        10 |    76 |
|            5 |          2 |        11 |    56 |
|            6 |          2 |        12 |    55 |
|            7 |          2 |         1 |    40 |
|            8 |          2 |         2 |    50 |
|            9 |          2 |         3 |    66 |
|           10 |          2 |         4 |    76 |
|           11 |          2 |         5 |    56 |
|           12 |          2 |         6 |    55 |
|           13 |          1 |         1 |    60 |
|           14 |          1 |         2 |    60 |
|           15 |          1 |         3 |    76 |
|           16 |          1 |         4 |    76 |
|           17 |          1 |         5 |    86 |
|           18 |          1 |         6 |    75 |
|           19 |          1 |         7 |    30 |
|           20 |          1 |         8 |    30 |
|           21 |          1 |         9 |    36 |
|           22 |          1 |        10 |    36 |
|           23 |          1 |        11 |    66 |
|           24 |          1 |        12 |    65 |
+--------------+------------+-----------+-------+


SELECT s.name
 , c.certlevel
 , c.course
 , e.marks
FROM student s 
    LEFT JOIN 
 enrolment e USING (student_id)
 LEFT JOIN
 course c USING (course_id)
ORDER BY e.student_id, c.certlevel, e.course_id;

+------+-----------+--------+-------+
| name | certlevel | course | marks |
+------+-----------+--------+-------+
| foo  |         1 | MS1    |    60 |
| foo  |         1 | MS2    |    60 |
| foo  |         1 | MS3    |    76 |
| foo  |         1 | MS4    |    76 |
| foo  |         1 | MS5    |    86 |
| foo  |         1 | MS6    |    75 |
| foo  |         2 | MS7    |    30 |
| foo  |         2 | MS8    |    30 |
| foo  |         2 | MS9    |    36 |
| foo  |         2 | MS10   |    36 |
| foo  |         2 | MS11   |    66 |
| foo  |         2 | MS12   |    65 |
| lim  |         1 | MS1    |    40 |
| lim  |         1 | MS2    |    50 |
| lim  |         1 | MS3    |    66 |
| lim  |         1 | MS4    |    76 |
| lim  |         1 | MS5    |    56 |
| lim  |         1 | MS6    |    55 |
| lim  |         2 | MS7    |    40 |
| lim  |         2 | MS8    |    50 |
| lim  |         2 | MS9    |    66 |
| lim  |         2 | MS10   |    76 |
| lim  |         2 | MS11   |    56 |
| lim  |         2 | MS12   |    55 |
+------+-----------+--------+-------+

Barand · November 26, 2016

This uses the above model to produce your table output

<?php
// PDO connection
$db = pdoConnect('foo');

$sql = "SELECT course,certlevel
        FROM course
        ORDER BY certlevel,course_id";
$res = $db->query($sql);
$courses = [];
foreach ($res as $crs) {
    $courses[ $crs['certlevel'] ][] = $crs['course'];
}
// build table heading and empty table row array
$thead = "<tr><th>Name</th>";
$newarray = [];
foreach ($courses as $cert=>$crss) {
    $cclass = "L$cert";
    $thead .= "<th class='$cclass'>" . join("</th><th class='$cclass'>",$crss) . "</th><th class='$cclass'>Certificate $cert</th>";
    foreach ($crss as $cname) {
        $newarray[$cert][$cname] = 0;
    }
}
$thead .= "</tr>\n";

// get the student marks
$sql = "SELECT s.name
         , c.course
         , c.certlevel
         , e.marks
        FROM student s 
            LEFT JOIN 
            enrolment e USING (student_id)
            LEFT JOIN
            course c USING (course_id)
        ORDER BY e.student_id, c.certlevel, e.course_id";
$res = $db->query($sql);
$prevname='';
$tdata = '';
$studata = $newarray;
foreach ($res as $row) {
    if ($row['name'] != $prevname) {
        if ($prevname) {
            $tdata .= "<tr><td>$prevname</td>";
            foreach ($studata as $cert=>$marks) {
                $cclass = "L$cert";
                $certres = min($marks) >= 50 ? "Entitle for Cert $cert" : 'Complete only';
                foreach ($marks as $m) {
                    $tdata .= "<td class='$cclass'>$m</td>";
                }
                $tdata .= "<td class='cert$cclass'>$certres</td>";
            }
            $tdata .= "</tr>\n";
        }
        $studata = $newarray;
        $prevname = $row['name'];
    }
    $studata[$row['certlevel']][$row['course']] = $row['marks'];
}
// last student
$tdata .= "<tr><td>$prevname</td>";
foreach ($studata as $cert=>$marks) {
    $cclass = "L$cert";
    $certres = min($marks) >= 50 ? "Entitle for Cert $cert" : 'Complete only';
    foreach ($marks as $m) {
        $tdata .= "<td class='$cclass'>$m</td>";
    }
    $tdata .= "<td class='cert$cclass'>$certres</td>";
}
$tdata .= "</tr>\n";
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <style type="text/css">
        body {
            font-family: sans-serif;
            font-size: 10pt;
        }
        table {
            border-collapse: collapse;
        }
        td, th {
            border: 1px solid #888;
            padding: 3px;
            min-width: 40px;
        }
        td.L1 {
            background-color: #ccf;
            text-align: right;
        }
        td.L2 {
            background-color: #fcc;
            text-align: right;
        }
        td.certL1 {
            background-color: #ccf;
            font-weight: 600;
        }
        td.certL2 {
            background-color: #fcc;
            font-weight: 600;
        }
        th.L1 {
            background-color: #aaf;
        }
        th.L2 {
            background-color: #faa;
        }
    </style>
</head>
<body>
    <table>
        <thead>
            <?=$thead?>
        </thead>
        <tbody>
            <?=$tdata?>
        </tbody>
    </table>
</body>
</html>

NOTE The pdo connect function (in an included file) is

define("HOST",'localhost');
define("USERNAME",'*****************');
define("PASSWORD",'*****************');

function pdoConnect($dbname) 
{
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname",USERNAME,PASSWORD);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    return $db;
}

Edited November 26, 2016 by Barand

FooKelvin · November 29, 2016

Thanks Barand, its working perfectly.

I have change on the condition and this more reality.

For the certification level, the level 2 certificate should not entitle if the student fail to entitle certificate level 1.

meaning,

in order to get cert level 1,

student have to pass MS1 until MS6

in order to get cert level 2,

student have to pass MS1 until MS9

To do this, i should check thru each row array?

or can it be like check get Certificate 1 (complete only or Entitle for cert1) column value to check?

Barand · November 29, 2016

I did wonder if that were the case.

Replace

$certres = min($marks) >= 50 ? "Entitle for Cert $cert" : 'Complete only';

with

    $certres = "Entitle for Cert $cert";
    for ($lvl=1; $lvl<=$cert; $lvl++) {
        if (min($studata[$lvl]) < 50) {
            $certres = 'Complete only';
            break;
        }
    }

That will check previous levels and not just the current level (NOTE this code occurs twice - inside the loop and after the loop to output the final student)

Edited November 29, 2016 by Barand
typo

FooKelvin · November 30, 2016

Hi Barand,

am i missed out anything? because the output show "completed only"

I tried to understand the for loop :

                $certres = "Entitle for Cert $cert";
                for ($lvl = 1; $lvl <= $cert; $lvl++) {
                    echo "cert:".$cert; //<-- new added
                    echo "level:".$lvl; //<-- new added
                    if (min($studata[$lvl]) < 50) {
                        $certres = 'Complete only';
                        break;
                    }
                }

i found that :

cert:1 level:1cert:2 level:1

FooKelvin · November 30, 2016

Hi Barand,

Sorry. i found where is going wrong. Your code words perfectly. there is because my data type using nvarchar, so the array include a lot of spaces.

thanks again Barand.

Sign In

Foreach array (sum array + condition)

Recommended Posts

FooKelvin

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

FooKelvin

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

FooKelvin

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

FooKelvin

Link to comment

Share on other sites

FooKelvin

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information