mySQL command to aggregate matches (Drupal to WordPress migration)

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.

Read More

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.

Related posts

Leave a Reply

1 comment

  1. try the group_concat as following:

    UPDATE IGNORE wordpress.wp_posts p, drupal.content_field_story_image i, drupal.files f
    SET p.post_content =
     CONCAT(
      group_concat(f.filename SEPARATOR ' '), ' ', 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'
      );