#StackBounty: #php How can i get data from using date range for querying multiple details from a table?

Bounty: 50

I’ve been able to get multiple details from this table, using the codes beneath the table…

I know the table looks odd, but that’s the available data table from the client.

    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

Below is the code i used to get the details:

$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];
        }
    }
}

//e2view
$query = "SELECT e2 FROM eyfstb WHERE gender = '$gender'";
// perform the query ...
if ($result = $db->query($query)) {
    $finalScore2 = 0;
    $scores="";
    $last_score="";
    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        $efind = $row['e2'];
        if (substr_count($efind, ",") >'2'){
            $scores = explode(',', $row['e2']);
            $last_score = trim($scores[count($scores)-1]);
            $finalScore2 += $place_map[$last_score];
        }
    }
}

now what i’m trying to do is get a date range below to help display only the records from $datfrm and $datto

actually i tried this below, but i’m getting error:

//Filter out dates between start and end date
$d1result = array_filter($d1view, function($data_item) use($datfrm,$datto) {
    return $data_item >= $datfrm && $data_item <= $datto;
});

$e1result = array_intersect_key($e1view, $d1result);

but now with the code i tried beneath, I’m getting more errors:

//e1view
$query = "SELECT e1,d1 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'];
        $dview = $row["d1"];    
        if (substr_count($efind, ",") >'2'){
            $dview = ltrim($dview,',');
            $dview = explode(',', $dview);
            $efind = ltrim($efind,',');
            $efind = explode(',', $efind);
            
            $dresult = array_filter($dview, function($data_item) use($datfrm,$datto) {
                return $data_item >= $datfrm && $data_item <= $datto;
            });
            
            $eresult = array_intersect_key($efind, $dresult);
            $efind = implode(',', $eresult);
            $efind = ','.$efind;
            $scores = explode(',', $efind);
            $last_score = trim($scores[count($scores)-1]);
            $finalScore1 += $place_map[$last_score];
        }
    }
}
    
//e2view
$query = "SELECT e2,d2 FROM eyfstb WHERE gender = '$gender'";
// perform the query ...
if ($result = $db->query($query)) {
    $finalScore2 = 0;
    $scores="";
    $last_score="";
    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        $efind = $row['e2'];
        $dview = $row["d2"];        
        if (substr_count($efind, ",") >'2'){
            $dview = ltrim($dview,',');
            $dview = explode(',', $dview);
            $efind = ltrim($efind,',');
            $efind = explode(',', $efind);

            $dresult = array_filter($dview, function($data_item) use($datfrm,$datto) {
                return $data_item >= $datfrm && $data_item <= $datto;
            });

            $eresult = array_intersect_key($efind, $dresult);
            $efind = implode(',', $eresult);
            $efind = ','.$efind;
            $scores = explode(',', $efind);
            $last_score = trim($scores[count($scores)-1]);
            $finalScore2 += $place_map[$last_score];
        }
    }
}

echo ($finalscore +$finalscore2);

Below are the errors displayed plus one answer i’m not sure it’s even correct:

Notice: Undefined index: in C:xampphtdocsSAPgenderresultgender1.php on line 69

(line 69 is `$finalScore1 += $place_map[$last_score];`)

Notice: Undefined index: in C:xampphtdocsSAPgenderresultgender1.php on line 69

Notice: Undefined index: in C:xampphtdocsSAPgenderresultgender1.php on line 106

(line 69 is `$finalScore2 += $place_map[$last_score];`)

39.4

And my desired result is to get only values in e1 and e2 that are in the date range from d1 and d2
if there are 2 files in e1 or e2 field, i don’t want it to display or map anything.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.