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,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.)
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`
The SEPARATOR is optional. Using it allows you to specify the type of separator you want. Here, I specified a pipe.
PostID | TagIDs |
---|---|
3067 | 49|50|1142|59|53 |
Without it, you will get a comma-separated output.
PostID | TagIDs |
---|---|
3067 | 49,50,1142,59,53 |
No comments :
Post a Comment