33172

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

Answer1:

You only need to do a good MySQL query.

See <a href="http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html" rel="nofollow">here</a>.

You can do additions, substractions, and things like date BETWEEN x AND Y, you can do a SELECT SUM() with a <a href="http://dev.mysql.com/doc/refman/5.0/fr/group-by-modifiers.html" rel="nofollow">GROUP BY</a> 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:

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

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'

Answer2:

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

Recommend

  • Adding line breaks to output file via fwrite
  • How can I use multiple WHERE statements when using IN with array
  • Wordpress, List Authors and Exclude Admin
  • PHP calc difference between time greater than 24 hours
  • Split an array in sub arrays of consecutive dates
  • WordPress: How search a post for “post_content” with “wp_query” class?
  • PHP getting month dates and placing into array
  • Insert Values into a custom table once order is placed in Woocommerce
  • wordpress wpdb->update not working
  • How can I initialize wpdb class in a php file?
  • Convert int 60 to a time value of 60 minutes
  • Is there a MySQL query that can encode to JSON in exactly this way?
  • PHP PDO Update prepared statement problem
  • Inserting NULL/empty string using libpqxx library
  • jqPlot date axis - bars drawn on wrong day?
  • MySql - get days remaining
  • data.table replicate rows after join?
  • Returning this from a constructor function in JS
  • What is the use of a session store?
  • How to get the date of next specified day of week
  • Runtime.exec() gives Error: Could not find or load main class
  • C# - Most efficient way to iterate through multiple arrays/list
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • Unity3D & Android: Difference between “UnityMain” and “main” threads?
  • Illegal mix of collations for operation for date/time comparison
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Delete MySQLi record without showing the id in the URL
  • SetUp method failed while running tests from teamcity
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • Change div Background jquery
  • How does Linux kernel interrupt the application?
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?