Geek Answers Handbook

Optimizing iteration through an array using foreach loops

I have been executing MySQL queries in foreach loops so far, but now I understand that it is more efficient to start the query first and then iterate through the array. I am wondering if I can optimize the code below - which uses the data in 3 tables to plot a Google graph - next. Is it possible, for example, to add a where clause to foreach loops, so that I do not need to include an if clause in each loop?

$begin = new DateTime(date('Ym-d', strtotime('-28 days'))); $end = new DateTime(date('Ym-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("Ymd"); $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); }
0
source share
2 answers

You only need a good MySQL query.

See here .

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

What Hakan means (I think) is that you are doing it wrong: you must first complete a query that will do almost all the work for you. No need to develop such a complex thing.

And three more tips:

  • try to avoid keywords in php like $expense->Date . This causes syntax highlighting problems (at best, at worst, Php doesn't understand your code).
  • add more comments to your code to explain what you are trying to do.
  • try to avoid keywords in PHP AND queries. You have a column named " Date " and a column named " Type ". It is not safe.

This is just the beginning of what your SQL might look like, and should almost cover 95% of your code. Note: this sentence: let the entire database server do the work for you, this is done for this:

 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'
+1
source

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

0
source

More articles:


All Articles