How to get the value for public:// from the database [duplicate]

Question

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.

Answer 1

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.