Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# How can I calculate a percentage within an array[] across two tables

Posted on 2014-08-02
Medium Priority
284 Views
For each "item_id" listed in the "training_items" table, I need to determine/display an overall completion rate (percentage).

I have two tables:

training_items
- item_id
- item_title

user_training_items
- item_id
- user_id
- date_completed (default is NULL) -- when the user completes the training, it is updated with date()

My thought is, for each item_id in the training_items table, count the number of times that item_id has a NULL date_completed in the user_training_items table and divided it by the total number of times the item_id appears within that table.

I'm hoping someone out there can provide some insight/script on how I can actually pull this off.
0
• 3

LVL 111

Expert Comment

ID: 40237297
Please clarify the calculation you want to do.
Let's say there are three training_items and five user_training_items.
Four of the user_training_items have completed training_item number one.
One of the user_training_items has completed training_item number two.
None have completed training_item number three.

In the aggregate, 67% of the training items have been completed?  Or training_item #0ne is 80% complete?

What would be the answer in this case and why?
0

Author Comment

ID: 40237430
Not all training_items will apply to all users so the number of times the item_id appears in the user_training_items table will differ for each training_item (item_id). I have to produce a list of all item_id's (and associated item_title) from the "training_items" table, then for each of those \$rows or item_id's, and within the user_training_items table, calculate a percentage of completion by dividing the # of date_completed IS NULL records by the total count of records for each item_id.

The intended output of your case (assuming that all 5 users were required to complete each of the 3 training_items) should be a list of all Training Items and the overall organizational completion:

Training Item ID           Training Item Title           Completion Status
001                                     Acceptable Use Policy     80%                                //     4/5
002                                     Telecommute Policy        20%                                //     1/5
003                                     Annual IT Training           0%                                 //     0/0
0

LVL 111

Accepted Solution

Ray Paseur earned 1200 total points
ID: 40237558

Moving parts start about line 150 line.  The strategy is to create an array of objects - one each for each of the training items and to populate these objects with the data needed to create the statistics.

``````<?php // demo/temp_chaddcarr.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Database/MySQL/Q_28489417.html#a40237430

