I have to 3 tables to establish one-to-many relationship with files and categories my table schema is as shown below:
categories table
CREATE TABLE `categories` (
`id` INT(3) NOT NULL AUTO_INCREMENT,
`category_name` VARCHAR(25) NOT NULL,
`visible_name` VARCHAR(25) NOT NULL,
`is_active` TINYINT(1) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_file_category_visible_name_colum` (`visible_name`)
)
ENGINE = INNODB;
files table
CREATE TABLE `files` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`title` VARCHAR(200) NOT NULL,
`filename` VARCHAR(255) NOT NULL,
`description` TEXT NOT NULL,
`date_uploaded` BIGINT(20) DEFAULT 0,
`last_updated` BIGINT(20) DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_unique_file` (`filename`)
)
ENGINE = INNODB;
file_categories table
CREATE TABLE `file_categories` (
`fkfile` BIGINT(20) NOT NULL,
`fkcategory` INT(3) NOT NULL,
PRIMARY KEY (`fkfile`, `fkcategory`),
FOREIGN KEY (`fkfile`)
REFERENCES files (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT,
FOREIGN KEY (`fkcategory`)
REFERENCES categories (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
ENGINE = INNODB;
Here are the sample data to I tested with:
INSERT INTO `stackoverflow_qst`.`files` (`id`, `title`, `filename`, `description`, `date_uploaded`, `last_updated`) VALUES (NULL, 'First Dummy File', 'fdf.pdf', 'Just a dummy file', '1552295385043', '0');
INSERT INTO `stackoverflow_qst`.`categories` (`id`, `category_name`, `visible_name`, `is_active`) VALUES (NULL, 'Category 1', 'Category One', '1'), (NULL, 'Category 2', 'Category Two', '2'), (NULL, 'Category 3', 'Category Three', '1');
INSERT INTO `stackoverflow_qst`.`file_categories` (`fkfile`, `fkcategory`) VALUES ('1', '1'), ('1', '2'), ('1', '3');
On selecting the files a wish to select all categories ids to that file as comma separated column with the file my selection query as as shown below:
SELECT f.id, f.title, f.filename, f.description, f.date_uploaded, f.last_updated,
GROUP_CONCAT(DISTINCT fc.fkcategory ORDER BY fc.fkcategory ASC ) AS categories_ids
FROM `files` AS f
INNER JOIN `file_categories` AS fc ON (fc.fkfile = f.id)
GROUP BY fc.fkfile
It's returning other columns correctly except for the categories_ids column which returns a BLOB with an error on preview as shown in this screenshot: categories_ids column BLOB preview Query Result in PHPMyAdmin
I have gone ahead to query just the file_categories table as comma separated but it still returns a blob as usual. I have looked up related question here on StackOverflow and I my query seems ok, some of my lookup are: MySQL : Multiple row as comma separated single row(The answer here is very direct and even point to MySql Doc), MYSQL comma separated IDs vs separate table, and SQL Join multiple ids comma separated but I'm still stuck with the same problem, does it have to do with the MySql Version I'm using?
Currently MySql Version 5.1.41, strictly for the server on which my current project would be deployed.