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

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";然后加&nbsp; &nbsp; if (empty($last_score)) {&nbsp; &nbsp; &nbsp; $last_score=0;&nbsp; &nbsp; }else{&nbsp; &nbsp; }&nbsp; &nbsp;&nbsp;之间$last_score = trim($scores[count($scores)-1]);和$finalScore15 += $place_map[$last_score];那应该可以完全解决你的问题。

Answer 3

你正在做if ($result = $db->query($query)) {&nbsp; &nbsp; $finalScore1 = 0;&nbsp; &nbsp;......}if ($result = $db->query($query)) {&nbsp; &nbsp;$finalScore2 = 0;&nbsp; &nbsp;.......}echo ($finalscore +$finalscore2);试着做：$finalScore1 = 0;if ($result = $db->query($query)) {&nbsp; &nbsp;......}$finalScore2 = 0;if ($result = $db->query($query)) {&nbsp; &nbsp;.......}echo ($finalscore +$finalscore2);你还没有定义，在所有上面定义：$place_map = ['first' => 1, 'second' => 0.8, 'third' => 0.4];希望这些可以解决您的错误，根据数据和代码休息结果

Answer 4

我希望这个能帮上忙&nbsp; &nbsp; <?php&nbsp;&nbsp;&nbsp;$Date1 = '01-10-2010';&nbsp;$Date2 = '07-12-2010';&nbsp;&nbsp;&nbsp;$array = array();&nbsp;$range = 0;$Variable1 = strtotime($Date1);&nbsp;$Variable2 = strtotime($Date2);&nbsp;&nbsp;&nbsp;for ($currentDate = $Variable1; $currentDate <= $Variable2;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $currentDate += (86400)) {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;$Store = date('Y-m-d', $currentDate);&nbsp;$array[] = $Store;$range++;}&nbsp;for ($i=0; $i < $range; $i++) {&nbsp;&nbsp; &nbsp; echo $array[$i];}&nbsp;&nbsp;?>&nbsp;

Answer 5

我在理解你想要从 e1 和 e2 中得到什么时遇到了一些困难 - 假设它只是该字段中的最后一个值。如果我错了，你可以在函数 $eview 中更正它。下面的代码使用您发布的数据产生 4.2 的结果。$datfrm = "";&nbsp; &nbsp; &nbsp; &nbsp;// or something like '2010-01-01'$datto = '2200';&nbsp; &nbsp; // 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) {&nbsp; &nbsp; $parts = array_filter(array_map('trim', explode(",", $row[$ecol])));&nbsp; &nbsp; $dates = array_filter(array_map('trim', explode(",", $row[$dcol])));&nbsp; &nbsp; if (!$parts || count($parts) != count($dates)) {&nbsp; &nbsp; &nbsp; &nbsp; echo "Expected same number of parts and dates!\n";&nbsp; &nbsp; &nbsp; &nbsp; return false;&nbsp; &nbsp; }&nbsp; &nbsp; $date = array_pop($dates);&nbsp; &nbsp; &nbsp; // Getting last date...&nbsp; &nbsp; return ($date >= $datfrm && $date < $datto) ?&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;($place_map[array_pop($parts)] ?? false) : 0;&nbsp; &nbsp; };$finalScore1 = $finalScore2 = 0;// perform the query ...$query = "SELECT e1, e2, d1, d2 FROM eyfstb WHERE gender = '{$gender}'";if ($result = $db->query($query)) {&nbsp; &nbsp; &nbsp; &nbsp; /* fetch associative array */&nbsp; &nbsp; while ($row = $result->fetch_assoc()) {&nbsp; &nbsp; &nbsp; $finalScore1 += $eview($row, 'e1', 'd1') ?: 0;&nbsp; &nbsp; &nbsp; $finalScore2 += $eview($row, 'e2', 'd2') ?: 0;&nbsp; &nbsp; }}echo ($finalScore1+$finalScore2) . "\n";