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
to
{filedir_3}filename.jpg
UPDATE exp_channel_data SET field_id_126 = Concat('{filedir_3}', 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('{filedir_3}',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
{filedir_3}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('{filedir_5}', 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.
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
$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: http://ellislab.com/asset/images/ent-partner-work/http://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 '<p>UPDATING</p> <p><strong>'.$col['name'].'</strong><br /> {filedir_'.$col['settings']['options'].'}<br /> row_id = '.$d['row_id'].'<br /> col_id = '.$exp_matrix_cols->row('col_id').'<br /> filename: '.$d['col_id_'.$exp_matrix_cols->row('col_id')].'</p>';
$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):
// 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):
// 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'); }
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
bkmorse - 15 November 2011 02:32 PM
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: http://ellislab.com/asset/images/ent-partner-work/http://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 '<p>UPDATING</p> <p><strong>'.$col['name'].'</strong><br /> {filedir_'.$col['settings']['options'].'}<br /> row_id = '.$d['row_id'].'<br /> col_id = '.$exp_matrix_cols->row('col_id').'<br /> filename: '.$d['col_id_'.$exp_matrix_cols->row('col_id')].'</p>';
$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:
http://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.
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.
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('{filedir_8}', 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(‘http://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?
