Optimize iterating through array using foreach loops

Question

I have been running MySQL queries within foreach loops up until now, but now realize that it is more efficient to run the query first and then iterate through the array. I am wondering if I can optimize the code below - which uses data in 3 tables to construct a Google graph - further. Is it possible for instance to add a where clause to the foreach loops so that I don't need to include the if clause within each loop?

$begin = new DateTime(date('Y-m-d', strtotime('-28 days')));
$end = new DateTime(date('Y-m-d', strtotime('+1 day')));
$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);

$sessions = $wpdb->get_results($wpdb->prepare("SELECT Due,Date from patient_sessions WHERE Type='Session'"));
$work_times = $wpdb->get_results($wpdb->prepare("SELECT Amount,Date from work_times"));
$expenses = $wpdb->get_results($wpdb->prepare("SELECT Amount,Date from expenses WHERE Client='Psychotherapy'"));

foreach ( $period as $dt ) {

    $session_total = 0;
    $work_time_total = 0;
    $expense_total = 0;

    $date = $dt->format("Y-m-d");
    $date_display = $dt->format("D j M");

    foreach ($sessions as $session) {
       if (substr($session->Date,0,10) === $date) {
          $session_total = ($session_total+$session->Due);
       }
    }

    foreach ($work_times as $work_time) {
       if ($work_time->Date === $date) {
          $work_time_total = ($work_time_total+$work_time->Amount);
       }
    }

    foreach ($expenses as $expense) {
       if ($expense->Date === $date) {
          $expense_total = ($expense_total+$expense->Amount);
       }
    }

    $balance = ($session_total + $work_time_total - $expense_total);

    $temp = array();

    $temp[] = array('v' => (string) $date_display); 
    $temp[] = array('v' => (string) $balance); 
    $rows[] = array('c' => $temp);
}

I don't understand your last line: $rows[] = array('c' => $temp);, what are you trying to do here? — Revent
– Revent, Commented Apr 24, 2013 at 19:51

Olivier Pons · Accepted Answer · 2014-05-28 08:42:33Z

You only need to do a good MySQL query.

See here.

You can do additions, substractions, and things like date BETWEEN x AND Y, you can do a SELECT SUM() with a GROUP BY and so on.

What Hakan means (I guess) is that you're doing it the wrong way: you should first do a query that makes almost all the work for you. No need to develop such complex thing.

And three other advices:

try to avoid keywords in Php like $expense->Date. This makes syntax highlighting problems (in the best case, in the worst Php won't understand your code).
add more comments in your code to explain what you're trying to do.
try to avoid keywords in Php AND SQL queries. You have a column named 'Date' and a column named 'Type'. This is not safe.

Here's just a beginning of what your SQL could look like, and it almost should cover 95% of your code. Note: this is a suggestion: let all database server do the job for you, this is made for that:

SELECT
    ps.Due,ps.Date,
    wt.Amount,wt.Date,
    ex.Amount,ex.Date

LEFT JOIN patient_sessions ps
    ON xxx
    WHERE ps.Type='Session'
    AND ps.Date
        BETWEEN DATE_ADD(NOW(), INTERVAL '-28' DAY)
        AND     DATE_ADD(NOW(), INTERVAL 1 DAY)
LEFT JOIN work_times wt
    ON xxx
LEFT JOIN expenses ex
    ON xxx
    WHERE ex.Client='Psychotherapy'

Håkan · Accepted Answer · 2013-04-24 22:04:38Z

0

Why don't you let the database do the if for you? Adding the date criteria to the WHERE statements, I mean.

answered Apr 24, 2013 at 22:04

Håkan

1866 bronze badges

Collectives™ on Stack Overflow

Optimize iterating through array using foreach loops

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related