CODEWITHSUNDEEP
phpmysqlforeachwhere-clause
Nick
Nick

Reputation: 4462

Optimize iterating through array using foreach loops

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);
}

Upvotes: 0

Views: 173

Answers (2)

Olivier Pons
Olivier Pons

Reputation: 15816

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'

Upvotes: 1

Håkan
Håkan

Reputation: 186

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

Upvotes: 0

Related Questions