Time Service Calculation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Time Service Calculation

Post by icesolid »

I am having problems with the time service calculation below. If I run the calculation for a customer that has 20 reports, the calculation runs quickly. However, when I run the calculation for a customer that has 100 or more (roughly) the calculation takes 10 to 20 seconds, which is slow loading time. I was wondering if there is anything in my code that I can change to make this calculation more efficient.

This calculation takes the number of cases that have been ordered in a certain time period (specified by $start_date and $end_date) and how many have been completed and calculates how many days as an average it took for each one individually to be completed. Then it takes those totals and gives one large total for all of the cases ordered and completed for a 30 day time period. ($row["control_number"] is the identifier for the row of data, each row has a control_number (ex: report number).

Code: Select all

<?php
$start_date = strtotime("-30 day");
$start_date = date("Y-m-d", $start_date);
$end_date = date("Y-m-d");

$result = mysql_query("SELECT * FROM cases WHERE customer='" . $_SESSION["user"] . "' AND date_completed BETWEEN '$start_date' AND '$end_date' ORDER BY control_number");
$row = mysql_fetch_array($result);

$ts_total_num = "1";

while($row = mysql_fetch_array($result)) {
    $ts_total_num ++;

    $result2 = mysql_query("SELECT * FROM cases WHERE control_number='" . $row["control_number"] . "'");
    $row2 = mysql_fetch_array($result2);

    $result3 = mysql_query("SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE control_number='" . $row["control_number"] . "'");
    $row3 = mysql_fetch_array($result3);

    $time_service = $row3["time_service"];
    $ts_total_amount = $ts_total_amount + $time_service;
}

$average = $ts_total_amount / $ts_total_num;
$average = number_format($average, "2");

echo $average;
?>
User avatar
kaszu
Forum Regular
Posts: 749
Joined: Wed Jul 19, 2006 7:29 am

Post by kaszu »

First thing i notice was:

Code: Select all

"SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE control_number='" . $row["control_number"] . "'"
if i understand correctly then your sending to mysql something like

Code: Select all

"SELECT DATEDIFF('"1990-01-01 11:11:11"','"1990-01-01 11:11:11"') AS time_service FROM cases WHERE control_number='"123'"
while in fact your not selecting anything from database, but just using it calculate difference of days between 2 dates, which can be done using php, because you already know these dates.

Second:
In first SQL you are selecting all fields from database, while you are using only "control_number".

Is control_number a primary key for cases table?
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

id is the primary field.

control_number is not.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

How dumb of me, sorry for wasting your time.

I used id to search the DB instead of control_number and the calculations are fast.

Thanks, sorry for bothering you, I should have figured that one out myself.
Post Reply