我正试图将我的模型和两个相关模型中的数据导出到CakePHP 2.4应用程序中的CSV文件中,需要一些帮助才能以计算成本低廉的方式完成这项工作。
在我的应用程序中,每个Post hasMany Field
和Field belongsTo Fieldtype.
Fields
存储值,Fieldtype
存储值类型(IE"氧气浓度")。在每个Post
上,可以有多个具有相同Fieldtype.
的Fields
CSV生成的问题是,每个Post的字段都需要不同数量的列,所以当我执行fputcsv
时,我需要添加空单元格,以备任何Post
的字段数小于氦的最大字段数时,例如,使标题对齐。
有没有一个SQL解决方案可以让我在我的帖子中进行连接,找到并返回一个字段类型数组,其中每个字段类型需要最大数量的字段?
我的数据库结构:
Posts
id
Fields
id post_id fieldtype_id value
Fieldtypes
id name
我没能找到一种简单的方法来获取SQL中的字段计数,事实证明,在PHP中这样做并不太慢,因为我不是每次都在访问数据库。
如果有其他人必须处理这个问题,下面是我所做的:
//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 . "'n";
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("'n", "'t", "'r"), '', $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 . "'n";
}
return $output;
}