I got this code right now, which works perfectly fine:
<?php
$platform_id = 7;
$query="SELECT * FROM game WHERE game_id IN
(SELECT game_id FROM game_platform WHERE platform_id=".$platform_id.")
AND game_release BETWEEN '2015-06-00'
AND '2015-06-30' ORDER BY game_release";
$result=mysqli_query($link,$query) or die (mysqli_error());
if (mysqli_num_rows($result) > 0)
{
echo"<table border=1>
<tr>
<th width=40px>Day</th>
<th width=270px>Title</th>
<th width=203px>Genre</th>
<th width=203px>Developer</th>
<th width=205px>Publisher</th>
<th width=61px>Retail?</th>
<th width=30px height=30px></th>
<th width=104px>Note</th>
</tr>";
while($row=mysqli_fetch_assoc($result))
{
list($year, $month, $day) = explode("-", $row['game_release']);
if (checkdate($month, $day, $year)) {
$date = (new DateTime($row['game_release']))->format("j");
} else {
$date = "TBC";
}
echo"
<tr>
<td> ".$date." </td>
<td>{$row['game_name']}</td>
<td>";
$query="SELECT * FROM genre WHERE genre_id=".$row['game_genre'];
$genreresult=mysqli_query($link,$query);
$genrerow=mysqli_fetch_assoc($genreresult);
echo $genrerow['genre_name'];
echo "</td>
<td>{$row['game_dev']}</td>
<td>{$row['game_pub']}</td>
<td>{$row['game_type']}</td>
<td><a href=\"{$row['game_site']}\" target=\"_blank\"><img src=\"images/officialwebsite.png\" title=\"Official website\"/></a>
<a href=\"{$row['game_trailer']}\" target=\"_blank\"><img src=\"images/youtube.png\" title=\"Trailer\"/></a></td>
<td>{$row['game_note']}</td>
</tr>";
}
echo"</table>";
}
else
{
echo "There don't seem to be any confirmed nor rumoured releases this month!";
}
?>
As you can see, invalid dates are shown as 'TBC'. In my database games which don't have a specific date yet, are added as '2015-09-00' for example. That means that the TBC games are listed at the top of the table because 00 comes before 01.
Is there any way to force them to be shown beneath the games with a valid date?
Change your query as shown below:
$query="SELECT * FROM game WHERE game_id IN (SELECT game_id FROM game_platform WHERE platform_id=".$platform_id.") AND game_release BETWEEN '2015-06-00' AND '2015-06-30' ORDER BY game_release like '%-00%', game_release";