10/07/2013

SQL: How to Combine / Concatenate a Column of Results as a Single Field

I'm transferring posts from an old MovableType database to WordPress. Posts in both content management systems use "Tags" to relate posts to one another by topic or person or whatever the users want. Both systems put Posts in one table, Tags in a second table, and references between the two in yet another table -- one per row.

For my PHP  transfer program, I want to get all the Tag ID numbers that match each post. Then, I'm going to split them apart and

The table is called mt_objecttag.
The Post ID are in the column objecttag_object_id.
The Tag IDs are in the column objecttag_tag_id.

objecttag_id objecttag_blog_id objecttag_object_datasource objecttag_object_id objecttag_tag_id
37 14 entry 3066 52
36 14 entry 3066 51
34 14 entry 3067 49
35 14 entry 3067 50
1361 14 entry 3067 1142
1362 14 entry 3067 59
1363 14 entry 3067 53
27 14 entry 3068 49
28 14 entry 3068 50

I can do this using a GROUP_CONCAT function.

To make it work, I write something that loops through the ID for each Post like this:
SELECT `objecttag_object_id` AS PostID,
GROUP_CONCAT (CONVERT (objecttag_tag_id, CHAR(8) SEPARATOR '|')) AS TagIDs
FROM mt_objecttag
WHERE `objecttag_object_id`= 3067 [insert Post ID Here]
GROUP BY `objecttag_object_id`
For some reason, the Tag ID numbers will be returned as a BLOB, which is useless. So, you have to CONVERT them to a string to get useful values. (Probably, because the output is a string, you have to convert the numbers to strings to be concatenated, too.)

The SEPARATOR is optional. Using it allows you to specify the type of separator you want. Here, I specified a pipe.

PostIDTagIDs
306749|50|1142|59|53

Without it, you will get a comma-separated output.

PostIDTagIDs
306749,50,1142,59,53


No comments :

Post a Comment