Faster way to pull averages from results of MySQL queries nested in loops – Phpmyadmin

I am really hesitate in posting an answer, as the SQL here is causing all kinds of extra work your doing in PHP. Without the DB schema and some sample data I feel like I am just walking blind though this. For example what are all these DISTINCT things doing here, maybe they are needed, maybe they are extra … I have no way to know.

That said here goes,

These are all the same table so it makes no sense to query the same data again, take these 2 Queries for example

$customers = "SELECT DISTINCT customer_name FROM `wo_main_alldata` WHERE ship_date BETWEEN '$twoYearsAgo' AND '$currentDate'";

$laneData = "SELECT DISTINCT type_of_shipment, pickup_city, pickup_state, consignee_city, consignee_state
FROM wo_main_alldata
WHERE customer_name = '$customerName' 
AND pickup_city != ''";

You can combine them with something like this:

$laneData = "SELECT DISTINCT
    customer_name,  -- From the first query
    type_of_shipment,
    pickup_city,
    pickup_state,
    consignee_city,
    consignee_state
FROM 
    wo_main_alldata
WHERE 
    ship_date BETWEEN '$twoYearsAgo' AND '$currentDate' 
    AND
    pickup_city != ''
";

The first query pulls all the “Distinct” customer names, then you loop over that and use that name to lookup the next set of data in the Same Table.

This lookup will void the distinct you used in the fist one, but (yea) you have it here too. It voids it because the second query says, “Give me all records where customer_name = something”, so if the name is in there repeatedly you find all of those with the second query. The distinct may have been important when they were separate, to control the foreach ($customerList as $customerName){ loop. But, we no longer need that loop.

When we combine these we add the customer_name to the Select of the second one, and also add the WHERE bits in. Then we can remove the condition that tied these together customer_name = '$customerName' because we no longer need it as it becomes customer_name=customer_name which is just “this row”.

Now doesn’t that look much cleaner. It also eliminates all this code:

$customers = "SELECT DISTINCT customer_name FROM `wo_main_alldata` WHERE ship_date BETWEEN '$twoYearsAgo' AND '$currentDate'";
$customerResult = mysqli_query($conn, $customers);
$customerList= array();
while ($row = mysqli_fetch_array($customerResult)) {
    $customerList[] = $row[0];
}
$lanesArray = array();
foreach ($customerList as $customerName){

    $laneData = "SELECT DISTINCT type_of_shipment, pickup_city, pickup_state, consignee_city, consignee_state
    FROM wo_main_alldata
    WHERE customer_name = '$customerName' 
    AND pickup_city != ''";

Both pick data from the same table, the second one (in the loop) simply takes the username from the first query, and looks it up again in the same table.

For the last query, you are (again) just back referencing the same data

 $equipment = $row2[0]; //from previous query on same table
 WHERE type_of_shipment = '$equipment' 

All the stuff in the where condition comes directly from the results of the other query, so you can just eliminate that. And that leaves us with this:

SELECT
   AVG(f.proj_revenue),
   AVG(f.proj_gross_profit),
   COUNT(f.pro_num)
FROM (
  SELECT DISTINCT
    customer_name,
    type_of_shipment,
    pickup_city,
    pickup_state,
    consignee_city,
    consignee_state
  FROM 
    wo_main_alldata
  WHERE 
    ship_date BETWEEN '$twoYearsAgo' AND '$currentDate' -- From the first query
    AND
    pickup_city != ''
) as f

I can’t really test this so you may have to make some adjustments, I am just sort of feeling my way though the logic of it. I am pretty sure that the columns in the top level query must also be in the inner sub query. Specifically f.proj_revenue, f.proj_gross_profit and f.pro_num. Probably you will get something like Unknown column 'f.proj_gross_profit' in 'field list'

There are a few ways to fix this, by joining again on the table.

SELECT
   AVG(m.proj_revenue),
   AVG(m.proj_gross_profit),
   COUNT(m.pro_num)
FROM 
    wo_main_alldata AS m
JOIN
    (
      SELECT DISTINCT
        id,  //<--- id is an issue
        customer_name, 
        type_of_shipment,
        pickup_city,
        pickup_state,
        consignee_city,
        consignee_state
      FROM 
        wo_main_alldata
      JOIN
      WHERE 
        ship_date BETWEEN '$twoYearsAgo' AND '$currentDate'
        AND
        pickup_city != ''
    ) as f
ON f.id = m.id

I am not really sure the best way around that as I don’t know what must be distinct. That really complicates it because if you put the ID in as above it can poison your Distinct call, by being unique per row. You may be able to just do it all in one query:

SELECT DISTINCT
    customer_name, 
    type_of_shipment,
    pickup_city,
    pickup_state,
    consignee_city,
    consignee_state,
    AVG(m.proj_revenue),
    AVG(m.proj_gross_profit),
    COUNT(m.pro_num)
  FROM 
    wo_main_alldata
  WHERE 
    ship_date BETWEEN '$twoYearsAgo' AND '$currentDate'
    AND
    pickup_city != ''

But it’s too abstract for me to tell. Don’t be afraid to take PHPmyAdmin (or whatever you manage the DB with) and work on the query right in there. That way you can play with it outside of any coding and get it just the way you want.

IN any case, if you find yourself making round trips to the DB for the same data chances are you can do it in one slightly more complex query. It’s tempting if you don’t know SQL well, but are fair at PHP to just do simple queries and deal with it in PHP.

At first this seems the “easy” way, but every bit of work you can have the DB do saves you 2 or 3 bits of work in PHP. Your code will be smaller, lighter weight, simpler and easier to read. For example (Provided you can combine these as suggested) you code becomes this:

$lanesAverageResult = mysqli_query($conn, $laneAverages); //our new query
while ($row3 = mysqli_fetch_array($lanesAverageResult)){

}

So we just eliminated 25+ lines of PHP, with a slightly more complex query.

PS sorry this is so long..

Hope it helps!