Dhenn Dhenn - 2 months ago 4
MySQL Question

MySQL - Display months dynamically according to user input

I am not sure how I am going to do this. I'm on my wit's end and exhausted all the resources I have right now.

Alright, I have one database with column, monthname, fiscal_year, and amount.

DROP TABLE IF EXISTS `savings`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `savings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`account_id` int(10) NOT NULL,
`monthname` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`fiscal_year` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`amount` decimal(30,2) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `net_savings_id_unique` (`id`,`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=841 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;


And here's the content:

LOCK TABLES `savings` WRITE;
/*!40000 ALTER TABLE `net_savings` DISABLE KEYS */;
INSERT INTO `net_savings` VALUES (1,1,'January','2016',-1291.47,'2016-09-23 06:45:00','2016-09-23 06:45:00'),(2,1,'February','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(3,1,'March','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(4,1,'April','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(5,1,'May','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(6,1,'June','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(7,1,'July','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(8,1,'August','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(9,1,'September','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(10,1,'October','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(11,1,'November','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(12,1,'December','2016',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(13,1,'January','2017',389296.02,'2016-09-23 06:45:00','2016-09-23 06:45:00'),(14,1,'February','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(15,1,'March','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(16,1,'April','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(17,1,'May','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(18,1,'June','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(19,1,'July','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(20,1,'August','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(21,1,'September','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(22,1,'October','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(23,1,'November','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(24,1,'December','2017',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(25,1,'January','2018',216143.70,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(26,1,'February','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(27,1,'March','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(28,1,'April','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(29,1,'May','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(30,1,'June','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(31,1,'July','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(32,1,'August','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(33,1,'September','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(34,1,'October','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(35,1,'November','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(36,1,'December','2018',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(37,1,'January','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(38,1,'February','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(39,1,'March','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(40,1,'April','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(41,1,'May','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(42,1,'June','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(43,1,'July','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(44,1,'August','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(45,1,'September','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(46,1,'October','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(47,1,'November','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(48,1,'December','2019',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(49,1,'January','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(50,1,'February','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(51,1,'March','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(52,1,'April','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(53,1,'May','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(54,1,'June','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(55,1,'July','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(56,1,'August','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(57,1,'September','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(58,1,'October','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(59,1,'November','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(60,1,'December','2020',0.00,'2016-09-23 06:45:00','2016-09-28 02:36:34'),(61,2,'January','2016',53840.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(62,2,'February','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(63,2,'March','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(64,2,'April','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(65,2,'May','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(66,2,'June','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(67,2,'July','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(68,2,'August','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(69,2,'September','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(70,2,'October','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(71,2,'November','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(72,2,'December','2016',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(73,2,'January','2017',1669693.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(74,2,'February','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(75,2,'March','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(76,2,'April','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(77,2,'May','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(78,2,'June','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(79,2,'July','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(80,2,'August','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(81,2,'September','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(82,2,'October','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(83,2,'November','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(84,2,'December','2017',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(85,2,'January','2018',1980328.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(86,2,'February','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(87,2,'March','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(88,2,'April','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(89,2,'May','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(90,2,'June','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(91,2,'July','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(92,2,'August','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(93,2,'September','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(94,2,'October','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(95,2,'November','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(96,2,'December','2018',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(97,2,'January','2019',2428559.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(98,2,'February','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(99,2,'March','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(100,2,'April','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(101,2,'May','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(102,2,'June','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(103,2,'July','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(104,2,'August','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(105,2,'September','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(106,2,'October','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(107,2,'November','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(108,2,'December','2019',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(109,2,'January','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(110,2,'February','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(111,2,'March','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(112,2,'April','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(113,2,'May','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(114,2,'June','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(115,2,'July','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(116,2,'August','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(117,2,'September','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(118,2,'October','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(119,2,'November','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(120,2,'December','2020',0.00,'2016-09-23 06:57:19','2016-09-23 07:27:11'),(181,4,'January','2016',963.32,'2016-09-23 07:11:42','2016-09-23 07:11:42'),(182,4,'February','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(183,4,'March','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(184,4,'April','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(185,4,'May','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(186,4,'June','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(187,4,'July','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(188,4,'August','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(189,4,'September','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(190,4,'October','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(191,4,'November','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(192,4,'December','2016',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(193,4,'January','2017',3960.93,'2016-09-23 07:11:42','2016-09-23 07:11:42'),(194,4,'February','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(195,4,'March','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(196,4,'April','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(197,4,'May','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(198,4,'June','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(199,4,'July','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(200,4,'August','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(201,4,'September','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(202,4,'October','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(203,4,'November','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(204,4,'December','2017',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(205,4,'January','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(206,4,'February','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(207,4,'March','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(208,4,'April','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(209,4,'May','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(210,4,'June','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(211,4,'July','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(212,4,'August','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(213,4,'September','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(214,4,'October','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(215,4,'November','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(216,4,'December','2018',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(217,4,'January','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(218,4,'February','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(219,4,'March','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(220,4,'April','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(221,4,'May','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(222,4,'June','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(223,4,'July','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(224,4,'August','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(225,4,'September','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(226,4,'October','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(227,4,'November','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(228,4,'December','2019',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(229,4,'January','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(230,4,'February','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(231,4,'March','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(232,4,'April','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(233,4,'May','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(234,4,'June','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(235,4,'July','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(236,4,'August','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(237,4,'September','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(238,4,'October','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(239,4,'November','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(240,4,'December','2020',0.00,'2016-09-23 07:11:42','2016-09-26 03:02:14'),(241,5,'January','2016',-261.74,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(242,5,'February','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(243,5,'March','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(244,5,'April','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(245,5,'May','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(246,5,'June','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(247,5,'July','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(248,5,'August','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(249,5,'September','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(250,5,'October','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(251,5,'November','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(252,5,'December','2016',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(253,5,'January','2017',24160.80,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(254,5,'February','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(255,5,'March','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(256,5,'April','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(257,5,'May','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(258,5,'June','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(259,5,'July','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(260,5,'August','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(261,5,'September','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(262,5,'October','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(263,5,'November','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(264,5,'December','2017',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(265,5,'January','2018',24160.80,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(266,5,'February','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(267,5,'March','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(268,5,'April','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(269,5,'May','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(270,5,'June','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(271,5,'July','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(272,5,'August','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(273,5,'September','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(274,5,'October','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(275,5,'November','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(276,5,'December','2018',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(277,5,'January','2019',8053.60,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(278,5,'February','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(279,5,'March','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(280,5,'April','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(281,5,'May','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(282,5,'June','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(283,5,'July','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(284,5,'August','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(285,5,'September','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(286,5,'October','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(287,5,'November','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(288,5,'December','2019',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(289,5,'January','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(290,5,'February','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(291,5,'March','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(292,5,'April','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(293,5,'May','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(294,5,'June','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(295,5,'July','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(296,5,'August','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(297,5,'September','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(298,5,'October','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(299,5,'November','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(300,5,'December','2020',0.00,'2016-09-23 07:20:36','2016-09-23 07:20:36'),(301,6,'January','2016',8846.54,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(302,6,'February','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(303,6,'March','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(304,6,'April','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(305,6,'May','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(306,6,'June','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(307,6,'July','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(308,6,'August','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(309,6,'September','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(310,6,'October','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(311,6,'November','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(312,6,'December','2016',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(313,6,'January','2017',41141.34,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(314,6,'February','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(315,6,'March','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(316,6,'April','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(317,6,'May','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(318,6,'June','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(319,6,'July','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(320,6,'August','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(321,6,'September','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(322,6,'October','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(323,6,'November','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(324,6,'December','2017',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(325,6,'January','2018',41141.34,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(326,6,'February','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(327,6,'March','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(328,6,'April','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(329,6,'May','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(330,6,'June','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(331,6,'July','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(332,6,'August','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(333,6,'September','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(334,6,'October','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(335,6,'November','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(336,6,'December','2018',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(337,6,'January','2019',41141.34,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(338,6,'February','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(339,6,'March','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(340,6,'April','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(341,6,'May','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(342,6,'June','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(343,6,'July','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(344,6,'August','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(345,6,'September','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(346,6,'October','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(347,6,'November','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(348,6,'December','2019',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(349,6,'January','2020',27427.56,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(350,6,'February','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(351,6,'March','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(352,6,'April','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(353,6,'May','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(354,6,'June','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(355,6,'July','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(356,6,'August','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(357,6,'September','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(358,6,'October','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(359,6,'November','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11'),(360,6,'December','2020',0.00,'2016-09-23 07:34:11','2016-09-23 07:34:11');
/*!40000 ALTER TABLE `net_savings` ENABLE KEYS */;
UNLOCK TABLES;


As you can see, this is pretty much getting more complicated for me as the user, wanted to input four values.

From: Month and Fiscal Year


To: Month and Fiscal Year

Now If I were the user, I would like to input June 2016 and March 2017. My result set will be displaying:

June, July, August, Sept, October, November, December(2016), January, February, March (2017)

So this is going to be very dynamic, and I only have two columns to use and both of them are storing strings.

This is my current code and I stucked here.

select a.id, a.account, a.region, sum(n.amount) as 'TotalNet',
sum(case when n.monthname = "November" then n.amount else 0 end) as `Nov`,
sum(case when n.monthname = "December" then n.amount else 0 end) as `Dec`,
sum(case when n.monthname = "January" then n.amount else 0 end) as `Jan`,
sum(case when n.monthname = "February" then n.amount else 0 end) as `Feb`,
sum(case when n.monthname = "March" then n.amount else 0 end) as `Mar`,
sum(case when n.monthname = "April" then n.amount else 0 end) as `Apr`,
sum(case when n.monthname = "May" then n.amount else 0 end) as `May`,
sum(case when n.monthname = "June" then n.amount else 0 end) as `Jun`,
sum(case when n.monthname = "July" then n.amount else 0 end) as `Jul`,
sum(case when n.monthname = "August" then n.amount else 0 end) as `Aug`,
sum(case when n.monthname = "September" then n.amount else 0 end) as `Sep`,
sum(case when n.monthname = "October" then n.amount else 0 end) as `Oct`
from
savings n left outer join accounts a
on a.id = n.account_id
where fiscal_year = 2016
group by a.account
order by a.account desc;


Please help sql experts! Thanks in advance.

Answer

General

You cannot dynamically generate columns. So in the MySQL only solution we focus on creating rows. On the PHP solution we are doing the transposition in PHP.

PHP Approach

Idea

Let us query our table row by row. Then transpose it. We also generate an entry for every month between start and end in order to always have an entry.

Implementation

SELECT a.account_id, fiscal_year, monthname, SUM(n.amount) as ThisMonth
FROM  savings n
LEFT OUTER JOIN accounts a
     ON .....
WHERE fiscal_year >= YEAR($start_date)
     AND monthname >= MONTHNAME($start_date)
     AND fiscal_year <= YEAR($end_date)
     AND monthname <= MONTHNAME($end_date)
GROUP BY c.date;

where we generate

$start_date = DateTime::createFromFormat('%M %Y', $input_start);
$end_date = DateTime::createFromFormat('%M %Y', $input_end);
$interval = DateInverall:createFromDateString('1 month');

Now let's transpose it, from rows to columns:

// QUERY OUR MYSQL USING PREPARED STATEMENTS
$dates = [];
for ($date = $start_date; $date <= $end_date; $date->add($interval)) {
   $dates[] = $date;
}

$accounts = [];
foreach($rows as $row) {
    if (!isset($accounts[$row['account_id']])) {
        $entry = []
        $accounts[$row['account_id']] = $entry;
    }
    $fyear = $row['fiscal_year'];
    $month = $row['monthname'];
    $accounts[$row['account_id']][$fyear][$month] = $row['TotalMonth'];
}

//print header. We are doing this very stupid here ,
//you want some sort of table layout
echo "Account ID";
foreach($dates as $date) {
    echo $date;
}
echo "\n";
// output our data
// at this point accounts is a multidimensional array: 
foreach($account as $aid => $account) {
   echo $aid;
   foreach($dates as $date) {
      $value = 0;
      if (!isset($account[$date->format('%Y')][$date->format('%M')])) {
         $value = $account[$date->format('%Y')][$date->format('%M')];
      }
      echo $value;
   }
   echo "\n";
}

MySQL only (per-row) approach

The idea

This solution focuses on per-row solution. Transposition can be done in PHP afterwards.

I assume we want to generate every month no matter if there is an entry in savings or not.

In order for any select statement to succeed we need a table to select from. While we can generate arbitrary values for every entry, we still need entries available.

In our case the easiest is to generate a table with dates.

The calendar table

Let's generate a table that contains entries from 1900 to 2100:

DELIMITER //
CREATE PROCEDURE gen_cal()
  BEGIN
    CREATE TABLE `calendar` (`date` DATE NOT NULL);
    SET @start = MAKEDATE(1900, 1);
    WHILE @start <= MAKEDATE(2100, 1) DO
      INSERT INTO `calendar` (`date`) VALUES (@start);
      SET @start = ADDDATE(@start, interval 1 month);
    END WHILE;
  END //
DELIMITER ;

Let's generate it using CALL. It generates a table with 2401 entries.

CALL gen_cal();

The select

Let's use our brand new calendar table to select (I removed the account part for simplicity).

SELECT c.date, MONTHNAME(c.date) as month, SUM(n.amount) as ThisMonth
FROM calendar c
LEFT OUTER JOIN savings n
   ON fiscal_year = YEAR(c.date)
     AND monthname = MONTHNAME(c.date)
WHERE c.date
   BETWEEN STR_TO_DATE('1 March 2016', '%d %M %Y')
   AND STR_TO_DATE('1 March 2017', '%d %M %Y')
GROUP BY c.date;

Note the part STR_TO_DATE('1 % %', '%d %M %Y') where we convert the format "1 March 2017" to a date. I assume that you can fill in '1 % %' via a prepared statement with the user given data.

The usage of YEAR and MONTHNAME makes it possible to select the correct date which we can then easily use to match against our user given input.

The usage of LEFT OUTER JOIN ensures we generate an entry for EVERY month, no matter if we have an entry in savings or not.

Drawbacks

We cannot in MySQL generate columns dynamically. So we are restricted to rows.

Comments