Hi Guys,
Been a while....
So I thought I'd write a quick article on SELECTING BY MySQL dates.
Now say for example you have a table, like so:
CREATE TABLE `date_test` (
`idorders` int(11) unsigned NOT NULL auto_increment,
`customer_name` varchar(255) default NULL,
`order_date` datetime default '0000-00-00 00:00:00',
PRIMARY KEY (`idorders`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
Field Type Collation Null
------------- ---------------- --------------- ------
idorders int(11) unsigned (NULL)
customer_name varchar(255) utf8_general_ci YES (NULL)
order_date datetime (NULL)
Anyway, on to the excersize!
So say for example you have 4 orders in the database...
INSERT INTO date_test VALUES(NULL, 'Bob Smith', '2006-08-01');
INSERT INTO date_test VALUES(NULL, 'Mark Johnson', '2006-09-22');
INSERT INTO date_test VALUES(NULL, 'Jimmy McDonald', '2007-02-22');
INSERT INTO date_test VALUES(NULL, 'Sky Brandshaw', '2008-03-28');
INSERT INTO date_test VALUES(NULL, 'Dean Smith', '2008-08-01');
INSERT INTO date_test VALUES(NULL, 'Bob Smith', '2008-08-28');
INSERT INTO date_test VALUES(NULL, 'Jimmy McDonald', '2009-02-22');
INSERT INTO date_test VALUES(NULL, 'Bob Smith', '2006-02-28');
Okay, so let's start with getting all historic orders for the month of february (any year):
SELECT `idorders`, `customer_name`, `order_date` FROM date_test WHERE MONTH(order_date) = '2'
Result:
idorders customer_name order_date
3 Jimmy McDonald 2007-02-22 00:00:00
7 Jimmy McDonald 2009-02-22 00:00:00
8 Bob Smith 2006-02-28 00:00:00
You can also do this using the month name (MAKE SURE YOU USE AN UPPERCASE STARTING LETTER)
SELECT `idorders`, `customer_name`, `order_date` FROM date_test WHERE MONTHNAME(order_date) = 'February';
Okay - that worked.
This is highly useful for year-on-year comparison charts.
Now let's do it per year:
SELECT `idorders`, `customer_name`, `order_date` FROM date_test WHERE YEAR(order_date) = '2006';
Result:
idorders customer_name order_date
1 Bob Smith 2006-08-01 00:00:00
2 Mark Johnson 2006-09-22 00:00:00
8 Bob Smith 2006-02-28 00:00:00
You can, of course, use php values to gererate the arguments, using the date function:
$day = date( "j" );
$month = date( "F" );
$year = date( "Y" );
Any questions?
PHP Website Development by Avera Solutions Limited
