Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    74
    Thanks
    19
    Thanked 1 Time in 1 Post

    Sorting Date Fields by Month in DB Query

    I was wondering if there was a way to sort regular date fields in a database (yyyy-mm-dd) by month.

    For instance, I have a database with a start_date and end_date field, and I have to display the listings by month. So I was hoping to be able to do something where I have an array of months, and then I cycle through the months and match those months up with the start_date fields, and then only display listings that match that month. I just put together something below that tries to show what I want to do even though I know it's most likely not the way to do it.

    PHP Code:
    $months = array('January''February''March');

    foreach (
    $months as $value) {
        
    $query mysql_query("SELECT start_date FROM listings ORDER BY start_date DESC");
        while(
    $x mysql_fetch_array($query)) {
            if(
    $value == $x['start_date']) {
            echo 
    '<strong><u>' $value 'Listings</u></strong>';
            
    $query2 mysql_query("SELECT * FROM listings ORDER BY start_date DESC");
            while(
    $y mysql_fetch_array($query2)) {
                  if(
    $value == $y['start_date']) {
                  echo 
    $y['listing_name'];
                  }
            }
            }
        }

    I want it to output something like this:

    January Listings
    Event 1
    Event 2

    February Listings
    Event 3
    Event 4
    Last edited by cgdtalent; 06-12-2013 at 01:43 AM.

  • #2
    Junsee
    Guest
    there is a little mysql function called GROUP BY
    works the same way as ORDER BY (syntax)
    but it groups the entries by the unique values in a field

    after that I would use MONTH()

    so something like

    PHP Code:
    "SELECT start_date FROM listings GROUP BY MONTH(start_date); " 

  • Users who have thanked Junsee for this post:

    cgdtalent (06-13-2013)

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,048
    Thanks
    2
    Thanked 317 Times in 309 Posts
    Group by actually consolidates the rows having the same group by value into one row.

    To do what you want, with the output that you want (month name), do the following -

    1) Run ONE query that gets the rows you want in the order that you want them. You should never run a query inside of a loop. You would also want to order by the start_date ASC (using DESC would show December first, followed by November...)

    2) Since you want the month name in the output, let mysql produce that for you. Add DATE_FORMAT(start_date,'%M') in the SELECT term in the query.

    3) As you loop over the results from your ONE query, detect a change in the month name to output a new month heading, then simply output the data under each heading (untested but should work) -

    Code:
    $query = "SELECT listing_name, DATE_FORMAT(start_date,'%M') as month_name FROM listings ORDER BY start_date";
    $result = mysql_query($query);
    $last_heading = null; // remember the last heading
    while($row = mysql_fetch_assoc($result)){
    	if($last_heading != $row['month_name']){
    		// the heading changed (or is the first one)
    		if($last_heading != null){
    			// it's not the first heading, close out the previous section here
    			echo '<br>'; // you output appears to have some spacing between sections
    		}
    		// output the new heading
    		echo "<strong><u>{$row['month_name']} Listings</u></strong>";
    		$last_heading = $row['month_name']; // remember the new heading
    	}
    	// output the data under each heading
    	echo $row['listing_name'] . '<br>';
    }
    Last edited by CFMaBiSmAd; 06-12-2013 at 01:48 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    cgdtalent (06-13-2013)

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Ummm...there's an even simpler function to use.

    MySQL supports MONTHNAME( ).

    So:
    Code:
    $query = "SELECT listing_name, MONTHNAME(start_date) as month_name "
           . " FROM listings ORDER BY start_date";
    Of course DATE_FORMAT works just as well. Just a bit harder to remember all the possible formats.

    There's a couple of minor things I'd do differently than CFMaBiSmAd did, but his concepts are spot on.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    cgdtalent (06-13-2013)

  • #5
    New Coder
    Join Date
    Jan 2013
    Posts
    74
    Thanks
    19
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    Ummm...there's an even simpler function to use.

    MySQL supports MONTHNAME( ).

    So:
    Code:
    $query = "SELECT listing_name, MONTHNAME(start_date) as month_name "
           . " FROM listings ORDER BY start_date";
    Of course DATE_FORMAT works just as well. Just a bit harder to remember all the possible formats.

    There's a couple of minor things I'd do differently than CFMaBiSmAd did, but his concepts are spot on.
    Thanks everyone for all the great advice....it was exactly what I was looking for. Thanks!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •