The detritus of a Button Monkey

31 Mar

Selecting data from MySQL according to month

I have had reason to select data from a table 0f 8,000 rows according to month. What I wanted to do was create a dynamic page where I could have a drop down box for months and years and then provide data based on the options. The problem I initially had was to get my data into a MySQL compatible format e.g. from dd/mm/yyyy to yyyy-mm-dd. I initially cheated by using Replace within Kate (A text editor) as I knew that this would be the quicker solution for me. I know that more experienced people could use some small script to automate the process. The size of the data involved and length of time it would take for me to write that kind of script meant the long winded approach wasn’t a complete waste of time.

Anyway, now that I had the data, I wanted to select just November for example. Searching in Mysql reference material had lots of useful features about date functions but nothing on what I wanted until I found MONTH(). This seemed to suggest that I could use this. I then used this SQL statement:

SELECT Result, MONTH(2006-11-00) FROM table

This produced results but when I looked at the data, it wasn’t working quite like I wanted. So I went back to the drawing board and used a LIKE approach. Using this statement:

SELECT * FROM `table` WHERE Date LIKE ‘2006-11-%%’

Success!

Leave a Reply

You must be logged in to post a comment.

© 2017 The detritus of a Button Monkey | Entries (RSS) and Comments (RSS)

Design by Your Index - Powered By Wordpress