PHP Help

hearmeroar

Basically, I need to display 6 "upcoming fixtures" in a table using PHP and MySQL. At the moment, I am pulling the fixtures in order of when they were added (with an auto_increment field called FixtureID) however, how would I go about ordering them by closest to todays date (preferably ordered by time aswell if 2 fixtures are 1 night)

I have a date field and a time field although, Im unsure about ordering queries when the field has an odd format (not an Int).

This is how I am ordering the fixtures at the moment:

<td background="phanq_images/phanq_menulinkleftmiddleedg.gif">
<?php
echo '<span class="Black"><a href="fixtures.php?FixtureID='; //Open a Hyperlink Tag
$sqlfixturesquery = "SELECT * FROM Fixtures ORDER BY FixtureID DESC LIMIT 0, 1"; //Select the closest fixture
$recordset = mysql_query($sqlfixturesquery); //Run the query
$fixturesarray = mysql_fetch_array($recordset); //Turn the record into an array
echo $fixturesarray[FixtureID]; //Write the FixtureID of the most recent match
echo '">'; //Close the tags
echo "pHanQ vs $fixturesarray[Opposition]"; //Write "pHanQ vs" the closest fixture
echo "</a></span>"; //Close the Hyperlink Tag
?>
</td>
<td align="right" background="phanq_images/phanq_menulinkleftmiddleedg.gif">
<?php
echo '<span class="BlackBold"><a href="fixtures.php?FixtureID='; //Open a Hyperlink Tag
echo $fixturesarray[FixtureID]; //Write the FixtureID of the fixture
echo '">'; //Close the tags
echo $fixturesarray[Date];
echo "</a></span>"; //Close the Hyperlink Tag
?>

seamus

Easy enough, MySQL knows how to order any data type. Did you declare the date and time columns as Varchar or DATE and TIME?

Should just be able to use
SELECT * FROM Fixtures ORDER BY Date DESC, Time DESC LIMIT 0, 1

hearmeroar

Thing is, the 'Date' data type is in a really ugly format, its yyyy-mm-dd, I would much prefer it to be dd/mm/yyyy, any way of doing that?

seamus

Well that's just the generic format. MySQL doesn't actually treat it as text. You can use the DATE_FORMAT function to get the data as you want it.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

So
SELECT FixtureID, DATEFORMAT(dateCol, '%d/%c/%Y') as fixdate FROM Fixtures ORDER BY Date DESC, Time DESC LIMIT 0, 1

tomED

You can use the PHP date() function to format the look.

E.g.

<?php

$today = "2006-12-04";

echo date("F j, Y, g:i a",strtotime($today)); // December 4, 2006, 12:00 am

?>

More here > http://ie.php.net/date

hearmeroar

Perfect guys!

Thanks a lot. I ended up using:

$date = $fixturesarray[Date];
echo date("d/m/Y",strtotime($date));

Which is working perfectly!

http://www.itsgameover.co.uk/