php - 忽略将数据导出到 php mysql 中的 csv 文件的空列

Question

我正在编写一个脚本，该脚本从三个表(播放列表、歌曲、费率)导出数据并将其放入一个 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;

还有一件事:

After I done that, empty columns are not export, the file import successfully ?

Answer 1

好的，感谢@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";