How to loop in mysql instead of looping results

Question

I have a while loop and inside this loop i make sql query and i create an array based on results but i would like to improve this by avoiding the while loop and making this one sql query.

I tried sub_queries but this did not improve my exeucution time. It actually made it slower. I have primary keys and indexes in the tables used in my queries.

$start_date     = $_REQUEST['start_date'];
$end_date       = $_REQUEST['end_date'];
$location_ids   = $_REQUEST['location_id'];

while (strtotime($start_date) <= strtotime($end_date)) {

    $sql="SELECT
    TRUNCATE((SELECT
    COUNT(*)
    FROM
    ticket
    WHERE
    DATE(ticket_timestamp) = '{$start_date}'
    AND outcome_id = 3
    AND ticket.location_id IN  ($location_ids)  ) / (SELECT
    COUNT(*)
    FROM
    ticket
    WHERE
    outcome_id < 4
        AND DATE(ticket_timestamp) = '{$start_date}'
        AND ticket.location_id IN ($location_ids) ) * 100,
    0) AS value;";


    if ($result = $this->db->query($sql)) {

        $row = $result->fetch_row();
        $newarray = array("name"=>$start_date,"value"=>$row[0]);

        if ($row[0] >= "0")
            array_push($response->result, $newarray);

        $start_date = date("Y-m-d", strtotime("+1 day", strtotime($start_date)));
        $i++;
    }
}

$response->success = true;
$response->total = count($response->result);
return $response;

These are my results currently and expected results should still be the same

{
"success":true,
 "result":[
           {"name":"2019-07-08","value":"17"},
           {"name":"2019-07-09","value":"18"}
          ],
 "total":2
}

You probably want to use `BETWEEN` in your SQL ( https://stackoverflow.com/questions/8458290/mysql-select-data-from-database-between-two-dates) and then `GROUP BY` for each date (https://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) — Nigel Ren, Jul 09 '19 at 18:06

Uueerdo · Accepted Answer · 2019-07-09T18:16:21.870

You can use aggregation to eliminate the need to loop, and conditional aggregation to remove the need for the subquerying altogether:

SELECT TRUNCATE(
       COUNT(CASE WHEN t.outcome_id = 3 THEN 1 ELSE NULL END) / COUNT(*) * 100
    ,0)
FROM ticket AS t
WHERE outcome_id < 4 
   AND DATE(t.ticket_timestamp) BETWEEN '{$start_date}' AND '{$end_date}'
   AND t.location_id IN ($location_ids)
GROUP BY DATE(t.ticket_timestamp);

COUNT does not count NULL values
By default, a CASE without an ELSE will evaluate as null if none of it's preceding WHEN...THEN's were used; ELSE NULL was optional but added to make the query logic clearer.
If ticket_timestamp is an actual timestamp or datetime data type, you could also see performance improvement by changing the date filtering to something like t.ticket_timestamp >= start_date AND t.ticket_timestamp < day_after_end_date
You should try to use parameterized queries instead of string insertion; otherwise you are left open to sql injection vulnerabilities

How to loop in mysql instead of looping results

1 Answers1