Thread

Using SQL, for files that need the appropriate filedir placed in front of the filename

*this assumes you changed your ngen file field to just “file”, if not run this query to set them to file:

UPDATE exp_matrix_cols SET col_type = 'file' where col_type = 'ngen_file_field'
UPDATE exp_channel_fields SET field_type = 'file' where field_type = 'ftype_id_11'

*note that the query (above) - you need to set the appropriate # for ftype_id_11, you can find that # from exp_ff_fieldtypes.fieldtype_id

this query (below) will change

filename.jpg

https://ellislab.com/asset/images/ent-partner-work/filename.jpg

UPDATE exp_channel_data SET field_id_126 = Concat('https://ellislab.com/asset/images/ent-partner-work/', field_id_126) WHERE (channel_id = 95 AND field_id_126 != '')

*change the _126, _3, 95 to the appropriate id of the field_id, channel_id and filedir you’re looking to change at that time

If you notice two lines of (usually images, with the _thumb.jpg appended to end),

update exp_channel_data set field_id_173 = Concat('https://ellislab.com/asset/images/ent-partner-work/',TRIM(TRAILING '\n' FROM SUBSTRING_INDEX(field_id_173, '\n', 1))) where channel_id = 121 and field_id_173 != ''

that query will change

filename.jpg
filename_thumb.jpg

to

https://ellislab.com/asset/images/ent-partner-work/filename.jpg

the filename_thumb.jpg is the option of thumb being enabled in Mark Huot’s file fieldtype

for files within matrix:

you could append the appropriate filedir to the beginning of the fields like:

UPDATE exp_matrix_data SET col_id_2 = Concat('https://ellislab.com/asset/images/showcase/', col_id_2) where col_id_2 != ''

*as mentioned above, be sure to set the appropriate ID’s within the query

*don’t forget to sync your files within the file manager

If anyone can improve and/or add to this, please respond, I am still in the middle of upgrading a huge v1.7.1 site to v2, so any help is appreciated.

I wrote about this a while ago and migrated the post to my new site Migrating Matrix & Ngen file fields to EE2 Matrix and native file field

I see you concatenated your site URL, did you concat the filedir_# after? Maybe what I am doing is overkill?

Honestly, it was so long ago now, I don’t really remember everything which was why I wrote the blog post in the first place.

Sorry i can’t help further.

No problem, the site I was updating is a 200+ channel site, with over 7000 entries. I’ve been working on batch queries to set every file with the appropriate filedir_3

Added some automation to assigning filedir’s to exp_channel_data

*This is for after upgrading to EE v2

First backup your database, then create a new template and enable php,  copy and paste the code below:

<?php

$this->EE->load->database();

$query = 
$this->EE->db
->select('field_id, field_name, field_label, field_list_items')
->where('field_type', 'file')
->get('exp_channel_fields')
->result_object();

foreach($query as $r): ?>

 updating <?=$r->field_name?> - field_id_<?=$r->field_id?>

