Jump to content

Foreach array (sum array + condition)


FooKelvin
Go to solution Solved by Barand,

Recommended Posts

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:

post-179514-0-96260500-1480058004_thumb.png

 

Expected Output:

post-179514-0-42930000-1480058055_thumb.png

 

 

Thank You

 

 

Link to comment
Share on other sites

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

  • Solution

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;
}

post-3105-0-29665100-1480156608_thumb.png

Edited by Barand
  • Like 1
Link to comment
Share on other sites

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

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

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