如何使用日期范围从表中查询多个详细信息来获取数据？

Question

我已经能够使用表下方的代码从此表中获取多个详细信息......我知道该表看起来很奇怪，但这是客户端提供的可用数据表。    id  | class  | gender |         e1              |      e2                     |                d1                     |           d2            ----------------------------------------------------------------------------------------------------------------------------------------------------------------    1   | class1   |  male  | ,first, first, first    | ,first, first, first        |  , 2019-12-24,2019-12-24,2019-12-24    | ,2019-12-04,2019-11-21,2019-11-20 |      2   | class2   | female | ,second, second, second |  ,second, second, second    |  ,2019-12-04,2019-11-21,2019-11-20      |  , 2019-12-20,2019-12-20,2019-12-20     3   | class3   |  male  | ,first, first, first    | ,third, third, third        |  ,2019-12-06,2019-12-13,2019-12-19    |  ,2019-12-06,2019-12-13,2019-12-19    4   | class4   |  male  | ,third, third, third    | ,third, third, third        | ,2019-12-20,2019-12-20,2019-12-20     |  ,2019-12-24,2019-12-24,2019-12-24      5   | class5   | female | ,second, second         | ,third, third, third        | ,2019-12-24,2019-12-24,2019-12-24     |  ,2019-12-24,2019-12-24,2019-12-24下面是我用来获取详细信息的代码：$datfrm ="00/00/0001";$datto ="31/12/2099";$gender="male";$place_map = ['first' => 1, 'second' => 0.8, 'third' => 0.4];//e1view$query = "SELECT e1 FROM eyfstb WHERE gender = '$gender'";// perform the query ...if ($result = $db->query($query)) {    $finalScore1 = 0;    $scores="";    $last_score="";    /* fetch associative array */    while ($row = $result->fetch_assoc()) {        $efind = $row['e1'];        if (substr_count($efind, ",") >'2'){            $scores = explode(',', $row['e1']);            $last_score = trim($scores[count($scores)-1]);            $finalScore1 += $place_map[$last_score];        }    }}

Answer 1

您想要跳过字段中不正好有 3 个元素的行e1。在行：之后 $efind = explode(',', $efind);，添加以下内容：

if (count($efind) !== 3) {
    continue;
}

在两个地方都添加这个。

---

代码中的另一个问题是，当您尝试比较日期时，您将字符串与<=和进行比较>=。您应该使用strcmp()。

如果不知道表的全部内容，就很难看出未定义索引错误来自何处。

Answer 2

首先，更改您的日期

$datfrm ="00/00/0001";

$datto ="31/12/2099";

到

$datfrm ="00-00-0001";

$datto ="31-12-2099";

然后加

    if (empty($last_score)) {

      $last_score=0;

    }else{

    }

    

之间

$last_score = trim($scores[count($scores)-1]);

和

$finalScore15 += $place_map[$last_score];

那应该可以完全解决你的问题。

Answer 3

你正在做

if ($result = $db->query($query)) {

    $finalScore1 = 0;

   ......

}

if ($result = $db->query($query)) {

   $finalScore2 = 0;

   .......

}

echo ($finalscore +$finalscore2);

试着做：

$finalScore1 = 0;

if ($result = $db->query($query)) {

   ......

}

$finalScore2 = 0;

if ($result = $db->query($query)) {

   .......

}

echo ($finalscore +$finalscore2);

你还没有定义，在所有上面定义：

$place_map = ['first' => 1, 'second' => 0.8, 'third' => 0.4];

希望这些可以解决您的错误，根据数据和代码休息结果

Answer 4

我希望这个能帮上忙

    <?php 

  

$Date1 = '01-10-2010'; 

$Date2 = '07-12-2010'; 

  

$array = array(); 

$range = 0;

$Variable1 = strtotime($Date1); 

$Variable2 = strtotime($Date2); 

  

for ($currentDate = $Variable1; $currentDate <= $Variable2;  

                                $currentDate += (86400)) { 

                                      

$Store = date('Y-m-d', $currentDate); 

$array[] = $Store;

$range++;

} 

for ($i=0; $i < $range; $i++) { 

    echo $array[$i];

}

  

?> 

Answer 5

我在理解你想要从 e1 和 e2 中得到什么时遇到了一些困难 - 假设它只是该字段中的最后一个值。如果我错了，你可以在函数 $eview 中更正它。

下面的代码使用您发布的数据产生 4.2 的结果。

$datfrm = "";       // or something like '2010-01-01'

$datto = '2200';    // or something like '2015-12-01'

$gender="male";

$place_map = ['first' => 1, 'second' => 0.8, 'third' => 0.4];

// My understanding of the code is to get the weighted value of the

// last word in the column - if that's wrong, you can change this

// I am assuming you want the date that corresponded to the item you used

// for the final score, so if wrong, you need to change this.

// Also assume there will be the same number of dates as 'e's

//

// return false if bad value, or 0 if not in date range

$eview = function ($row, $ecol, $dcol) use ($place_map, $datfrm, $datto) {

    $parts = array_filter(array_map('trim', explode(",", $row[$ecol])));

    $dates = array_filter(array_map('trim', explode(",", $row[$dcol])));

    if (!$parts || count($parts) != count($dates)) {

        echo "Expected same number of parts and dates!\n";

        return false;

    }

    $date = array_pop($dates);      // Getting last date...

    return ($date >= $datfrm && $date < $datto) ?

           ($place_map[array_pop($parts)] ?? false) : 0;

    };

$finalScore1 = $finalScore2 = 0;

// perform the query ...

$query = "SELECT e1, e2, d1, d2 FROM eyfstb WHERE gender = '{$gender}'";

if ($result = $db->query($query)) {

        /* fetch associative array */

    while ($row = $result->fetch_assoc()) {

      $finalScore1 += $eview($row, 'e1', 'd1') ?: 0;

      $finalScore2 += $eview($row, 'e2', 'd2') ?: 0;

    }

}

echo ($finalScore1+$finalScore2) . "\n";