我正在处理一个脚本,该脚本可从三个表(playlist, songs, rate
)导出数据,并将其放入一个csv文件中,它可以正常工作。
如果一个或多个列为空或具有空值,则它还会在csv文件中导出并显示为空列,如下所示:
因此,我希望如果列为空,则这些列不会在csv文件中导出。我不知道该怎么办。
注意:我只希望空列不导出,不行
这是我在一个csv文件中导出三个表的代码。
$pre = $wpdb->prefix;
$link = mysqli_connect($mysql_host,$mysql_user,$mysql_pass,$mysql_db) or die('Could not connect: '.mysqli_error());
mysqli_select_db($link,$mysql_db) or die('Could not select database: '.$mysql_db);
$query = "SELECT plist.*, psong.*, prate.*
FROM " . $pre . "hmp_songs As psong
LEFT JOIN " . $pre . "hmp_playlists As plist
On plist.playlist_name = psong.song_playlist
LEFT JOIN " . $pre . "hmp_rating As prate
On psong.song_id = prate.rsong_id";
$result = mysqli_query($link,$query) or die("Error executing query: ".mysqli_error());
$row = mysqli_fetch_assoc($result);
$line = "";
$comma = "";
foreach($row as $name => $value){
$line .= $comma . '"' . str_replace('"', '""', $name) . '"';
$comma = ",";
}
$line .= "\n";
$out = $line;
mysqli_data_seek($result, 0);
while($row = mysqli_fetch_assoc($result)){
$line = "";
$comma = "";
foreach($row as $value)
{
$line .= $comma . '"' . str_replace('"', '""', $value) . '"';
$comma = ",";
}
$line .= "\n";
$out .= $line;
}
$csv_file_name = 'HMP_'.date('Ymd_His').'.csv'; # CSV FILE NAME WILL BE table_name_yyyymmdd_hhmmss.csv
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=".$csv_file_name);
header("Content-Description:File Transfer");
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Type: application/octet-stream');
echo __($out,"hmp");
exit;
还有一件事:
完成之后,不导出空列,文件导入成功了吗?
好的,我感谢@vel弄清楚了。
我只是更改了查询,它运行完美,并且也成功导入了。
这是我的查询:
$query = "SELECT plist.playlist_id, plist.playlist_name, plist.playlist_shortcode, psong.song_id, psong.list_order,
psong.song_playlist, psong.mp3, psong.ogg, psong.title, psong.buy, psong.buyy, psong.buyyy, psong.price, psong.cover,
psong.artist
FROM " . $pre . "hmp_songs As psong
LEFT JOIN " . $pre . "hmp_playlists As plist
On plist.playlist_name = psong.song_playlist
Where plist.playlist_id IS NOT NULL
And plist.playlist_name IS NOT NULL
And plist.playlist_shortcode IS NOT NULL
And psong.song_id IS NOT NULL
And psong.list_order IS NOT NULL
And psong.song_playlist IS NOT NULL
And psong.mp3 IS NOT NULL
And psong.ogg IS NOT NULL
And psong.title IS NOT NULL
And psong.buy IS NOT NULL
And psong.buyy IS NOT NULL
And psong.buyyy IS NOT NULL
And psong.price IS NOT NULL
And psong.cover IS NOT NULL
And psong.artist IS NOT NULL";
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句