使用foreach循环优化通过数组的迭代 [英] Optimize iterating through array using foreach loops

问题描述

到目前为止，我一直在foreach循环中运行MySQL查询，但现在认识到先运行查询然后循环访问数组会更高效。我想知道是否可以优化下面的代码 - 使用3个表格中的数据构建Google图表 - 进一步。是否有可能例如添加一个where子句的foreach循环，以便我不需要在每个循环中包含if子句？

  $ begin = new DateTime（date（'Ym-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）; 
 
 $ session = $ 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为$ dt）{
 
 $ session_total = 0; 
 $ work_time_total = 0; 
 $ expense_total = 0; 
 
 $ date = $ dt-> format（Y-m-d）; 
 $ date_display = $ dt->格式（D j M）; 
 $ b foreach（$ session as $ session）{
 if（substr（$ session-> Date，0,10）=== $ date）{
 $ session_total = （$ session_total + $会话而>由于）; 
 
 
 
 foreach（$ work_times as $ work_time）{
 if（$ work_time->日期=== $ date）{
 $ work_time_total =（$ work_time_total + $ work_time->金额）; 
 
 
 
 foreach（$ expenses as $ expense）{
 if（$ expense->日期=== $ date）{
 $ expense_total =（$ expense_total + $ expense->金额）; 
} 
} 
 
 $余额=（$ session_total + $ work_time_total  -  $ expense_total）; 
 
 $ temp = array（）; 
 
 $ temp [] = array（'v'=>（string）$ date_display）; 
 $ temp [] = array（'v'=>（string）$ balance）; 
 $ rows [] = array（'c'=> $ temp）; 
 
  

解决方案

您只需要做好的MySQL查询。

请参阅 here。

您可以添加，替代以及像 Y ，你可以用 SELECT SUM（） /5.0/fr/group-by-modifiers.htmlrel =nofollow> GROUP BY 等。

Hakan的意思是（我猜）是你做错了，你应该先做一个查询，为你做几乎所有的工作。

和其他三种建议：

• 尽量避免使用Php中的关键字，如 $ expense-> Date 。这会使语法高亮显示问题（在最好的情况下，最差的 Php不会理解您的代码）。
  
• 添加更多在您的代码中注释以解释您正在尝试执行的操作。
  
• 尝试避免Php 和 SQL查询中的关键字。你有一个名为' Date '的列和一个名为' Type '的列。这是不安全的。

这只是你的SQL的开始，它几乎应该覆盖95％的代码。注意：这是一个建议：让所有的数据库服务器为你做这个工作，这是为了：

  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费用ex 
 ON xxx 
 WHERE ex.Client ='Psychotherapy'
  




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

解决方案

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'

这篇关于使用foreach循环优化通过数组的迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！