PHP Calculation to MySQL Query
Posted: Mon Aug 13, 2007 6:51 pm
I was just wondering if there is any way to perform this calculation in an simplier/smaller MySQL query.
Here is the calculation:
This calculation calculates time service for the month of January and prints a total. It takes every case ordered in January and then finds the date difference between each cases date ordered and date completed and gets a total number of days. Then it divides the total number of days by the number of cases ordered in that month and produces an average time service for that month.
Here is the calculation:
Code: Select all
$result = mysql_query("SELECT * FROM cases WHERE $sort AND date_completed BETWEEN '$y-01' AND '$y-02' ORDER BY id");
$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 id='" . $row["id"] . "'");
$row2 = mysql_fetch_array($result2);
$result3 = mysql_query("SELECT DATEDIFF('" . $row2["date_completed"] . "','" . $row2["date_ordered"] . "') AS time_service FROM cases WHERE id='" . $row["id"] . "'");
$row3 = mysql_fetch_array($result3);
$time_service = $row3["time_service"];
$ts_total_amount = $ts_total_amount + $time_service;
}
$january = $ts_total_amount / $ts_total_num;