[RESOLVED] MySQL "order by" question

[laduch]

I'm using an open source event calendar system built by php and mysql.

in mysql database - day, month, year are "d" - int , "m" - int, "y" -int...

a example row

m d y
12 1 2007

I know it is a bad practice... I'm just using it for temporary

The problem is, I want to show the upcoming event on the homepage.
So I query data from these tables using following code:

$query = "select * from events ORDER BY y, m, d LIMIT 1";
$result = mysql_query($query, $dbconnect);	
$numRow = mysql_num_rows($result);
echo"<div id='eventText'>";
for($x = 0 ; $x < $numRow ; $x++ ){									
	$row = mysql_fetch_assoc($result);
	echo "
		<div>
		<h4>" . $row['m'] . "-" . $row['d'] . "-" . $row['y'] . "</h4>
		<h4>" . $row['title'] . "</h4>

	<p>" . $row['text'] . "</p>


	</div>";

}
echo "</div>";

Of course script above doesn't do the job...
So, anyone can tell me how to query it out correctly?

[NogDog]

Not counting the fact that I'd put the Y-m-d into a single date type column in the first place, I'd probably do:

$query = "select *, STR_TO_DATE(CONCAT(y,'-',m,'-',d), '%Y-%c-%e') AS sort_date FROM events ORDER BY sort_date DESC LIMIT 1";

[laduch]

thank you! But it only shows the last event of the year.

[Weedpacket]

laduch wrote:

But it only shows the last event of the year.

Any particular reason you're explicitly limiting it to return one result?

[laduch]

I only want to show 1 upcoming event on homepage