// CREATE TEST DATA
\$training_items = array
( array( 'item_id' => 1, 'item_title' => 'Acceptable' )
, array( 'item_id' => 2, 'item_title' => 'Telecommute' )
, array( 'item_id' => 3, 'item_title' => 'Annual' )
)
;
\$my_date = date('c');
\$user_training_items = array
( array( 'item_id' => 1, 'user_id' => 1, 'date_completed' => \$my_date )
, array( 'item_id' => 2, 'user_id' => 1, 'date_completed' => \$my_date )
, array( 'item_id' => 3, 'user_id' => 1, 'date_completed' => NULL )

, array( 'item_id' => 1, 'user_id' => 2, 'date_completed' => \$my_date )
, array( 'item_id' => 2, 'user_id' => 2, 'date_completed' => NULL )
, array( 'item_id' => 3, 'user_id' => 2, 'date_completed' => NULL )

, array( 'item_id' => 1, 'user_id' => 3, 'date_completed' => \$my_date )
, array( 'item_id' => 2, 'user_id' => 3, 'date_completed' => NULL )
, array( 'item_id' => 3, 'user_id' => 3, 'date_completed' => NULL )

, array( 'item_id' => 1, 'user_id' => 4, 'date_completed' => \$my_date )
, array( 'item_id' => 2, 'user_id' => 4, 'date_completed' => NULL )
, array( 'item_id' => 3, 'user_id' => 4, 'date_completed' => NULL )

, array( 'item_id' => 1, 'user_id' => 5, 'date_completed' => NULL )
, array( 'item_id' => 2, 'user_id' => 5, 'date_completed' => NULL )
, array( 'item_id' => 3, 'user_id' => 5, 'date_completed' => NULL )
)
;

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
\$db_host = "localhost"; // PROBABLY THIS IS OK
\$db_name = "??";
\$db_user = "??";
\$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
\$mysqli = new mysqli(\$db_host, \$db_user, \$db_word, \$db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if (\$mysqli->connect_errno)
{
\$err
= "CONNECT FAIL: "
. \$mysqli->connect_errno
. ' '
. \$mysqli->connect_error
;
trigger_error(\$err, E_USER_ERROR);
}

// CREATING A TABLE FOR OUR TEST DATA
\$sql
=
"
CREATE TEMPORARY TABLE training_items
( id         INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, item_id    INT         NOT NULL DEFAULT 0
, item_title VARCHAR(24) NOT NULL DEFAULT ''
)
"
;
if (!\$res = \$mysqli->query(\$sql))
{
\$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. \$mysqli->errno
. ' ERROR: '
. \$mysqli->error
. ' QUERY: '
. \$sql
;
trigger_error(\$err, E_USER_ERROR);
}

// CREATING A TABLE FOR OUR TEST DATA
\$sql
=
"
CREATE TEMPORARY TABLE user_training_items
( id             INT  NOT NULL AUTO_INCREMENT PRIMARY KEY
, item_id        INT  NOT NULL DEFAULT 0
, user_id        INT  NOT NULL DEFAULT 0
, date_completed DATE NOT NULL DEFAULT 0
)
"
;
if (!\$res = \$mysqli->query(\$sql))
{
\$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. \$mysqli->errno
. ' ERROR: '
. \$mysqli->error
. ' QUERY: '
. \$sql
;
trigger_error(\$err, E_USER_ERROR);
}

foreach (\$training_items as \$arr)
{
\$sql = "INSERT INTO training_items ( item_id, item_title ) VALUES ( '{\$arr["item_id"]}', '{\$arr["item_title"]}' )";
if (!\$res = \$mysqli->query(\$sql))
{
\$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. \$mysqli->errno
. ' ERROR: '
. \$mysqli->error
. ' QUERY: '
. \$sql
;
trigger_error(\$err, E_USER_ERROR);
}
}

foreach (\$user_training_items as \$arr)
{
\$sql = "INSERT INTO user_training_items ( item_id, user_id, date_completed ) VALUES ( '{\$arr["item_id"]}', '{\$arr["user_id"]}', '{\$arr["date_completed"]}' )";
if (!\$res = \$mysqli->query(\$sql))
{
\$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. \$mysqli->errno
. ' ERROR: '
. \$mysqli->error
. ' QUERY: '
. \$sql
;
trigger_error(\$err, E_USER_ERROR);
}
}

// GET EACH OF THE TRAINING ITEMS
\$sql = "SELECT item_id, item_title FROM training_items ORDER BY item_id";
\$res = \$mysqli->query(\$sql);
while (\$row = \$res->fetch_object())
{
\$items[] = \$row;
}

// GET STATISTICS FOR THE ITEMS -- TOTAL REQUIREMENT
foreach (\$items as \$key => \$item)
{
\$sql = "SELECT COUNT(*) AS total FROM user_training_items WHERE item_id = \$item->item_id";
\$res = \$mysqli->query(\$sql);
\$row = \$res->fetch_object();
\$item->total = \$row->total;
\$items[\$key] = \$item;
}

// GET STATISTICS FOR THE ITEMS -- CURRENTLY COMPLETED
foreach (\$items as \$key => \$item)
{
\$sql = "SELECT COUNT(*) AS finis FROM user_training_items WHERE item_id = \$item->item_id AND date_completed > 0";
\$res = \$mysqli->query(\$sql);
\$row = \$res->fetch_object();
\$item->finis = \$row->finis;
\$item->pct   = number_format( (\$item->finis / \$item->total) * 100) . '%';
\$items[\$key] = \$item;
}

// GENERATE STATISTICAL REPORT
foreach (\$items as \$item)
{
echo PHP_EOL . "Item: \$item->item_id, Title: \$item->item_title, Completion: \$item->pct";
}
``````
0

LVL 111

Expert Comment

ID: 40265474
Thanks for the points -- interesting question! ~Ray
0

## Featured Post

Question has a verified solution.

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