Converting EE1 Text Field with Image tag to EE2 File Field

Question

I've a EE1 - EE2 project where I have a native text field storing a full image tag like:

<img src="{filedir_2}sh-275.jpg" style="border: 0;" alt="SH-641 humidity chamber" />

This being the result of the EE1 upload process. I'd like to get this turned into an EE2 file field so need the field contents to just be:

{filedir_2}sh-275.jpg

The main goal is so that I can use the EE image resizing.

Has anyone come up with some clever SQL to do that in an automated fashion? I'd rather not have to touch each entry.

I know I have the option to use a template-level image resizer yet but would rather see about getting the native tool working first.

Answer 1

Straight SQL is much more difficult than a little bit of PHP in a one-off PHP-enabled template.

JayF is right that a new File field to hold your extracted data is a good idea - you can delete the old field after you see the migration has worked as expected.

This code assumes you've upgraded to EE 2.6+. Replace the top two field names, throw it in a PHP-enabled template, and load it up.

<?php

$old_field = 'field_id_XX';
$new_field = 'field_id_XX';

$images = ee()->db->query("
    SELECT entry_id, $old_field FROM exp_channel_data WHERE $old_field != ''
");
foreach($images->result_array() as $image)
{
    preg_match('/src="([^"]*)"/i', $image[$old_field], $match);
    if(!empty($match))
    {
        $data = array(
            $new_field => $match[1]
        );
        ee()->db->query(
            ee()->db->update_string('exp_channel_data', $data, 'entry_id = '.$image['entry_id'])
        );
    }
}

?>

Answer 2

I've done this kind of thing in SQL. The trick is matching what you need using just SQL, since its string matching is somewhat verbose. I've also done this using SQL in PHP--just looping through a query result set, making the changes using PHP's RegEx or basic string substitution, and then updating the entries.

To get at the field data in EE1, you'll want to do a query like:

select entry_id, field_id_10 from exp_weblog_data 
where channel_id = 1 and field_id_10 like '%src="{filedir_2}%.jpg%'

You can find the field_ids for the fields you need, via this:

select * from exp_weblog_fields

Or, go to edit a field in the EE control panel, and grab the field ID from the URL.

---

updated: here's an example query for parsing out the file info. Note that there's a +4 in the equation because we look-up the position of .jpg (which is 4 characters). If you end-up doing this totally in SQL, then I'd run separate queries for .jpg, .gif, .png as needed.

select entry_id, field_id_10, 
substr(field_id_10, 
locate('{filedir_2}', field_id_10),
locate('.jpg', field_id_10, locate('{filedir_2}', field_id_10))
- locate('{filedir_2}', field_id_10) + 4
)
from exp_weblog_data  
where channel_id = 1 and field_id_10 like '%src="{filedir_2}%.jpg%'

So, ultimately, you'd want to create a new field for the new image value (say it's field_id_11), and do something like this:

update exp_weblog_data set field_id_11 = 
substr(field_id_10, 
locate('{filedir_2}', field_id_10),
locate('.jpg', field_id_10, locate('{filedir_2}', field_id_10))
- locate('{filedir_2}', field_id_10) + 4
)
where channel_id = 1 and field_id_10 like '%src="{filedir_2}%.jpg%'