JSON Entry Export for ExpressionEngine with offset and limit

export-entries.html

<?php

// Pass id, limit and offset in as a query string
// Allow PHP and parse on Output

$channel = $_GET['id'];
$limit = $_GET['limit'];
$offset = $_GET['offset'];
$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' LIMIT $limit OFFSET $offset");

$fields = $fields_query->result_array();

foreach ($entries_query->result_array() as $id => $row) {
    $content[$id] = array();

    $content[$id]['ee_id'] = $row['entry_id'];

    $content[$id]['title'] = $row['title'];

    $content[$id]['url_title'] = $row['url_title'];

    $content[$id]['status'] = $row['status'];

    $content[$id]['enabled'] = ($row['status'] == 'closed' || $row['status'] == 'draft') ? 0 : 1;

    $content[$id]['entry_date'] = ($row['entry_date']) ? date('Y-m-d H:i:s', $row['entry_date']) : '';
    $editDate = new DateTime($row['edit_date']);
    $editDate->setTimeZone(new DateTimeZone("Europe/Dublin"));
    $content[$id]['edit_date'] = date_format($editDate, 'Y-m-d H:i:s');
    
    $content[$id]['expiration_date'] = ($row['expiration_date']) ? date('Y-m-d H:i:s', $row['expiration_date']) : '';

    $content[$id]['author_id'] = $row['author_id'];

    // Author Email
    
    $authorEmail = $this->EE->db->query("SELECT * FROM exp_members WHERE member_id =  " . $row['author_id'])->result_array();
    if ($authorEmail) {
        foreach ($authorEmail as $author) {
            $content[$id]['author_email'] = $author['email'];
        }
    }

    // Seo Lite
    $seoLite = $this->EE->db->query("SELECT * FROM `exp_seolite_content` WHERE entry_id =  " . $row['entry_id'])->result_array();
    if ($seoLite) {
        foreach ($seoLite as $seo) {
            $content[$id]['seo_title'] = $seo['title'];
            $content[$id]['seo_keywords'] = $seo['keywords'];
            $content[$id]['seo_description'] = $seo['description'];
        }
    }

    // Categories
    $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') {
            $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 Tagger field
        } elseif ($field['field_type'] == 'tagger') {
            $tags = preg_replace("/[,]+/", '|', $row['field_id_' . $field['field_id']]);
            $content[$id][$field['field_name']] = trim($tags);
            
            // Support for Relationship field
        } elseif ($field['field_type'] == 'relationship') {
            $array = array('field_id' => $field['field_id'], 'parent_id' => $row['entry_id']);
            $rel = $this->EE->db->select('child_id')->where($array)->get('relationships');

            global $rel1;
            global $rel2;
            global $rel3;

            // Manually add relationship field names
            foreach ($rel->result_array() as $r) {

                $content[$id][$field['field_name']][] = $r['child_id'];

                if ($field['field_name'] == 'related_article_1') {

                    $rel1 = $content[$id]['related_article_1'];
                    $content[$id]['rel_combined'] = array_merge((array) $rel1);
                }
                if ($field['field_name'] == 'related_article_2') {

                    $rel2 = $content[$id]['related_article_2'];
                    $content[$id]['rel_combined'] = array_merge((array) $rel1, (array) $rel2);

                }
                if ($field['field_name'] == 'related_article_3') {

                    $rel3 = $content[$id]['related_article_3'];
                    $content[$id]['rel_combined'] = array_merge((array) $rel1, (array) $rel2, (array) $rel3);

                }


            }

        // Simple text field for a URL but how to deal with missing http://
        } elseif ($field['field_id'] == '30') {

            $url = $row['field_id_30'];

            if (!preg_match("/^(http|ftp):/", $url)) {
                $url = 'http://' . $url;
            }
            $content[$id][$field['field_name']] = $url;
            // Support for expresso field
        } elseif ($field['field_type'] == 'expresso') {
            
            $output = preg_replace("/\|+/", '&#124;', $row['field_id_' . $field['field_id']]);
            $content[$id][$field['field_name']] = $output;

        } else {
            // Any other field
            $content[$id][$field['field_name']] = $row['field_id_' . $field['field_id']];
        }

    }
}

echo json_encode($content);
?>