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

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.
chaddcarrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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
chaddcarrAuthor Commented:
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
Ray PaseurCommented:
Please see http://iconoun.com/demo/temp_chaddcarr.php

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

// LOADING OUR DATA INTO THE TABLES
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);
    }
}

// LOADING OUR DATA INTO THE TABLES
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";
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Thanks for the points -- interesting question! ~Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.