2

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.

2

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'])
        );
    }
}

?>
1
  • Worked a champ - thanks so much Derek! Hope others may find this little chunk of code useful as well. Aug 16 '13 at 19:42
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%'
2
  • Hey Jay - Derek's code was plug and play but I really appreciate your time answering as well. Cheers! Aug 16 '13 at 19:43
  • You're welcome, Michael. I'm glad that Derek posted that code. I didn't have any PHP like this handy, but I also tend to prefer doing this kind of thing in PHP, too. It's obviously awkward doing this kind of transform purely in SQL.
    – Jay F
    Aug 16 '13 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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