php soccer season - gamedays - matches

[veltix]

Hi,

How I can get seasons and under seasons is gamedays and each gameday have his own matches.

(Fifa 2018 is season)
Fifa 2018 - first gameday

PHP VS Help
Help VS PHP

Fifa 2018 - second gameday

PHP VS Help
Help VS PHP

Fifa 2018 - third gameday
PHP VS Help

My code, what I’ve tryed:

[php]<?php
function convertNumberToWord($num = false)
{
$num = str_replace(array(’,’, ’ ‘), ‘’ , trim($num));
if(! $num) {
return false;
}
$num = (int) $num;
$words = array();
$list1 = array(’’, ‘First’, ‘Second’, ‘Third’, ‘Fourth’, ‘Fifth’, ‘Sixth’, ‘Seventh’, ‘Eight’, ‘Ninth’, ‘Tenth’
);
$list2 = array(’’, ‘ten’, ‘twenty’, ‘thirty’, ‘forty’, ‘fifty’, ‘sixty’, ‘seventy’, ‘eighty’, ‘ninety’, ‘hundred’);
$list3 = array(’’, ‘thousand’, ‘million’, ‘billion’, ‘trillion’, ‘quadrillion’, ‘quintillion’, ‘sextillion’, ‘septillion’,
‘octillion’, ‘nonillion’, ‘decillion’, ‘undecillion’, ‘duodecillion’, ‘tredecillion’, ‘quattuordecillion’,
‘quindecillion’, ‘sexdecillion’, ‘septendecillion’, ‘octodecillion’, ‘novemdecillion’, ‘vigintillion’
);
$num_length = strlen($num);
$levels = (int) (($num_length + 2) / 3);
$max_length = $levels * 3;
$num = substr(‘00’ . $num, -$max_length);
$num_levels = str_split($num, 3);
for ($i = 0; $i < count($num_levels); $i++) {
$levels–;
$hundreds = (int) ($num_levels[$i] / 100);
$hundreds = ($hundreds ? ’ ’ . $list1[$hundreds] . ’ hundred’ . ’ ’ : ‘’);
$tens = (int) ($num_levels[$i] % 100);
$singles = ‘’;
if ( $tens < 20 ) {
$tens = ($tens ? ’ ’ . $list1[$tens] . ’ ’ : ‘’ );
} else {
$tens = (int)($tens / 10);
$singles = (int) ($num_levels[$i] % 10);
$singles = ’ ’ . $list1[$singles] . ’ ‘;
}
$words[] = $hundreds . $tens . $singles . ( ( $levels && ( int ) ( $num_levels[$i] ) ) ? ’ ’ . $list3[$levels] . ’ ’ : ‘’ );
} //end for loop
$commas = count($words);
if ($commas > 1) {
$commas = $commas - 1;
}
return implode(’ ', $words);
}

$sql1 = “SELECT id, name FROM seasons WHERE is_ended = 0 ORDER BY date DESC”;

$result = mysqli_query($conn, $sql1) or die(mysqli_error($conn));
$seasons = array();
while ($row2 = mysqli_fetch_assoc($result)) {
$seasons[] = $row2;
}
foreach ($seasons as $key => $season) {
$id = $season[‘id’];
$sql2 = “SELECT game_day FROM matches WHERE is_deleted = 0 AND season_id = ‘{$id}’ AND NOT ‘finished’ ORDER BY date DESC”;

$result2 = mysqli_query($conn, $sql2) or die(mysqli_error($conn));
$gdays = array();
$row4 = mysqli_fetch_assoc($result2);

$gd = $row4[‘game_day’];
$query_Recordset_List = <<<EOF
SELECT
t1.id,
t1.title,
t1.match_date AS dt,
t1.team1_id,
t1.team2_id,
t1.status,
t1.game_day,
t1.stadium,
t2.name AS home_team_name,
t3.name AS away_team_name,
t2.logo AS home_team_logo,
t3.logo AS away_team_logo
FROM matches t1
LEFT JOIN teams t2 ON t1.team1_id = t2.id
LEFT JOIN teams t3 ON t1.team2_id = t3.id
WHERE
t1.is_deleted = 0
AND season_id = ‘{$id}’
AND game_day = ‘{$gd}’
AND NOT ‘finished’
ORDER BY t1.date DESC
EOF;

$Recordset_List = mysqli_query($conn, $query_Recordset_List) or die(mysqli_error($conn));
$totalRows_Recordset_List = mysqli_num_rows($Recordset_List);

$matches = array();

while ($row_Recordset_List = mysqli_fetch_assoc($Recordset_List)) {
if ($use_sef_links == true){
$sef_link = ‘’;
$sef_link .= $row_Recordset_List[‘title’] . ’ ';
$sef_link .= ‘(’ . $row_Recordset_List[‘home_team_name’] . ’ ‘;
$sef_link .= ‘vs ’ . $row_Recordset_List[‘away_team_name’] . ‘) ‘;
$sef_link .= $row_Recordset_List[‘match_date’];
$sef_link = str_replace("-", " ", $sef_link);
$sef_link = preg_replace(’#\s{1,}#’, " “, $sef_link);
$sef_link = str_replace(” ", “-”, $sef_link);
$sef_link = preg_replace(’#[^a-zA-Z0-9-]#’, “”, $sef_link);
$sef_link .= ‘_m’. $row_Recordset_List[‘id’] . ‘.html’;

    $row_Recordset_List['link'] = $sef_link;
}
else{
    $row_Recordset_List['link'] = 'match.php?id=' . $row_Recordset_List['id'];
}
$matches[] = $row_Recordset_List;

}

?>

                <?php foreach ($matches as $key => $match) { ?>

                <?php } ?>

<?php } ?>[/php]

[phdr]

The simplest and most general purpose way of doing this is to pivot/index the data when you retrieve it, using the season name and the game_day values as array indexes for the $matches array. Then simply loop over the resulting matches array, first to get the season name and the arrays of game_day data, then loop over each array of game_day data to get the game day name and the arrays of team data, then loop over the array of team data to produce the individual output.

But first, you need to simply all the query code to use just one query that gets the data you want in the order that you want it. This will greatly simplify all the code/queries and it will greatly speed up the code/queries (each query you run requires a communication between php and the database server, and for straightforward queries, this communication time is several times longer than the amount of time it takes to actually execute the query.)

If you are querying for only the current season, you should add that as a condition in the query. The current logic will display results for all seasons that there is data for.

Also, this part of the query - AND NOT ‘finished’ makes no sense and is probably causing the query to match no data (the string ‘finished’ is a true value and NOT that is a false value.) That condition should include a table/column name - AND some table/column is NOT ‘finished’?

If I get a chance, I will post the simplified code/query and how to pivot/index the data when building the $matches array.

[phdr]

The following is the query related code that should (untested) produce the $matches array as suggested above -
[php]// one query to get the data you want in the order that you want it
$sql = "SELECT
s.name AS season_name,
m.id,
m.title,
m.match_date AS dt,
m.status,
m.game_day,
m.stadium,
t_h.name AS home_team_name,
t_a.name AS away_team_name,
t_h.logo AS home_team_logo,
t_a.logo AS away_team_logo
FROM seasons s
JOIN matches m ON s.id = m.season_id
JOIN teams t_h ON m.team1_id = t_h.id
JOIN teams t_a ON m.team2_id = t_a.id
WHERE
s.is_ended = 0 AND
m.is_deleted = 0 AND
m.status NOT ‘finished’
ORDER BY s.date DESC, m.match_date DESC
";

$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));

$totalRows = mysqli_num_rows($result);

$matches = array();

while ($row = mysqli_fetch_assoc($result))
{
if ($use_sef_links == true)
{
$sef_link = “{$row[‘title’]} ({$row[‘home_team_name’]} vs {$row[‘away_team_name’]}) {$row[‘dt’]}”;
$sef_link = str_replace("-", " ", $sef_link);
$sef_link = preg_replace(’#\s{1,}#’, " “, $sef_link);
$sef_link = str_replace(” ", “-”, $sef_link);
$sef_link = preg_replace(’#[^a-zA-Z0-9-]#’, “”, $sef_link);
$sef_link .= ‘_m’. $row[‘id’] . ‘.html’;

	$row['link'] = $sef_link;
}
else
{
	$row['link'] = 'match.php?id=' . $row['id'];
}

$matches[$row['season_name']][$row['game_day']][] = $row;

}[/php]

I took a guess that the NOT ‘finished’ part in the query referred to the matches status column.

You should be able to figure out from here how to loop over the $matches array to produce the output that you want. You can use the following to see what the $matches array looks like -

[php]echo ‘

’; print_r($matches); echo ‘

’;[/php]