Problem
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.
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
Solution
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.
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.