ExpressionEngine PHP Export into JSON

export.php

<?php
header('Content-type: application/json; charset=utf-8'); //Setting the page Content-type

$content = array();
$channel = $_GET['id'];
$fields_query_string = "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_string = "SELECT * FROM exp_channel_data cd INNER JOIN exp_channel_titles ct ON cd.entry_id = ct.entry_id WHERE cd.channel_id = '$channel'";

// query single field
if(isset($_GET['field'])) {
 $_field = $_GET['field'];
 $fields_query_string = $fields_query_string . " AND field_name = '$_field'";
}

// filter by status
if(isset($_GET['status'])) {
 $_status = $_GET['status'];
 $entries_query_string = $entries_query_string . " AND status = '$_status'";
}

// limit query
if(isset($_GET['limit'])) {
 $_limit = $_GET['limit'];
 $entries_query_string = $entries_query_string . " LIMIT $_limit";
}

// offsett query
if(isset($_GET['offset'])) {
    $_offset = $_GET['offset'];
    $entries_query_string = $entries_query_string . " OFFSET $_offset";
}


$fields_query = $this->EE->db->query($fields_query_string);
$entries_query = $this->EE->db->query($entries_query_string);
$fields = $fields_query->result_array();

// custom merge field
//$image_credit = '';

//print_r($fields);
//print_r($_field);
//print_r($fields_query_string);
//print_r($entries_query);
//die();

// export books only!
if($channel == 9 && isset($_GET['books'])) {
    foreach ($entries_query->result_array() as $id => $row) {
        foreach ($fields as $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) {
                        $value = $matrix_row['col_id_'.$matrix_col['col_id']];
                        $value = str_replace("{filedir_1}", "http://www.arrvev.no/files/images/", $value);
                        $content[$id][$matrix_col['col_name']] = trim($value);
                    }
                }
            }
        }
    }
} else {
    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 P&T Playa field inside Matrix field
                        if ($matrix_col['col_type'] == 'playa') {
                            $value = preg_replace("/\[[^]]+\]/", '', $matrix_row['col_id_'.$matrix_col['col_id']]);
                            // get only the slug/url_title from Playa-field
                            $pieces = explode(' ', $value);
                            // print_r($pieces);
                            $value = array_pop($pieces);

                        } else {
                            $value = $matrix_row['col_id_'.$matrix_col['col_id']];
                        }

                        // TODO: Replce this with logic from "if ($field['field_type'] == 'file')" ??!?
                        $value = str_replace("{filedir_1}", "http://www.arrvev.no/files/images/", $value);

                        $content[$id][$field['field_name']][$key][$matrix_col['col_name']] = trim($value);
                    }
                }
            // 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']]);            

                // get only the slug/url_title from Playa-field
                $pieces = explode(' ', $value);
                $value = array_pop($pieces);
                $content[$id][$field['field_name']] = trim($value);

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

            // P&T assets
            } elseif ($field['field_type'] == 'assets') {
                $value = $row['field_id_' . $field['field_id']];
                $value = str_replace("{filedir_1}", "http://www.arrvev.no/files/images/", $value);
                $content[$id][$field['field_name']] = $value;
                
            // Introvert
            } elseif ($field['field_type'] == 'introvert') {

    //            var_dump($field); 
                // die();
                $entry_id       = $row['entry_id'];
                $rel_table 		= 'exp_playa_relationships';
                $rel_parent_col = 'parent_entry_id';
                $rel_child_col 	= 'child_entry_id';

                $sql = "SELECT DISTINCT
                    $rel_table.$rel_parent_col as parent_id,
                    $rel_table.$rel_child_col as child_id,
                    exp_channel_titles.channel_id,
                    exp_channel_titles.entry_id,
                    exp_channel_titles.status,
                    exp_channel_titles.entry_date,
                    exp_channels.channel_title,
                    exp_statuses.status,
                    exp_statuses.highlight,
                    exp_channel_titles.title,
                    exp_channel_titles.url_title

                FROM $rel_table

                    LEFT JOIN exp_channel_titles
                    ON ($rel_table.$rel_parent_col=exp_channel_titles.entry_id)

                    LEFT JOIN exp_channel_data
                    ON ($rel_table.$rel_parent_col=exp_channel_data.entry_id)

                    LEFT JOIN exp_channels
                    ON (exp_channels.channel_id=exp_channel_titles.channel_id)

                    LEFT JOIN exp_statuses
                    ON (exp_channel_titles.status=exp_statuses.status)

                WHERE $rel_table.$rel_child_col = $entry_id
                ORDER BY exp_channels.channel_title, exp_channel_titles.title";

            $query = $this->EE->db->query($sql);
            $result =  ($query->num_rows() > 0) ? $query->result_array() : array();
            foreach ($query->result_array() as $key => $relationships) {
                $value = $relationships['url_title'];
            }
            $content[$id][$field['field_name']] = $value;

            // var_dump($result);
    //        die();


            // custom merge
            } elseif ($field['field_name'] == 'image_credit') {
                $image_credit = $row['field_id_' . $field['field_id']];
            } elseif ($field['field_name'] == 'image_text') {
                $content[$id][$field['field_name']] = trim($row['field_id_' . $field['field_id']] . ' ' . $image_credit);

            // Any other field
            } else {
                
                $value = $row['field_id_' . $field['field_id']];
                    
                // decode entities
                $value = html_entity_decode($value);

                // add slashes to escape 
                //$value = addslashes($value);

                // replace filedir-references
                $value = str_replace("{filedir_1}", "/files/images/", $value);
                
                $content[$id][$field['field_name']] = $value;
            }
        }
    }
}
echo json_encode($content);

?>