Ignore empty columns for export data to csv file in php mysql

Question

I am working on a script that export the data from three tables (playlist, songs, rate) and put it into one csv file its working perfectly.

If the one or more columns are empty or have null value it also export in csv file and show empty column like this:

And this

And this

So I want that if the columns are empty, then these columns are not export in csv file. I have no idea how can I do this.

Note: I just want columns empty are not export, not rows

Here is my code of export three tables in one csv file.

$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;

One more thing:

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

Answer 1

Ok I figured it out thanks to @vel.

I just change my query and it works perfectly and it also import successfully.

here is my query:

$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";

Answer 2

I think array_filter might be of use here.

$row = array_filter( mysqli_fetch_assoc( $result ) );

and in the loop

while( $row = array_filter( mysqli_fetch_assoc( $result ) ) ){
    /* do stuff */
}