Skip to main content
Update wording
Source Link

And that string could be generated using the mapping from abovementioned earlier. With a query like the one above, the chain of if...else statements should be replaceable with $row['timeValue'] and $row['label'].

And that string could be generated using the mapping from above. With a query like the one above, the chain of if...else statements should be replaceable with $row['timeValue'] and $row['label'].

And that string could be generated using the mapping mentioned earlier. With a query like the one above, the chain of if...else statements should be replaceable with $row['timeValue'] and $row['label'].

Source Link

Having been taught to have a few queries as possible, my first thought is to try to help get that code to only use one query. My initial thought is to advise you to add the ORDER BY clause - something like the query below.

Warning: Untested code:

$result = mysqli_query($con,"SELECT actualstart, actualhalftime, actualft, HomeTeam, AwayTeam FROM livescores ORDER BY actualstart DESC, actualhalftime DESC, actualft DESC");

Then when looping through the results, you would have to check (in PHP) whether each field (actualstart, actualhalftime, actualft, etc.) was greater than $datefilter (perhaps utilizing strtotime())

$dateFilterString = strtotime($datefilter)
while($row = mysqli_fetch_array($result))
{
    echo "<tr>";
    $label = '';
    $time = '';
    if (strtotime($row['actualstart']) >= $dateFilterString) {
        $label = 'KO';
        $time = $row['actualstart'];
    } 
    else if (strtotime($row['actualhalftime']) >= $dateFilterString) {
        $time = $row['actualstart'];
        $label = 'HT';
    }
    else if (strtotime($row['actualft']) >= $dateFilterString) {
        $time = $row['actualft'];
        $label = 'FT';
    }
    echo "<td>" . $time . "</td>";
    echo "<td>" . $row['League'] . "</td>";
    echo "<td>" . $label ."</td>";
    echo "<td>" . $row['HomeTeam'] .' v '. $row['AwayTeam'] . "</td>";
    echo "<td></td>";
    echo "</tr>";
}

Obviously that block of if statements and blocks would become quite large for 10 different fields... That could be compacted by defining a mapping, such as:

const MAPPING = [
    'actualstart' => 'KO',
    'actualhalftime' => 'HT',
    'actualft' => 'FT'
    //other mappings     
];

And then iterate over those keys and values:

foreach(MAPPING as $key => $mappedLabel) {
    if (strtotime($row[$key]) >= $dateFilterString) {
        $time = $row[$key];
        $label = $mappedLabel;
    }
}

The query could also be updated to utilize the CASE statement. For example, the label and time value could be selected conditionally based on the time values (new lines added for readability):

$result = mysqli_query($con,"SELECT CASE 
        WHEN actualstart >= '$datefilter' THEN actualstart 
        WHEN actualhalftime >= '$datefilter' THEN actualhalftime 
        WHEN actualft>= '$datefilter' THEN actualft 
    END CASE AS timeValue, CASE 
        WHEN actualstart >= '$datefilter' THEN 'KO' 
        WHEN actualhalftime >= '$datefilter' THEN 'HT' 
        WHEN actualft>= '$datefilter' THEN 'FT' 
    END CASE AS label, HomeTeam, AwayTeam 
    FROM livescores 
    ORDER BY actualstart DESC, actualhalftime DESC, actualft DESC");

And that string could be generated using the mapping from above. With a query like the one above, the chain of if...else statements should be replaceable with $row['timeValue'] and $row['label'].

Also, the ORDER BY could use the CASE statements to specify custom values. Refer to this SO answer but instead of string literals use the expressions mentioned above in the conditional SELECT fields.