2
\$\begingroup\$

I'm running the below code to populate a HTML table using several calls to mysqli_query(). The below snippet only shows three however I'm running ten in my file for various other date columns and may need more over time. I've attached a file below showing the basic database layout and required output.

Example

The required output is basically just a list of times from various columns

The below code works fine, however it is probably not the most efficient and other options like MySQL JOIN or UNION don't seem to work.

Do we have any another options to perform the desired output?

 $result = mysqli_query($con,"SELECT * FROM livescores WHERE actualstart >= '$datefilter'");
 $resulthalf = mysqli_query($con,"SELECT * FROM livescores WHERE actualhalftime >= '$datefilter'");
 $actualft = mysqli_query($con,"SELECT * FROM livescores WHERE actualft>= '$datefilter'");

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

Many Thanks

\$\endgroup\$
  • \$\begingroup\$ 1) instead of having three while loops, have a for loop with some variable going through "KO", "HT", and "FT". 2) is it reasonable to query all rows in the table and have the PHP decide which games belong to which category? 3) Do you really need that many echo statements? Could you use a HERE document, for example? \$\endgroup\$ – Barry Carter Dec 31 '17 at 12:57
  • \$\begingroup\$ Along with Barry's questions: is it generally always true that the value in column actualft > the value in column actualhalftime > the value in column starttime? and what are samples of values for $datefilter- would it be like 18:00, 19:30, etc.? \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Jan 4 '18 at 22:09
2
\$\begingroup\$

I can't say how the queries should be built for the other 7 that you haven't posted, but I think I've identified enough commonalities in your design intent to exercise the D.R.Y. principle.

While it is always best to minimize total calls to the database, it seems you are struggling to package all of the queries into one. I'll suggest implementing mysqli_multi_query() for your case with the noticeable benefit being maintainability. I am assuming that your $datefilter value is 100% validated/secure and is safe to place into your queries (versus being a raw user input). I should also note that if any of your queries experience a syntax error, the loop will break, the html table will close early, and you will see details about the erring query.

The following method can be modified to allow blank rows and/or group headings withing the html table -- just write that portion between the do{ and if($result... lines.

Untested Code:

// $datefilter MUST be validated/trustworthy as prepared statements are not in use.
$queries['KickOff']="SELECT actualstart AS `Time`,League,'KO' AS Label,HomeTeam,AwayTeam FROM livescores WHERE actualstart>='$datefilter'";
$queries['HalfTime']="SELECT actualhalftime AS `Time`,League,'HT' AS Label,HomeTeam,AwayTeam FROM livescores WHERE actualhalftime>='$datefilter'";
$queries['Fulltime']="SELECT actualft AS `Time`,League,'FT' AS Label,HomeTeam,AwayTeam FROM livescores WHERE actualft>='$datefilter'";
// ... ~7 more queries entries here

echo "<table>";
if(mysqli_multi_query($mysqli,implode(';',$queries))){
    do{
        if($result=mysqli_store_result($mysqli)){
            while($row=mysqli_fetch_assoc($result)){
                echo "<tr><td>{$row['Time']}</td><td>{$row['League']}</td><td>{$row['Label}</td><td>{$row['HomeTeam']} v {$row['AwayTeam']}</td><td></td></tr>";
            }
            mysqli_free_result($result);
        }
    } while(next($queries) && mysqli_more_results($mysqli) && mysqli_next_result($mysqli));
}
echo "</table>";
if($mysqli_error=mysqli_error($mysqli)){
    echo "<div>Error occurred on ",key($queries);
    // echo " Query: ",current($queries),"Syntax Error:<br>$mysqli_error</div>";  // developer-only details
    echo "</div>";
}

A link to my previous work that I am leveraging for this answer.

\$\endgroup\$
  • \$\begingroup\$ Do you think it would be wise to suggest the OP could choose to generate the strings in $queries by iterating over an array of the fields (e.g. $fields = array('actualstart', 'actualhalftime',...);) and appending each field into a generic string (e.g. foreach ($fields as $field) { $queries[$field] = "SELECT " . $field . " AS `TIME`, League,..."; }? If so, array_map() could also be used... \$\endgroup\$ – Sᴀᴍ Onᴇᴌᴀ Feb 9 '18 at 17:06
  • 1
    \$\begingroup\$ I think it is likely that I would alter my advice if I had more details. It would be beneficial to receive all original queries, an sqlfiddle with a sufficient portion of data, and a fuller representation of the expected result. \$\endgroup\$ – mickmackusa Feb 10 '18 at 7:10
1
\$\begingroup\$

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 mentioned earlier. 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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.