I've been looking at different CRUD's that work fine when it's just a table, but how can I get CRUD functionality on the results of pivot query?
I made a fiddle here: http://www.sqlfiddle.com/#!9/12eb0/3/0
Are there any solutions that will help me edit those results... or add/delete?
Edit: Added the code here:
CREATE TABLE IF NOT EXISTS `drivers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`status` CHAR(1) NOT NULL,
PRIMARY KEY (`id`))
;
INSERT INTO `drivers` (`id`, `name`, `status`) VALUES
(1, 'Tony Stark', 'A'),
(2, 'Steve Rogers', 'A'),
(3, 'Bruce Banner', 'A')
;
CREATE TABLE IF NOT EXISTS `products` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`description` VARCHAR(255) NOT NULL,
`status` CHAR(1) NOT NULL,
PRIMARY KEY (`id`))
;
INSERT INTO `products` (`id`, `description`, `status`) VALUES
(1, 'Sandwich Bread', 'A'),
(2, 'Buns', 'A'),
(3, 'Rolls', 'A')
;
CREATE TABLE IF NOT EXISTS `production_list_header` (
`list_number` VARCHAR(255) NOT NULL,
`date_created` DATE NOT NULL,
PRIMARY KEY (`list_number`))
;
INSERT INTO `production_list_header` (`list_number`, `date_created`) VALUES
('20150826112314', '2015-08-26'),
('20150827085030', '2015-08-27')
;
CREATE TABLE IF NOT EXISTS `production_list_detail` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`list_number` VARCHAR(255) NOT NULL,
`product_id` INT UNSIGNED NOT NULL,
`driver_id` INT UNSIGNED NOT NULL,
`quantity` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `fk_production_list_detail_production_list_header_idx` (`list_number` ASC),
INDEX `fk_production_list_detail_products1_idx` (`product_id` ASC),
INDEX `fk_production_list_detail_drivers1_idx` (`driver_id` ASC),
CONSTRAINT `fk_production_list_detail_production_list_header`
FOREIGN KEY (`list_number`)
REFERENCES `production_list_header` (`list_number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_production_list_detail_products1`
FOREIGN KEY (`product_id`)
REFERENCES `products` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_production_list_detail_drivers1`
FOREIGN KEY (`driver_id`)
REFERENCES `drivers` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
;
INSERT INTO `production_list_detail` (`id`, `list_number`, `product_id`, `driver_id`, `quantity`) VALUES
(1, '20150826112314', 1, 1, 500),
(2, '20150826112314', 1, 2, 600),
(3, '20150826112314', 1, 3, 550),
(4, '20150826112314', 2, 1, 450),
(5, '20150826112314', 2, 2, 575),
(6, '20150826112314', 2, 3, 350),
(7, '20150826112314', 3, 1, 670),
(8, '20150826112314', 3, 2, 920),
(9, '20150826112314', 3, 3, 250)
;
And the query is:
SELECT p.description,
SUM(IF(dr.name = 'Tony Stark', d.quantity, 0)) as 'Tony Stark',
SUM(IF(dr.name = 'Steve Rogers', d.quantity, 0)) as 'Steve Rogers',
SUM(IF(dr.name = 'Bruce Banner', d.quantity, 0)) as 'Bruce Banner'
FROM production_list_detail d
join production_list_header h on h.list_number = d.list_number
join products p on p.id = d.product_id
join drivers dr on dr.id = d.driver_id
where d.list_number = '20150826112314'
group by p.description;
How can I edit the results of that query any which way... php, crud, mysql client program?
