How to Sort Months According to Order

[fireineyes]

I am accessing a mysql table that has a list of events.  In each result, there is a month listed.  So far, I have been able to make each DISTINCT month echo correctly.  I know how to sort alphabetically but not sure the best way to approach trying to put my list in a particular order.  Here is what my code looks like..

 

$query = "SELECT DISTINCT Month FROM event WHERE Year = $url_year";
$result = mysql_query($query);

while ($record = mysql_fetch_assoc($result)){

$Mo = $record['Month'];


echo '<br>';

switch ($Month)
{
case 1:
  echo 'Jan';
  break;
case 2:
   echo 'Feb';
  break;
case 3:
   echo 'Mar';
  break;
case 4:
   echo 'Apr';
  break;  
case 5:
   echo 'May';
  break;
case 6:
   echo 'Jun';
  break;
case 7:
   echo 'Jul';
  break;
case 8:
   echo 'Aug';
  break;   
case 9:
   echo 'Sep';
  break;
case 10:
   echo 'Oct';
  break;
case 11:
   echo 'Nov';
  break;
case 12:
   echo 'Dec';
  break; 

  
default:
  echo '';
  }


}

 

 

 

The way this is, it simply outputs the months in the order of how they come in the database.  I would like to order how these come out.  I suspect that I have to put them into an array.  Any tips would be appreciated.  In the final output, I really want to want to show the months hyperlinked (I did not do that yet in the switch statement) that are found to exist in the mysql table with a month listed.  The other months would be un-hyperlinked. 

 

The result will be something like:

Jan Feb Mar Apr May Jun Jul Aug Sep Nov Dec

 

Based on what months have records listed.

 

Any advice would be appreciated. I am still a little green at php, so if this is a stupid question, pardon my ignorance :-)   

[Pikachu2000]

Is is just stored in the database as the name of the month by itself in a field, or is it in a timestamp or datetime field?

[PFMaBiSmAd]

Your first step to solving this would be to store your dates in a single DATE data type (YYYY-MM-DD) column.

 

Storing separate year, month, and day values takes more code to accomplish any particular task.

[fireineyes]

I stored it as text. 

 

example row..

Event      |  Month    |  Year      | Day

============================

car show |  Nov        |  2010    |  1

[wildteen88]

The way this is, it simply outputs the months in the order of how they come in the database.  I would like to order how these come out.

You can order the results using the ORDER BY clause, eg

 

order by months in ascending order

SELECT DISTINCT Month FROM event WHERE Year = $url_year ORDER BY Month ASC

 

order by months in descending order

SELECT DISTINCT Month FROM event WHERE Year = $url_year ORDER BY Month ASC

[fireineyes]

wildteen88... would that work only if it is a timestamp? 

[PFMaBiSmAd]

Except that he has the month names stored, not the ordinal month value.

[wildteen88]

Except that he has the month names stored, not the ordinal month value.

Yeah, just noticed that post. Looking at the OP code I thought the month was stored as the ordinal value.

 

wildteen88... would that work only if it is a timestamp? 

No.

[PFMaBiSmAd]

If you are referring to a UNIX Timestamp, they are the worst choice to store data as when dealing with dates that you are trying to manipulate in human terms, such as events ordered by month...

[Pikachu2000]

Change them to a proper format now, before you get too deep into this to do it later (if you aren't already).

[fireineyes]

I am using values because I am getting the values of the webpage from the url.  For example.  If I have this url...

 

http://www.mydomain.com/script/2010/Jan

 

there is a file in the folder called "script" that runs php with a tweak to the htaccess file.  Like this:

 

<Files script> 
ForceType application/x-httpd-php 
</Files>

 

In the script file, it explodes the url and uses the data in the url to return results.  Something like...

$REQUEST_URI = $_SERVER['REQUEST_URI'];

//   BREAK UP THE URL PATH USING '/' as delimiter //////////// 
$url_array=explode("/",$REQUEST_URI);  
$url_dates=$url_array[2]; 	  //d is basic home page for dates...maybe show link to all years.   						   
$url_year=$url_array[3];      //year
$url_month=$url_array[4];        //month 
$url_day=$url_array[5];        //month 

if ($url_day) { 
    include('include/day.php'); 

    exit; 
} 

elseif ($url_month) { 
    include('include/month.php'); 
    exit; 

} elseif ($url_year) { 
    include('include/year.php'); 
    exit; 
}

elseif ($url_dates) { 
   include('include/date.php'); 

   exit; 
}

else { 

    Header( "Location:  http://www.mydomain.com/classic-car-shows/script/"); 
    exit; 
} 

 

 

[fireineyes]

Pikachu2000 - You say to change to correct values.  Please point me to where I can learn this better if you know a place.  In the datatable, I was going to store the months like this..

 

event      | month | year  | day

===================

car show |  5        | 2010 |  3

 

meaning May 3, 2010. 

 

So what do you suggest?  A timestamp?  I am pretty new to working with time and dates.  What I will be doing is setting up a form for people to submit the date and time of an event.  There will be three drop downs in the form for month, day, and year.  The values from those drop downs will be entered into the db. 

 

Any suggestions are appreciated.  At the moment, the way it is listed in my table, it makes it easy to read the datea straight and do a mysql query.

 

[fireineyes]

Would inserting the date like this be better?  Found this code on a tutorial page. 

 

$query_manual = "INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2008-7-04')";

 

 

 

 

[Pikachu2000]

No, it would be worse actually as now you have a whole string to deal with. (EDIT: need to research that a bit before I make that statement....) What is the end goal? Is this to be able display a "drill-down" type of calendar for event listings?

[Pikachu2000]

OK, as I was thinking that doesn't do anything but add 'DATE: Manual Date" to one field, and "2008-7-04" to another. So, it would work if the date field was of the DATE or DATETIME data type, but to add the other field to hold the string "DATE: Manual Date" just seems useless for your application, since all of the dates input to that field will be manual dates.

[fireineyes]

Would it help if I give you a sql dump?

[Pikachu2000]

Just the `event` table structure would be fine for now, I'm sure.

[PFMaBiSmAd]

Storing a date using a standard mysql DATE data type, will -

 

A) Allow you to directly sort dates because ORDER BY your_date_column will work,

B) Allow you to directly compare dates. Any greater-then/less-than comparison between standard DATE values will work,

C) Allow you to use the couple dozen datetime functions directly in your query, such as DATE_FORMAT() to get a standard DATE value or any part of it into any format you want,

D) Will reduce the storage requirement of your data,

E) Will make your queries simpler, faster, and eliminate a lot of the slow php code you need to manipulate your existing data.

[fireineyes]

Sounds good.  I will do a little studying up on the DATE function. 

[myrddinwylt]

Look up the "datetime" field, or "timestamp", either can be sorted by ASC or DESC.

 

The "datetime" field, follows the format "2010-01-31 13:29:29", where the "timestamp" field stores as a Unix timestamp (looks like a really long number) --  "13992988499"  or something like that.

 

Handling different types in php

 

datetime

$val = strtotime($row['datetimefieldname']);

 

timestamp

$val = $row['timestampfieldname'];

 

Sorting in MySQL

....  ORDER BY `datetime_or_timestamp_field_name` ASC ...

 

Both blocks of PHP code above get the Date/Time value in UNIX Timestamp format, which is the easiest to reformat (in my opinion).  See date() for more information on formatting date/times.

 

Sounds good.  I will do a little studying up on the DATE function.