PHP Calculation to MySQL Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

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

PHP Calculation to MySQL Query

Post by icesolid »

I was just wondering if there is any way to perform this calculation in an simplier/smaller MySQL query.

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;
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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Untested!

Code: Select all

SELECT AVG(DATEDIFF(date_completed, date_ordered))
FROM cases 
WHERE $sort AND date_completed BETWEEN '$y-01' AND '$y-02' 
PS: Your code shows cases *completed* in Januaray, not the ordered ones ...
If you want AVG of the ordered cases you should use date_ordered in the where clause.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply