ExpressionEngine PHP Export into JSON

export.php

<?php

$channel = $_GET['id'];
$content = array();

$fields_query = $this->EE->db->query("SELECT * FROM exp_channel_fields LEFT JOIN exp_channels ON exp_channel_fields.group_id = exp_channels.field_group WHERE exp_channels.channel_id = '$channel'");

$entries_query = $this->EE->db->query("SELECT * FROM exp_channel_data cd INNER JOIN exp_channel_titles ct ON cd.entry_id = ct.entry_id WHERE cd.channel_id = '$channel'");

$fields = $fields_query->result_array();

foreach ($entries_query->result_array() as $id => $row) {
    $content[$id] = array();
    $content[$id]['title'] = $row['title'];
    $content[$id]['url_title'] = $row['url_title'];
    $content[$id]['status'] = $row['status'];
    $content[$id]['enabled'] = ($row['status'] == 'closed') ? 0 : 1;
    $content[$id]['entry_date'] = ($row['entry_date']) ? date('Y-m-d H:i:s', $row['entry_date']) : '';
    $content[$id]['expiration_date'] = ($row['expiration_date']) ? date('Y-m-d H:i:s', $row['expiration_date']) : '';
    $content[$id]['author_id'] = $row['author_id'];
    
    // Handle category
    $categories = $this->EE->db->query("SELECT * FROM exp_category_posts c JOIN exp_categories cp ON c.cat_id = cp.cat_id WHERE entry_id = ".$row['entry_id'])->result_array();

    if ($categories) {
        foreach ($categories as $category) {
            $content[$id]['category_' . $category['group_id']][] = $category['cat_name'];
        }
    }
    
    foreach ($fields as $field) {
        // Support for P&T Matrix field
        if ($field['field_type'] == 'matrix') {
            $query = $this->EE->db->query("SELECT * FROM exp_matrix_cols WHERE field_id=".$field['field_id']);

            foreach ($query->result_array() as $matrix_col) {
                $query = $this->EE->db->query("SELECT * FROM exp_matrix_data WHERE field_id=".$field['field_id']." AND entry_id=".$row['entry_id']." ORDER BY row_order");

                foreach ($query->result_array() as $key => $matrix_row) {
                    // Support for File field
                    if ($matrix_col['col_type'] == 'file') {
                        $this->EE->load->library('file_field');
                        $fileInfo = $this->EE->file_field->parse_field($matrix_row['col_id_'.$matrix_col['col_id']]);
                        
                        $content[$id][$field['field_name']][$key][$matrix_col['col_name']] = $fileInfo['url'];
                    } else {
                        // Any other field
                    	$content[$id][$field['field_name']][$key][$matrix_col['col_name']] = $matrix_row['col_id_'.$matrix_col['col_id']];
                    }
                }
            }
        // Support for P&T Playa field
        } elseif ($field['field_type'] == 'playa') {
            // Change from "[93] [my-category] My Category" = "My Category"
            $value = preg_replace("/\[[^]]+\]/", '', $row['field_id_' . $field['field_id']]);
            
            $content[$id][$field['field_name']] = trim($value);
            
        // Support for File field
        } elseif ($field['field_type'] == 'file') {
            $this->EE->load->library('file_field');
            $fileInfo = $this->EE->file_field->parse_field($row['field_id_' . $field['field_id']]);
            
            $content[$id][$field['field_name']] = $fileInfo['url'];
            
        // Support for P&T Assets field
        } elseif ($field['field_type'] == 'assets') {
            $value = $row['field_id_' . $field['field_id']];
            
            $content[$id][$field['field_name']] = $value;
            
        } else {
            // Any other field
            $content[$id][$field['field_name']] = $row['field_id_' . $field['field_id']];
        }
    }
}
    
echo json_encode($content);

?>

I’m sorry, it’s been a really long time since I’ve worked with this and at this point I can’t help. Good luck though.

On Thu, 29 Oct 2020 at 20:13, doctorgloom ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ I know this is a longshot, but I'm helping with a site that is running EE 1.7.3 (ancient!) and to make matters worse used two old extensions to build out their catalog (both by Mark Huot, File Type and Multirelationship). This export solution won't work because it is for EE2, and I tried updating to EE2 and running it but it won't work because of those old Extensions (which break things in EE2). The goal is to move to Craft. So, I was wondering if anyone had any suggestions? And like I said, I know it's a longshot. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <https://gist.github.com/5fbe54ef0abb15e3ff6f667291098464#gistcomment-3509379>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAJE62PWW2P26VVTRM2S25TSNHEIFANCNFSM4HJQ6FPQ> .

-- Kind Regards, *Paul Whitehead* *How to find me: * Email: paul@prwhitehead.co.uk Portfolio: prwhitehead.co.uk Email: prwhitehead@gmail.com Twitter: twitter.com/pau1rw <http://+twitter.com/pau1rw>

Hey everyone, not sure how many people are still doing this but I thought I might add my edits for whoever it might help. I found that files/and assets, particularly one's using aws' S3 buckets, didn't work well. I also had issues with categories not working. There is also a seperate template for Members/Users which is the second snippet

<?php

$channel = $_GET['id'];
$content = array();

$fields_query = $this->EE->db->query("SELECT * FROM exp_channel_fields LEFT JOIN exp_channels ON exp_channel_fields.group_id = exp_channels.field_group WHERE exp_channels.channel_id = '$channel'");

$entries_query = $this->EE->db->query("SELECT * FROM exp_channel_data cd INNER JOIN exp_channel_titles ct ON cd.entry_id = ct.entry_id WHERE cd.channel_id = '$channel'");

