CakePHP中的多维数组=> CSV导出

问题描述：

I'm trying to export data from my model and two related models into a CSV file in my CakePHP 2.4 app, and need some help doing it in a computationally non-expensive manner.

In my app, each Post hasMany Field and each Field belongsTo Fieldtype. Fields stores values, and Fieldtype stores the value types (IE "Oxygen concentration"). On each Post, there can be multiple Fields with the same Fieldtype.

The problem with CSV generation here is that each Post's Fields require a different number of columns, so when I'm doing my fputcsv I'm going to need to add empty cells for when any Post has less than the maximum number of fields for, say, Helium so the headers line up.

Is there a SQL solution that will somehow let me do a join on my Posts find and return an array of Fieldtypes with the maximum number of Fields needed for each Fieldtype?

My DB structure:

Posts
id

Fields
id    post_id    fieldtype_id    value

Fieldtypes
id    name

我正在尝试将我的模型和两个相关模型中的数据导出到我的CakePHP 2.4应用程序中的CSV文件中，并且需要一些帮助以计算上非昂贵的方式进行。 p>

在我的应用程序中，每个发布有多个字段 code>，每个字段属于字段类型。 Fields code>存储值， Fieldtype code>存储值类型（IE“氧气浓度”）。在每个 Post code>上，可以有多个 Fields code>，具有相同的 Fieldtype。 code> p>

CSV的问题这里的生成是每个Post的字段需要不同数量的列，所以当我正在执行 fputcsv code>时，我需要在任何 Post code>时添加空单元格比起Helium的最大字段数少，所以标题排成一行。 p>

是否有一个SQL解决方案以某种方式让我在我的帖子上进行连接查找并返回一个具有每个Fieldtype所需的最大字段数的Fieldtypes数组？ p>


我的数据库结构： p> 
 
 
 帖子
id 
 
Fields  
id post_id fieldtype_id value 
 
Fieldtypes 
id name 
  code>  pre> 
  div>

答

I wasn't able to find an easy way to get the field count in SQL, and as it turns out doing it in PHP isn't too slow because it's not like I'm accessing the database each time.

If anyone else has to deal with this problem, here's what I did:

//Where $posts is my array of Posts and $fieldtypes is an id => name array of my column types
public function makeCSV($posts = array(), $fieldtypes = array()){
    $fields = array();
    $fieldlist = array();
    foreach($posts as $post){
        foreach($post['Field'] as $field){
            if($field['value'] != ''){
                $fields[] = $field['fieldtype_id'];
                $fieldlist[$field['fieldtype_id']] = null;
            }
        } //Get a list of all the fields for that post

        $postcount = array_count_values($fields);
        $fields = array();

        // get a per-post count of fields

        foreach($postcount as $id => $count){
            if ($count > $fieldlist[$id] || !isset($fieldlist[$id])){
                $fieldlist[$id] = $count;
            }
        }
    }

        $output = null;     
        $output .= "Latitude" . ",";
        $output .= "Longitude" . ",";
        $output .= "Sighted (UTC)" . ",";
        $output .= "Posted (UTC)" . ",";
        $output .= "User" . ",";
        $output .= "Location" . ",";
        $output .= "Tags" . ",";
        $output .= "Category" . ",";
        $output .= "Species status" . ",";
//      $output .= "Comments" . ",";

    foreach ($fieldlist as $fieldtype => $count){
        $total = $count;
        while($count != 0){
            $output .= $fieldtypes[$fieldtype] . " " . ($total + 1 - $count)  . ",";
            --$count;
        }
    }
        $output = $output . "
";

    foreach ($posts as $post) {
        $output = $output . addslashes($post['Post']['lat']) . "," . addslashes($post['Post']['lng']) . ",";
        $output = $output . "\"" . addslashes($post['Post']['sighted']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['Post']['created']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['User']['username']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['Post']['location']) . "\"" . ",";
        $output = $output . "\"" . addslashes($post['Post']['tags']) . "\"" . ",";
        $output = $output . addslashes($post['Post']['category']) . ",";
        if ($post['Post']['status'] == 1) {
            $output .= "Present,";
        } elseif($post['Post']['status'] == 2) {
            $output .= "Recently Appeared,";
        } elseif ($post['Post']['status'] == 3) {
            $output .= "Disappeared,";
        }
        else {
            $output .= ",";
        }
//      $output = $output . "\"" . addslashes(str_replace(array("
", "\t", ""), '', $post['Post']['body'])) . "\"" . ",";

        foreach ($fieldlist as $fieldtype => $count){
            foreach($post['Field'] as $field){
                if($field['fieldtype_id'] == $fieldtype){
                    $output .= $field['value'] . ",";
                    --$count;
                }
            }
            while ($count > 0){
                $output .= ",";
                --$count;
            }
        }
        $output = $output . "
";   
    }
    return $output; 
}

CakePHP中的多维数组=> CSV导出

相关推荐