By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,640 Members | 2,597 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,640 IT Pros & Developers. It's quick & easy.

Best approach for this?

P: n/a
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,
Jul 17 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
In article <86**************************@posting.google.com >,
ki*****@aol.com (none) wrote:
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,


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, <de*****@cauce.org> is een "spam trap".
E-mail adres is <jpk"at"akamail.com>, waarbij "at" = @.
Jul 17 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.