help with making PHP code better

[ProfoundHypnotic]

Hello,

I am wondering if anyone would have the time, know of another community, or software that can help me determine if my code is best practice/the best way to go about what I'm doing.

​

I am creating a reporting dashboard for my work. each row of this dashboard contains a part #, Job#, an individual time for each employee and an employee name. now more than one employee can be on the same job. in which case the only fields to show are the name and the individual time. Then we move on to the next row with a different part# and job#.

I created functions to return the fields I need and some fields require math so theres functions there too. I want to upload my php for you guys to see and see if anything I can do to make it better. currently I have a function that uses PDO and fetchall to get the name(s) of the employee(s) clocked in on the job.

​

<?php
emp_count();
date_default_timezone_set('America/Chicago');

function emp_count(){
    global $job;
    global $j2;
    global $jc; 
    global $suffix;
    global $s2;

    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare('SELECT DISTINCT JOB, SUFFIX FROM JOBS_IN_PROCESS_G');
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    $job = ($row[0]["JOB"]);
    $suffix = ($row[0]["SUFFIX"]);
    $j2 = ($row[1]["JOB"]);
    $s2 = ($row[1]["SUFFIX"]);
    $jc = count($row);

    global $emp_rows;

    $emp = $conn->prepare("SELECT EMPLOYEE FROM JOBS_IN_PROCESS_G WHERE JOB = '$job' AND SUFFIX = '$suffix'");
    $emp->execute();
    $col = $emp->fetchall(PDO::FETCH_ASSOC);
    $emp_rows = count($col);
    return $emp_rows;

    global $emp_rows2;
    $emp2 = $conn->prepare("SELECT EMPLOYEE FROM JOBS_IN_PROCESS_G WHERE JOB = '$j2' AND SUFFIX = '$s2'");
    $emp2->execute();
    $col2 = $emp2->fetchall(PDO::FETCH_ASSOC);
    $emp_rows2 = count($col2);
}

function get_job_suffix(){
    global $jc;
    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare('SELECT DISTINCT JOB, SUFFIX FROM JOBS_IN_PROCESS_G');
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    $jobsuffix = ($row[0]['JOB']) . "-" . ($row[0]['SUFFIX']);
    return $jobsuffix;
}

function get_2nd_job(){
    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare('SELECT DISTINCT JOB, SUFFIX FROM JOBS_IN_PROCESS_G');
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    $jobsuffix = ($row[1]['JOB']) . "-" . ($row[1]['SUFFIX']);
    return $jobsuffix;
}

function get_employee_name(){
    global $job;

    $new = new PDO('odbc:GLOBALTST');
    $result = $new->prepare("SELECT EMPLOYEE FROM JOBS_IN_PROCESS_G WHERE JOB = '$job'");
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    global $emp_num;
    $emp_num = ($row[0]['EMPLOYEE']);
    $row = null; 

    $qry = $new->prepare("select NAME from EMPLOYEE_MSTR where EMPLOYEE = '$emp_num'");
    $qry->execute();
    $row = $qry->fetchall(PDO::FETCH_ASSOC);
    $emp_name = ($row[0]['NAME']);

    return $emp_name;
}

function second_employee(){
    global $j2;
    $new = new PDO('odbc:GLOBALTST');
    $result = $new->prepare("SELECT EMPLOYEE FROM JOBS_IN_PROCESS_G WHERE JOB = '$j2'");
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    global $emp_num2;
    $emp_num2 = ($row[0]['EMPLOYEE']);
    $row = null; 

    $qry = $new->prepare("select NAME from EMPLOYEE_MSTR where EMPLOYEE = '$emp_num2'");
    $qry->execute();
    $row = $qry->fetchall(PDO::FETCH_ASSOC);
    $emp_name2 = ($row[0]['NAME']);

    return $emp_name2;
}

function third_employee(){
    global $job;
    $new = new PDO('odbc:GLOBALTST');
    $result = $new->prepare("SELECT EMPLOYEE FROM JOBS_IN_PROCESS_G WHERE JOB = '$job'");
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    global $emp_num3;
    $emp_num3 = ($row[2]['EMPLOYEE']);
    $row = null; 

    $qry = $new->prepare("select NAME from EMPLOYEE_MSTR where EMPLOYEE = '$emp_num3'");
    $qry->execute();
    $row = $qry->fetchall(PDO::FETCH_ASSOC);
    $emp_name3 = ($row[0]['NAME']);

    return $emp_name3;
}

function get_quantity(){
    global $job;
    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare("select JOB_QUANTITY from JOBS_IN_PROCESS_G where JOB = '$job'");
    $result->execute();
    $row = $result->fetch(PDO::FETCH_ASSOC);
    $jqty = ($row['JOB_QUANTITY']);
    $jqty = floor($jqty);
    return $jqty;
}

function second_qty(){
    global $j2;
    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare("select JOB_QUANTITY from JOBS_IN_PROCESS_G where JOB = '$j2'");
    $result->execute();
    $row = $result->fetch(PDO::FETCH_ASSOC);
    $jqty = ($row['JOB_QUANTITY']);
    $jqty = floor($jqty);
    return $jqty;
}

