-1

I'm in the process of exporting a gallery from Drupal to Wordpress. I'm trying to create an SQL query on the drupal DB that will return to me the URIs of the files. (for automated import on the other side)

This seems like the easiest and non-intrusive way of getting the images. However, at the end I get public://some_file_name.jpg type of strings. When I look at the current live site, it's also clear that the value of public:// is vastly different for different images / galleries.

This is my Query so far:

SELECT n.nid, td.description 'category', n.title 'album_title', n.created, img_title.field_photos_title 'img_title', f.uri
FROM bcb_old.node n
  JOIN bcb_old.field_data_field_photos img_title on n.nid = img_title.entity_id
    JOIN bcb_old.taxonomy_index ti on ti.nid = n.nid
    JOIN bcb_old.taxonomy_term_data td on ti.tid = td.tid
    JOIN bcb_old.file_managed f on f.fid = img_title.field_photos_fid
WHERE n.type ='gallery_2' 
 AND n.status = 1

Can someone point me in the direction of how to translate public:// into either a url? (relative or absolute doesn't matter)

edit: This is not a duplicate of this. I'm asking for a SQL solution. Not a PHP one. I don't want to add code to the live environment and it is not my place to do so. That's why I want a DB only solution.

1
  • @marcvangend I'm asking for an SQL way to do it. There's many PHP ways to do it, like the link you refer to, but I prefer not touching the running environment as it is live, and the server is not under my maintenance, and I have very little Drupal experience.
    – Coo
    Commented Feb 29, 2016 at 10:09

1 Answer 1

0

You can't do that through SQL directly; you will have to use Drupal to generate those paths for you.

Since you seem to be doing this manually (i.e. without an importing tools), and one time only, here's what I would do:

  1. Create a new table where you will store formatted information. This table would have nid, category, album_title, created, img_title and uri columns.

  2. Install Devel module and then navigate to /devel/php.

  3. Execute the following code:

    $results = db_query("SELECT
                          n.nid,
                          td.description 'category',
                          n.title 'album_title',
                          n.created,
                          img_title.field_photos_title 'img_title',
                          f.uri
                        FROM bcb_old.node n
                        JOIN bcb_old.field_data_field_photos img_title on n.nid = img_title.entity_id
                        JOIN bcb_old.taxonomy_index ti on ti.nid = n.nid
                        JOIN bcb_old.taxonomy_term_data td on ti.tid = td.tid
                        JOIN bcb_old.file_managed f on f.fid = img_title.field_photos_fid
                        WHERE
                          n.type ='gallery_2' AND
                          n.status = 1")->fetchAll();
    foreach ($results as $result) {
      // Generate the full URL for this file.
      $image_url = file_create_url($result->uri);
      // Insert this in the new table.
      db_insert('YOUR_NEW_TABLE')
        ->fields(array(
          'nid'                => $result->nid,
          'description'        => $result->description,
          'title'              => $result->title,
          'created'            => $result->created,
          'field_photos_title' => $result->field_photos_title,
          'uri'                => $image_url,
        ))
        ->execute();
    }                          
    

Then you can migrate the data from this new table to wherever you need it.

2
  • I was considering something along these lines. Problem is, the server isn't under my maintenance. And it's live. And my Drupal experience is so small that if I'd mess anything up, I probably wouldn't be able to quick fix it. I suppose I could download a complete copy of the files as well (already have the DB) and start my own local test env. Thank you for the insight!
    – Coo
    Commented Feb 29, 2016 at 10:13
  • In that case, the code above might be your best option. All you would need is the database access. However, before you do anything, make a full backup of the DB, in case anything goes wrong, and do it first in your dev environment. Good luck! Commented Feb 29, 2016 at 10:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.