Best approach for this?

[none]

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,

[Jan Pieter Kunst]

Re: Best approach for this?

In article <863d38a1.04042 60430.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_function s.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" = @.