How to turn rows into columns?

Question

I have a database where I store keywords grouped into projects and data related to each keyword, then I show the datagrids foreach project with one row for each keyword and several columns, all of which are extracted from the same table data . I have 4 tables, keywords, projects, group_keywords and data. “keywords” only stores the keyword, “projects”, project name, “group_keywords”, ids keywords for keywords assigned to this project, and “data” is where all foreach keywords are transmitted, identified by a foreign key for keywords .id and a column of names to identify the data name.

Now, to get the keywords + all the data for the project, I use this query:

SELECT * FROM `group_keywords` INNER JOIN keywords on keywords.id = keyword_id INNER JOIN data ON data.id = keywords.id WHERE `group_id` = (SELECT `id` FROM `projects` WHERE `name` = 'ProjectName' 

It gives me something like

  id group_id keyword_id id keyword id name value 12 5 52 52 absorption food 52 data_name_x1 6 12 5 52 52 absorption food 52 data_name_x2 8 12 5 52 52 absorption food 52 data_name_x3 26 12 5 52 52 absorption food 52 data_name_x4 2 ... 

But I want to get:

 id group_id keyword_id id keyword id data_name_x1 data_name_x2 data_name_x3 data_name_x4 12 5 52 52 absorption food 52 6 8 26 2 ... 

So, I can sort and use pagination for datagrids easily, otherwise I have no idea how to do this, because when using large datasets I cannot just dump everything into an array, too much data.

This is the diagram:

 -- -------------------------------------------------------- -- Table structure for table `keywords` CREATE TABLE IF NOT EXISTS `keywords` ( `id` int(10) unsigned NOT NULL auto_increment, `keyword` varchar(255) NOT NULL, UNIQUE KEY `id` (`id`), UNIQUE KEY `keyword` (`keyword`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=115386 ; -- -------------------------------------------------------- -- Table structure for table `data` CREATE TABLE IF NOT EXISTS `data` ( `id` int(10) unsigned NOT NULL, `name` varchar(100) NOT NULL, `value` varchar(15) NOT NULL, UNIQUE KEY `id` (`id`,`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- Table structure for table `projects` -- CREATE TABLE IF NOT EXISTS `projects` ( `id` int(10) NOT NULL auto_increment, `name` varchar(100) NOT NULL, `parent` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ; -- -------------------------------------------------------- -- Table structure for table `group_keywords` CREATE TABLE IF NOT EXISTS `group_keywords` ( `id` int(10) NOT NULL auto_increment, `group_id` int(10) NOT NULL, `keyword_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `group_id` (`group_id`,`keyword_id`), KEY `keyword_id` (`keyword_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=119503 ; -- -------------------------------------------------------- -- Constraints for table `data` -- ALTER TABLE `data` ADD CONSTRAINT `data_ibfk_1` FOREIGN KEY (`id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -------------------------------------------------------- -- Constraints for table `group_keywords` -- ALTER TABLE `group_keywords` ADD CONSTRAINT `group_keywords_ibfk_1` FOREIGN KEY (`keyword_id`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; 

Answer 1

This operation is traditionally called "univot", and several RDBMS support it, but MySQL is not one of them. You have two options: do it in SQL or do it in PHP. In MySQL, it looks something like this using self-joins (I don’t know which field is suitable for you as an ID field, so I'm sorry that I created my own example). In terms of performance, make sure you provide both the identifier and the column name, otherwise these connections will be scanned.

 shapes ID Name Value 1 Color Red 1 Shape Circle ... for more "columns" 2 Color Green 2 Shape Square ... for more "columns" SELECT A.ID, B.Value as Color, C.Value as Shape ... for more "columns" FROM shapes A LEFT JOIN shapes B ON B.ID = A.ID AND B.Name = 'Color' LEFT JOIN shapes C ON C.ID = A.ID AND C.Name = 'Shape' ... for more "columns" 

Which should cleanse us (if my head-SQL parser is not mistaken today):

 ID Color Shape 1 Red Circle 2 Green Square 

For the PHP version you do not have to load an array, you can pass it. Sort by PC and go down setting properties. In pseudo code:

 Set X to undefined Get a Record Check the ID property, if it different than X, create a new object, set X to the new ID, and yield the previous object Set the property of the object based on the "Name" column of our result 

Hope this helps!