[RESOLVED] MySQL Date Query

[mramonster]

I am creating a database for national events and need to display them so the date comes out "Jan 01" instead of the MySQL standard "2008-01-01".

<?php
// DATABASE INFO HERE!

// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
	$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

$query  = "SELECT start_date, end_date, title, city, state FROM motorcycleevents WHERE state='AL' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

echo "<table width=\"675\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">";
// print the random numbers
while(list($start_date, $end_date, $title, $city, $state) = mysql_fetch_array($result))
{ 
	echo "<tr>";
	echo "<td width=\"15%\">";
	echo "$start_date";
	echo "</td>";
	echo "<td width=\"15%\">";
	echo "$end_date";
	echo "</td>";
	echo "<td width=\"45%\">";
	echo "$title";
	echo "</td>";
	echo "<td width=\"20%\">";
	echo "$city";
	echo "</td>";
	echo "<td width=\"5%\">";
	echo "$state";
	echo "</td>";
	echo "</tr>";
}

echo '</table><br />';

// how many rows we have in database
$query   = "SELECT COUNT(start_date) AS numrows FROM motorcycleevents";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
	$page = $pageNum - 1;
	$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";

$first = " <a href=\"$self?page=1\">[First Page]</a> ";
} 
else
{
	$prev  = ' [Prev] ';       // we're on page one, don't enable 'previous' link
	$first = ' [First Page] '; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
	$page = $pageNum + 1;
	$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
} 
else
{
	$next = ' [Next] ';      // we're on the last page, don't enable 'next' link
	$last = ' [Last Page] '; // nor 'last page' link
}

// print the page navigation link
echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;

mysql_close();

?>

and it punches out this:

2008-01-01 2008-01-01 Test Event2 AL
2008-01-01 2008-01-01 Test Event2 AL
2008-01-01 2008-01-01 Test Event2 AL
2008-01-01 2008-01-01 Test Event2 AL
2008-01-01 2008-01-01 Test Event2 AL
2008-01-01 2008-01-01 Test Event2 AL
2008-01-01 2008-01-01 test3 AL

[First Page] [Prev] Showing page 1 of 1 pages [Next] [Last Page]

[bradgrafelman]

So don't select the columns themselves, use MySQL's DATE_FORMAT() function instead.

[mramonster]

Got it! Thanks! Here's the fixed coding!

$query  = "SELECT DATE_FORMAT(start_date, '%b %d'), DATE_FORMAT(end_date, '%b %d'), title, city, state FROM motorcycleevents WHERE state='AL' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');

echo "<table width=\"675\" border=\"0\" cellpadding=\"0\" cellspacing=\"3\">";
// print the random numbers
while(list($start_date, $end_date, $title, $city, $state) = mysql_fetch_array($result))
{ 
	echo "<tr>";
	echo "<td width=\"15%\">";
	echo "$start_date";
	echo "</td>";
	echo "<td width=\"15%\">";
	echo "$end_date";
	echo "</td>";
	echo "<td width=\"45%\">";
	echo "$title";
	echo "</td>";
	echo "<td width=\"20%\">";
	echo "$city";
	echo "</td>";
	echo "<td width=\"5%\">";
	echo "$state";
	echo "</td>";
	echo "</tr>";
}

echo '</table><br />';