I’m currently doing a migration of a Drupal blog to WordPress on a MySQL database.
I’ve imported all of my posts, and I’m currently trying to associate all of my drupal post images (located in a story_image widget) to my wordpress posts.
When I run this query:
UPDATE IGNORE wordpress.wp_posts p, drupal.content_field_story_image i, drupal.files f
SET p.post_content =
CONCAT(
f.filename, ' ', p.post_content
)
WHERE p.ID = i.nid
AND i.field_story_image_fid = f.fid
AND (
f.filename LIKE '%.jpg'
OR f.filename LIKE '%.jpeg'
OR f.filename LIKE '%.png'
OR f.filename LIKE '%.gif'
);
all of my wordpress posts are prepended with exactly one of their images, though I want it to prepend a list of all of them.
I’m a bit rusty with sql stuff, and would like to know what the statement would be to append the filename each time there is a proper match, and not just once.
Here is a simplified version of the databases and tables I am working with
DATABASE: wordpress
TABLE: wp_posts
id | post_content
------------------
1 | hello
DATABASE: drupal
TABLE: content_field_story_image
nid | field_story_image_fid
----------------------------
1 | 18
1 | 19
TABLE: files
fid | filename
---------------
18 | alpha.jpeg
19 | beta.jpeg
Expected post_content after update:
beta.jpeg alpha.jpeg hello
Actual post_content after update:
alpha.jpeg hello
I probably shouldn’t even have it in an UPDATE…SET to begin with. Thanks in advance for any suggestions.
UPDATES:
Thanks to the answers provided I’ve been playing around with GROUP_CONCAT
I now have this query which returns a list of images for each post
SELECT GROUP_CONCAT(drupal.files.filename SEPARATOR ' ')
FROM wordpress.wp_posts , drupal.content_field_story_image, drupal.files
WHERE wordpress.wp_posts.ID = drupal.content_field_story_image.vid
AND drupal.content_field_story_image.field_story_image_fid = drupal.files.fid
AND (
drupal.files.filename LIKE '%.jpg'
OR drupal.files.filename LIKE '%.jpeg'
OR drupal.files.filename LIKE '%.png'
OR drupal.files.filename LIKE '%.gif'
)
GROUP BY wordpress.wp_posts.ID;
Now just looking for a way to insert that list into each wordpress post.
try the group_concat as following: