In article <863d38a1.0404260430.2a765d1c@posting.google.com >,
kinskai@aol.com (none) wrote:
[color=blue]
> Hi all, I'm trying to think of the most logical solution fro this
> problem I have. I have a table with 'date' in the format dd.mm.yy and
> the column just uses 'varchar' and also another column with an 'id'
> number in it. What I want to do is build a simple 2 row table with the
> months of the year in the first row then the records with a date which
> falls in that month will be displayed in the cell under the
> corresponding month, like this;
>
> | january | february | march | etc.
> course 1 course 5
> course 3
>
> I'm planning to use the 'id' entry of the table to do an inner join to
> get the relevant info I need for the display, but what is the best way
> to query MySQL to grab all available records and sort them into
> months? Do I have to take the dates and strip the 'dd' and 'yy' of so
> that only 'mm' is referenced? I'm really hitting the limit of my
> skills here.
>
> Many thanks,[/color]
You can of course strip the dd. and .yy in the SELECT:
SELECT SUBSTRING(date_field,4,2) AS month FROM .. ORDER BY month
<http://dev.mysql.com/doc/mysql/en/String_functions.html>
SUBSTRING(str, pos, len). I forget if MySQL starts counting from 0 or 1,
so the second argument might be 3 instead of 4.
In your place I would probably change the column from VARCHAR to a
proper DATE, however, so that it is possible to select months etc.
explicitly.
JP
--
Sorry, <devnull@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.