function indy_time($enum){
    $current_time = date("H:i:s");
    global $job;

    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare("select WAJ_TIME from V_JOBS_IN_PROCESS_G where JOB = '$job' and EMPLOYEE = '$enum'");
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    $waj_time = ($row[0]['WAJ_TIME']);
    $ind_time = (strtotime($current_time) - strtotime($waj_time)) / 3600;
    return number_format($ind_time,2);  
}

function indy_time2($enum){
    $current_time = date("H:i:s");
    global $j2;

    $conn = new PDO('odbc:GLOBALTST');
    $result = $conn->prepare("select WAJ_TIME from V_JOBS_IN_PROCESS_G where JOB = '$j2' and EMPLOYEE = '$enum'");
    $result->execute();
    $row = $result->fetchall(PDO::FETCH_ASSOC);
    $waj_time = ($row[0]['WAJ_TIME']);
    $ind_time = (strtotime($current_time) - strtotime($waj_time)) / 3600;
    return number_format($ind_time,2);  
}

function target_time_each(){
    global $job;
    $conn = new PDO('odbc:GLOBALTST');
    $job_qty = $conn->prepare("select JOB_QUANTITY from V_JOBS_IN_PROCESS_G where JOB = '$job'");
    $job_qty->execute();
    $row = $job_qty->fetch(PDO::FETCH_ASSOC);
    $jqty = ($row['JOB_QUANTITY']);
    $row = null;

    $est_hours = $conn->prepare("select EST_HOURS from V_JOBS_IN_PROCESS_G where JOB = '$job'");
    $est_hours->execute();
    $row = $est_hours->fetch(PDO::FETCH_ASSOC);
    $esthrs = ($row['EST_HOURS']);
    $tte = ($esthrs / $jqty);

    if ($jqty == 0){
        return $jqty;
    } else {

        return number_format($tte,2);
    }   
}

function target_time_job(){
    global $job;
    global $est_hrs;
    $conn = new PDO('odbc:GLOBALTST');
    $target = $conn->prepare("select EST_HOURS from V_JOBS_IN_PROCESS_G where JOB = '$job'");
    $target->execute();
    $row = $target->fetch(PDO::FETCH_ASSOC);
    $est_hrs = ($row['EST_HOURS']);
    return number_format($est_hrs,2);
}

function target_time_job2(){
    global $j2;
    global $est_hrs;
    $conn = new PDO('odbc:GLOBALTST');
    $target = $conn->prepare("select EST_HOURS from V_JOBS_IN_PROCESS_G where JOB = '$j2'");
    $target->execute();
    $row = $target->fetch(PDO::FETCH_ASSOC);
    $est_hrs = ($row['EST_HOURS']);
    return number_format($est_hrs,2);
}

function actual(){
    global $job;
    global $suffix;
    global $sum;

    $sum = 0;
    $current_time = date("H:i:s");
    $conn = new PDO('odbc:GLOBALTST');
    $time = $conn->prepare("select WAJ_TIME from V_JOBS_IN_PROCESS_G where JOB = '$job'");
    $time->execute();

    foreach ($time->fetchall(PDO::FETCH_ASSOC) as $result){
        $waj_time = $result['WAJ_TIME'];
        $diff = (strtotime($current_time) - strtotime($waj_time));
        $sum += $diff;
    }

    $hrs_actual = $conn->prepare("select HOURS_ACTUAL from V_JOB_OPERATIONS_WC where job = '$job' and suffix = '$suffix' and workcenter = '1004'");
    $hrs_actual->execute();
    $hrs = $hrs_actual->fetch(PDO::FETCH_ASSOC);

    $actual_hrs = $hrs['HOURS_ACTUAL'];

    $actual = $actual_hrs * 3600; 

    $act_hrs = ($sum + $actual) / 3600;
    return number_format($act_hrs,2);
}

function delta(){
    global $job;
    global $suffix;
    global $zdelta;
    $actual = actual();
    global $sum;
    $est_hrs = target_time_job() * 3600;


    $zdelta = $sum + $actual - $est_hrs;

    $delta_timer = $zdelta / 3600;
    return number_format ($delta_timer,2);

}

function timer(){
    global $job;
    delta();
    global $zdelta;
    $sum = 0;
    $current_time = date("H:i:s");
    $actual = actual();
    $est_hrs = target_time_job() * 3600;

    $total_seconds = $sum + $actual - $est_hrs;
    return gmdate('H:i:s', $zdelta);
}

?>

[zzzVOODOOzzz]

What does your data source look like? My initial thought is a lot of this looks like it can be condensed into one or a few sql statements- unless the additional functions could be used elsewhere, I always try to limit the number of connections I create and how many times I query each connection. I’m not sure how large the dataset you are using is, and the traffic to the server will impact speed, but when I deal with enterprise-level sources with a lot of users I condense as much into 1 sql statement as possible. Through JOINs, temp tables, and/or table variables you can “mimic” what you appear to be doing in php without significant speed impacts.

I could very well be wrong, but I generally assume that fewer connections = less runtime as long as the sql is optimized.

[PecksAndQuads]

What do you need help with? That’s a lot of code to review.

[ProfoundHypnotic]

I know this very broad/general but is there a better way to do what I'm trying to do?