<?php
 
 $this->EE->db->query('
 UPDATE 
  exp_channel_data 
 SET 
  field_id_'.$r->field_id.' = concat("{filedir_'.$r->field_list_items.'}", TRIM(TRAILING "\r" FROM SUBSTRING_INDEX(field_id_'.$r->field_id.', "\n", 1)))
 WHERE 
  field_id_'.$r->field_id.' != ""
 ');

endforeach; ?>

This will go thru each file field, and set the appropriate filedir in front of the filename, also it will strip the second line within the cell, usually that happens when you set the directory to save an image file as a thumb, using Mark Huot’s file fieldtype

UPDATE: if script returns a blank page, check version of matrix fieldtype, this code works for matrix v1.3.5

This is for assigning the appropriate {filedir_#} to file fields that were previously ngen_file_field (within the matrix filed type) in v1, now File field_type in v2 of EE.

*This is for after you upgraded to v2, also…be sure to have a copy of your exp_weblog_fields table from v1

*Also, run this once. If you run it again, it’ll append the filedir_# again, so it might look like this: https://ellislab.com/asset/images/ent-partner-work/https://ellislab.com/asset/images/ent-partner-work/filename.jpg

Before you do anything, backup your database. You will also need your v1 table of exp_weblog_fields handle, because you’ll need to add that to your EE 2 db, once completed, you can remove that table from your database. This uses that table as reference for what directory each field/file needs appended to it.

Create a new template, enable php, copy and paste the following, then run it.

<?php

$this->EE->load->database();

$query = $this->EE->db
->select('field_name, field_id, field_type, ff_settings')
->like('ff_settings', 'ngen_file_field')
/* what is exp_weblog_fields doing here?? You need to import it into your EE2 database so you can capture the appropriate 
filedir, col_id, filename and other data that make this possible */
->get('exp_weblog_fields')
->result_array();

foreach($query as $r):

 $data = unserialize($r['ff_settings']);

 foreach($data['cols'] as $col):

  // if ngen_file_field, then look further
  if($col['type'] == 'ngen_file_field') {

  // get proper col_id to pass into UPDATE filedir_# query, below
  $exp_matrix_cols = $this->EE->db
  ->select('col_id')
  ->where('col_name', $col['name'])
  ->where('field_id', $r['field_id'])
  ->get('exp_matrix_cols');

  // select the appropriate col_id_# data, to update
  $exp_matrix_data = $this->EE->db
  ->select('row_id, col_id_'.$exp_matrix_cols->row('col_id'))
  ->where('col_id_'.$exp_matrix_cols->row('col_id').' !=', '')
  ->get('exp_matrix_data')
  ->result_array();

  // for each data within that col_id_#
  foreach($exp_matrix_data as $d):
   
   // outputting info about what fields are being updated
   print 'UPDATING
   <strong>'.$col['name'].'</strong>

   {filedir_'.$col['settings']['options'].'}

   row_id = '.$d['row_id'].'

   col_id = '.$exp_matrix_cols->row('col_id').'

   filename: '.$d['col_id_'.$exp_matrix_cols->row('col_id')].'';

   $this->EE->db
   ->query('
    UPDATE
     exp_matrix_data 
    SET
     col_id_'.$exp_matrix_cols->row('col_id').' = concat("{filedir_'.$col['settings']['options'].'}", col_id_'.$exp_matrix_cols->row('col_id').')
    WHERE 
     col_id_'.$exp_matrix_cols->row('col_id').' != ""
    AND
     row_id = '.$d['row_id'].'
   ');
  endforeach; // end $exp_matrix_data array
  }

 endforeach; // end $data['cols'] array
endforeach; // end $query array
?>

This worked for me, but I have not tested it anywhere else. If it didn’t work for you, just import your backup of the database.

This saved me from having to manually set the {filedir_#} for each file that was uploaded using ngen_file_field (within a matrix field) in version 1

When upgrading to the v2, your file fieldtypes forget what directories they are restricted to, for this custom field type file option (screenshot) http://cl.ly/Bpn3

*Backup your database first
*This is for after upgrading to v2
*This will overwrite current field_settings

Create a new template and enable php

<?php

$this->EE->load->database();

// get all the file field types that have an assigned field_list_items
$file_fields = $this->EE->db
->select('field_settings, field_id, field_list_items')
->where('field_type', 'file')
->where('field_list_items !=', '')
->get('exp_channel_fields')
->result_object();

// loop thru each field_type, set the appropriate # from field_list_items, into array
foreach($file_fields as $r):
 
 $field_settings = array(
  'field_content_type'     => 'all',
  'allowed_directories'     => $r->field_list_items,
  'field_show_smileys'     => 'n',
  'field_show_glossary'     => 'n',
  'field_show_spellcheck'    => 'n',
  'field_show_formatting_btns' => 'n',
  'field_show_file_selector'  => 'n',
  'field_show_writemode'    => 'n'
 );

 // encode and serialize array, so it can be inserted into exp_channel_fields.field_settings
 $field_settings_serialized = base64_encode(serialize($field_settings));

 // pop that serialized data into exp_channel_fields.field_settings
 $this->EE->db
 ->set('field_settings', $field_settings_serialized)
 ->where('field_id', $r->field_id)
 ->update('exp_channel_fields');

endforeach;

?>

You will now see the appropriate folder selected when you view this option wihtin the custom field settings: screenshot - http://cl.ly/Bpn3

*Also, this does not set the appropriate File Type, it will leave them all as “All”

This is like the one before, but for assigning the appropriate Allowed directory for file field types that are within a matrix field, shown here: http://cl.ly/BqJO

*Backup your database
*This is to be used after you have upgraded to EE v2
*This sets the appropriate Allowed directory, but not the File Type, that will be set to ‘All’, as seen here: http://cl.ly/BqJO

Create a new template with php enabled, copy and paste code into it, then run.

<?php

$this->EE->load->database();

// get all the matrix file field types
$file_fields = $this->EE->db
->select('col_settings, col_id, col_name')
->where('col_type', 'file')
->get('exp_matrix_cols')
->result_object();

// loop thru each col_settings
foreach($file_fields as $r):
 
 $array = unserialize(base64_decode($r->col_settings));

 // we only want ones that don't have their directory value set
 if(array_key_exists('options', $array)) {
  $col_settings = array(
   'directory'   => $array['options'],
   'content_type' => 'all'
  );

  // serialize the data, for the exp_matrix_cols.col_settings field
  $col_settings_serialized = base64_encode(serialize($col_settings));

  // pop that serialized data into exp_matrix_cols.col_settings field
  $this->EE->db
  ->set('col_settings', $col_settings_serialized)
  ->where('col_id', $r->col_id)
  ->update('exp_matrix_cols');
 }

endforeach;

?>

Hi,

I keep getting a blank page when I run the below PHP. I have the exp_weblog_fields table in v2 but ngen_file_field does not appear anywhere in the ff_settings column so I think the code gets no further. I see the field_type (ftype_id_7) which is now ‘file’ in v2 but no mention of ngen_file_field is in the exp_weblog_fields table. Am I missing something?

Thanks,
Jason

This is for assigning the appropriate {filedir_#} to file fields that were previously ngen_file_field (within the matrix filed type) in v1, now File field_type in v2 of EE.

*This is for after you upgraded to v2, also…be sure to have a copy of your exp_weblog_fields table from v1

*Also, run this once. If you run it again, it’ll append the filedir_# again, so it might look like this: https://ellislab.com/asset/images/ent-partner-work/https://ellislab.com/asset/images/ent-partner-work/filename.jpg

Before you do anything, backup your database. You will also need your v1 table of exp_weblog_fields handle, because you’ll need to add that to your EE 2 db, once completed, you can remove that table from your database. This uses that table as reference for what directory each field/file needs appended to it.

Create a new template, enable php, copy and paste the following, then run it.

<?php

$this->EE->load->database();

$query = $this->EE->db
->select('field_name, field_id, field_type, ff_settings')
->like('ff_settings', 'ngen_file_field')
/* what is exp_weblog_fields doing here?? You need to import it into your EE2 database so you can capture the appropriate 
filedir, col_id, filename and other data that make this possible */
->get('exp_weblog_fields')
->result_array();

foreach($query as $r):

 $data = unserialize($r['ff_settings']);

 foreach($data['cols'] as $col):

  // if ngen_file_field, then look further
  if($col['type'] == 'ngen_file_field') {

  // get proper col_id to pass into UPDATE filedir_# query, below
  $exp_matrix_cols = $this->EE->db
  ->select('col_id')
  ->where('col_name', $col['name'])
  ->where('field_id', $r['field_id'])
  ->get('exp_matrix_cols');

  // select the appropriate col_id_# data, to update
  $exp_matrix_data = $this->EE->db
  ->select('row_id, col_id_'.$exp_matrix_cols->row('col_id'))
  ->where('col_id_'.$exp_matrix_cols->row('col_id').' !=', '')
  ->get('exp_matrix_data')
  ->result_array();

  // for each data within that col_id_#
  foreach($exp_matrix_data as $d):
   
   // outputting info about what fields are being updated
   print 'UPDATING
   <strong>'.$col['name'].'</strong>

   {filedir_'.$col['settings']['options'].'}

   row_id = '.$d['row_id'].'

   col_id = '.$exp_matrix_cols->row('col_id').'

   filename: '.$d['col_id_'.$exp_matrix_cols->row('col_id')].'';

   $this->EE->db
   ->query('
    UPDATE
     exp_matrix_data 
    SET
     col_id_'.$exp_matrix_cols->row('col_id').' = concat("{filedir_'.$col['settings']['options'].'}", col_id_'.$exp_matrix_cols->row('col_id').')
    WHERE 
     col_id_'.$exp_matrix_cols->row('col_id').' != ""
    AND
     row_id = '.$d['row_id'].'
   ');
  endforeach; // end $exp_matrix_data array
  }

 endforeach; // end $data['cols'] array
endforeach; // end $query array
?>

This worked for me, but I have not tested it anywhere else. If it didn’t work for you, just import your backup of the database.

This saved me from having to manually set the {filedir_#} for each file that was uploaded using ngen_file_field (within a matrix field) in version 1

Can anyone provide an SQL query that would add the correct file directory to the images in my database? Currently, after using a suggestion made here - http://ellislab.com/forums/viewthread/203380/ - all of my data looks like this:
{filedir_}filename.jpg
It should look like this:
https://ellislab.com/asset/images/pronet-logo/filename.jpg

I realize that I did not add the correct number to the template originally. At this point, everything looks good except that I just need to add the number ‘4’.

I had been using Mark Huot’s file fieldtype and ended up with this problem.

Thanks,

Geoff

This code will prepend filedir’s to any regular (non-matrix) fields that used nGen File. Run this code after you upgrade. Change ftype_id_9 to ftype_id_X where X is the ID of nGen File in the exp_ff_fieldtypes table (in your EE1 site). Be sure to import a copy of exp_weblog_fields to your db after the upgrade so this will run correctly.

<?php

$this->EE->load->database();

$query = 
$this->EE->db
->select('field_id, field_name, field_label, field_list_items, ff_settings')
->where('field_type', 'ftype_id_9')
->get('exp_weblog_fields')
->result_object();

foreach($query as $r): ?>

 updating <?=$r->field_name?> - field_id_<?=$r->field_id?>

<?php
 
 $settings = unserialize($r->ff_settings);
 $dir = $settings['options'];

if($dir) {

 $this->EE->db->query('
 UPDATE 
  exp_channel_data 
 SET 
  field_id_'.$r->field_id.' = concat("{filedir_'.$dir.'}", TRIM(TRAILING "\r" FROM SUBSTRING_INDEX(field_id_'.$r->field_id.', "\n", 1)))
 WHERE 
  field_id_'.$r->field_id.' != ""
 ');

}

endforeach; ?>

Hi Brad - this routine has worked for me for all but my Matrix file fields.  All the regular file fields have worked out fine, but with the file fields in matrix, I get a syntax error when i attempt the update:

UPDATE exp_matrix_data SET col_id_1 = Concat('https://ellislab.com/asset/bug_patches/', col_id_1) WHERE col_id_1 != ''

Results in the following error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘UPDATE exp_matrix_data SET col_id_1 = Concat(‘https://ellislab.com/asset/bug_patches/’, col_id_1) WHE’ at line 1

I’ve verified in this instance that the col_id is 1 and the filedir is 8.  Can you think of a reason this wouldn’t run?