I'm trying to build a html table from a mysql database. The months of the year will be the across the top of the table(Jan-Dec), and the years will be the first column on the left, 2015 at the top and 2008 at the bottom. Currently I have it ordered by date, descending. This means the latest item will be at the top left position. However I need it to be sorted by ASC for the months of the year, and DSC for the years. I'm assuming I need to user DATE_FORMAT but I'm not sure how to implement this. The date format in the database is like this: 2014-07-01
$resource_where_conditions = '`publish` = \'y\' and `category_id` = ' . $cat_data['id'];
$date_result = $db->selectByStrings($resource_data_fields, '`resource`', $resource_where_conditions, '`date` desc');
$current_year = '';
$base_url_query = (_USE_SEO_URLS === true) ? $_SERVER['PHP_SELF'] . '?' : $_SERVER['REQUEST_URI'] . '&';
if ($req->isRequest ('year')) $url_year = $req->getRequest ('year');
if ($db->getNumRows($date_result) > 0) {
$copy_investor_newsletter .= $tab0 . '<table class="table table-bordered table-responsive"><tbody><thead><tr><th>January</th><th>Feburay</th><th>March</th><th>April</th><th>May</th><th>June</th><th>July</th><th>August</th><th>September</th><th>October</th><th>November</th><th>December</th></tr></thead>' . $retn;
while ($data = $db->getNextRow($date_result)) {
if (substr ($data['date'], 0, 4) != $current_year) {
$current_year = substr ($data['date'], 0, 4);
if (!isset ($latest_year)) $latest_year = $current_year;
if (!isset ($url_year)) {
$navsel = ' class="ActNav"';
$url_year = $current_year;
} elseif ($url_year == $current_year) {
$navsel = ' class="ActNav"';
$selected_year = $url_year;
} else $navsel = '';
$copy_investor_newsletter .= $tab1 . '<tr>';
// if ($url_year == $current_year) {
/*
* read and display all newsletters for selected year
*/
$resource_where_conditions = '`publish` = \'y\' and `category_id` = ' . $cat_data['id'] . ' and substr(`date`,1,4) = \'' . $current_year . '\'';
$resource_result = $db->selectByStrings($resource_data_fields, '`resource`', $resource_where_conditions, '`date` desc');
if ($db->getNumRows($resource_result) > 0) {
// $copy_investor_newsletter .= $retn . $tab2 . '<ul>' . $retn;
while ($data = $db->getNextRow($resource_result)) {
$copy_investor_newsletter .= $tab3 . '<td><a href="/includes/act_download.php?fid=' . $this_fund_id . '&rid=' . $data['id'] . '&cid=' . $cat_data['id'] . '&download=' . $data['file'] . '" target="_blank">' . $data['heading'] . '</a></td>' . $retn;
}
// $copy_investor_newsletter .= $tab2 . '</ul>' . $retn . $tab1;
}
// }
$copy_investor_newsletter .= '</tr>' . $retn;
}
}
$copy_investor_newsletter .= $tab0 . '</tbody></table>' . $retn . $retn;
}
Try this SQL query to get your desired ordering:
SELECT * FROM Table ORDER BY YEAR(date) DESC, MONTH(date) DESC
Don't fully understand, what is the difference between what you have now and what you want to get
But I think you should look in the direction of:
select date_format(date, '%c') month, date_format(date, '%Y') year, id, name, etc
from ... order by month ASC, year DESC
So you select month in numeric presentation and then you can order by it.