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
4
Medium Priority
?
284 Views
Last Modified: 2014-08-16
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
Comment
Question by:chaddcarr
  • 3
4 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
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

by:chaddcarr
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

by:
Ray Paseur earned 1200 total points
ID: 40237558
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
 
LVL 111

Expert Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question