$fields = $fields_query->result_array();

foreach ($entries_query->result_array() as $id => $row) {
    $content[$id] = array();
    $content[$id]['title'] = $row['title'];
    $content[$id]['url_title'] = $row['url_title'];
    $content[$id]['status'] = $row['status'];
    $content[$id]['enabled'] = ($row['status'] == 'closed') ? 0 : 1;
    $content[$id]['entry_date'] = ($row['entry_date']) ? date('Y-m-d H:i:s', $row['entry_date']) : '';
    $content[$id]['expiration_date'] = ($row['expiration_date']) ? date('Y-m-d H:i:s', $row['expiration_date']) : '';
    $content[$id]['author_id'] = $row['author_id'];
    
    // Handle category
    $categories = $this->EE->db->query("SELECT * FROM exp_category_posts c JOIN exp_categories cp ON c.cat_id = cp.cat_id WHERE entry_id = ".$row['entry_id'])->result_array();

    if ($categories) {
        foreach ($categories as $category) {
            $content[$id]['category_' . $category['group_id']][] = $category['cat_name'];
        }
    }
    
    foreach ($fields as $field) {
        // Support for P&T Matrix field
        if ($field['field_type'] == 'matrix') {
            $query = $this->EE->db->query("SELECT * FROM exp_matrix_cols WHERE field_id=".$field['field_id']);

            foreach ($query->result_array() as $matrix_col) {
                $query = $this->EE->db->query("SELECT * FROM exp_matrix_data WHERE field_id=".$field['field_id']." AND entry_id=".$row['entry_id']." ORDER BY row_order");

                foreach ($query->result_array() as $key => $matrix_row) {
                    // Support for File field
                    if ($matrix_col['col_type'] == 'file') {
                        $this->EE->load->library('file_field');
                        $fileInfo = $this->EE->file_field->parse_field($matrix_row['col_id_'.$matrix_col['col_id']]);
                        
                        $content[$id][$field['field_name']][$key][$matrix_col['col_name']] = $fileInfo['url'];
                    } else {
                        // Any other field
                    	$content[$id][$field['field_name']][$key][$matrix_col['col_name']] = $matrix_row['col_id_'.$matrix_col['col_id']];
                    }
                }
            }
        // Support for P&T Playa field
        } elseif ($field['field_type'] == 'playa') {
            $productList = array();
            $lines = explode("\n", $row['field_id_' . $field['field_id']]);
            if ($field['field_label'] == 'Products'){            
                foreach($lines as $line){
                    $matches = array();
                    preg_match_all("/(?<=\[)([^\[\]]*)(?=\])/", $line, $matches);
                    $product = $matches[0]; //= ~["29","10-resources-to-teach-stem-in-afterschool"]
                    if(count($product) == 0){ break; }
                    array_push($productList, $product[1]);
                }

                // var_dump($matches);
                $content[$id][$field['field_name']] = $productList;
            }
            else {
                // Change from "[93] [my-category] My Category" = "My Category"   
                foreach($lines as $line){    
                    array_push($productList, preg_replace("/\[[^]]+\]\s*/", '', $line));
                }
                // $content[$id][$field['field_name']] = trim($value);
                $content[$id][$field['field_name']] = $productList;
            }
            
        // Support for File field   
        } elseif ($field['field_type'] == 'file') {
            $url;
            if($field['field_name'] == 'product_thumbnail' || $field['field_name'] == 'product_detail_image') {
                $url = preg_replace('/{.*}/', '', $row['field_id_' . $field['field_id']]);
            }
            else {
                $this->EE->load->library('file_field');
                $fileInfo = $this->EE->file_field->parse_field($row['field_id_' . $field['field_id']]);
                $url = $fileInfo['url'];
            }
            $content[$id][$field['field_name']] = $url;
        //Channel Images
        } elseif ($field['field_type'] == 'channel_images') {
            $imageQuery = $this->EE->db->query("SELECT * FROM exp_channel_images WHERE field_id=".$field['field_id']." and entry_id=".$row['entry_id']);
            $images = array();

            foreach ($imageQuery->result_array() as $iid => $image) {
                $images[$iid] = array();

                $images[$iid]['Title'] = $image['title'];
                $images[$iid]['URL'] = '/' . $image['filename'];
                $images[$iid]['Caption'] = $image['description'];
            }
            $content[$id][$field['field_name']] = $images;

        // Support for P&T Assets field
        } elseif ($field['field_type'] == 'assets') {
            $value = $row['field_id_' . $field['field_id']];
            $content[$id][$field['field_name']] =$value;
            
        } else {
            // Any other field
            $content[$id][$field['field_name']] = $row['field_id_' . $field['field_id']];
        }
    }
}
    
echo json_encode($content);

?>

User Export - id is the group id

<?php

$group = $_GET['id'];
$content = array();

$fields_query = $this->EE->db->query("SELECT * FROM exp_member_fields;");

$entries_query = $this->EE->db->query("
    SELECT m.group_id, m.username, m.screen_name, m.email, md.* FROM exp_member_data md
    INNER JOIN exp_members m ON md.member_id = m.member_id 
    WHERE m.group_id = '$group';");

$fields = $fields_query->result_array();

foreach ($entries_query->result_array() as $id => $row) {
    $content[$id] = array();
    $content[$id]['username'] = $row['username'];
    $content[$id]['screen_name'] = $row['screen_name'];
    $content[$id]['email'] = $row['email'];
    
    foreach ($fields as $field) {
        $content[$id][$field['m_field_name']] = $row['m_field_id_' . $field['m_field_id']];
    }
}
    
echo json_encode($content);

?>