[SOLVED] cell from multiple rows as columns

[aunquarra]

Explaining this might be awkward, but if you just see it, it'll make sense real quick. So let's say I have a table like this...

CREATE TABLE `test_table` (`id` int(11) NOT NULL auto_increment, `key_id` int(11) NOT NULL,  `field` varchar(10) NOT NULL,  `value` varchar(10) NOT NULL,  PRIMARY KEY  (`id`));
INSERT INTO `test_table` VALUES (1, 1, 'color', 'red');
INSERT INTO `test_table` VALUES (2, 1, 'size', 'large');
INSERT INTO `test_table` VALUES (3, 2, 'color', 'blue');
INSERT INTO `test_table` VALUES (4, 2, 'size', 'small');

So, to review...

mysql> select * from test_table;
+----+--------+-------+-------+
| id | key_id | field | value |
+----+--------+-------+-------+
|  1 |      1 | color | red   |
|  2 |      1 | size  | large |
|  3 |      2 | color | blue  |
|  4 |      2 | size  | small |
+----+--------+-------+-------+
4 rows in set (0.00 sec)

 

What I would like to do is combine all the `field`s, grouped by per `key_id`, into a single row of the result.

 

So the output would be something similar to this...

+--------+-------+-------+
| key_id | color | size  |
+--------+-------+-------+
|      1 | red   | large |
|      2 | blue  | small |
+--------+-------+-------+

 

And, if other `field`s were added, it would reflect it...

+--------+-------+-------+---------+-------+
| key_id | color | size  | article | price |
+--------+-------+-------+---------+-------+
|      1 | red   | large | pants   | 15.99 |
|      2 | blue  | small | tshirt  | 5.99  |
+--------+-------+-------+---------+-------+

 

A guy I know told me to look into views to accomplish this... While I'm glad he told me to check it out (because they look interesting for future reference), they don't really seem to do what I'm trying to do.

 

If I have to, I'll fall back on rewriting the PHP to do this legwork for me, but if mySQL has some sort of feature to accomplish this with single rows... That would be stinkin' sweet. The current PHP setup is very clean and efficient because it's just looking for one row. If I have to build up that row, it could take a hit performance-wise. So I'm just making sure there's not a mySQL feature I'm just ignorant to...

 

Thanks in advance. PHP freaks forums rock. No, really. You knowledgeable veterans are God's gift to haphazard developers such as myself.

[fenway]

Name/value tables cause all sorts of problems -- this is one of them.  It would be quite difficult -- and extremely inefficient -- to have mysql generate the result you're interested in.  These types of tables are explicitly desgined for cases where you're earching by a known name, not trying to collect them all.  The latter needs to be done in application code.

 

In the boundary case, you'd only have a single table in your database with every field as a name... this is extreme denormalization.

[aunquarra]

So, PHP legwork it is...

 

I hate this model, but unfortunately, the application required it. Thanks.

[fenway]

So, PHP legwork it is...

 

I hate this model, but unfortunately, the application required it. Thanks.

Shouldn't be too much legwork, you're just building a hash.  And I don't know that I'm convince that it's "required"... but work with what you've got.

[aunquarra]

Well, I'm certainly open to suggestion, but I don't see any cleaner way of doing it, short of writing code that continually alters the table structure to fit my original posts' output.

 

Basically, it controls sets of user-definable fields on some forms. Originally, I had a max of four user-definable fields per form, which were each just columns in the tables with correlating data. Efficient and easy, but limited.

 

And now I've learned that unlimited is more important to the users than efficient and easy. So I've got to figure out a way to make the system support an infinite number of fields.

 

If you've got some direction, I'm all ears.

 

----

As for the legwork, it will be a little more complicated than I'd like. The reason I wanted this query was because an existing automated report generating system with user-definable conditions/filters is needing it all to be in a single row from a single query. Just writing the necessary exceptions so I'll even have a place to insert some app scripting to compile the data will be a hassle. A database solution would certainly be ideal. And if that means restructuring, I'm fine with that.

 

 

[edit because I forgot to explain the legwork aspect]

[fenway]

Hmm... how about making a temporary table before you're going to run the report -- i.e. once all of the possible fields are known -- and then simply inserting/updating each row from the existing name/value table? That is, with insert into... on duplicate key update.... ?

[aunquarra]

Well, the reporting is automatically performed, but on access. So it's effectively realtime. Would table creation, the inserts, and dropping (with approximately 1k-10k sets of data) cause any kind of performance issue?

[fenway]

Obviously that would be quite the performance hit, though in principle you could do it memory (though maybe not for a 10k set).  I guess you could have a trigger update such a summary table all the time....

 

The real issue is that it's non-trivial to convert column values into "fields".