How to Export data from 3 tables in one .csv file php

Question

I want to export the data from the 3 tables without joins in one .csv file. I am trying with joins but i am not getting the result Which i want.

Below are my table structure

Playlist Songs Rating

CODE

$mysql_host = DB_HOST;
$mysql_user = DB_USER;
$mysql_pass = DB_PASSWORD;
$mysql_db = DB_NAME;

$pre = $wpdb->prefix;

$link = mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die('Could not connect: ' . mysql_error());

mysql_select_db($mysql_db, $link) or die('Could not select database: ' . $mysql_db);

$query = "SELECT plist.*, psong.*, prate.* 
          FROM " . $pre . "foo_playlists As plist 
          LEFT JOIN " . $pre . "foo_songs As psong
          On plist.playlist_name = psong.splaylist_name 
          LEFT JOIN " . $pre . "foo_rating As prate
          On psong.song_id = prate.rsong_id";

$result = mysql_query($query);
$row = mysql_fetch_assoc($result);

$line = "";
$comma = "";

foreach ($row as $name => $value) {
   $line .= $comma . '"' . str_replace('"', '""', $name) . '"';
   $comma = ",";
}
    $line .= "\n";
    $out = $line;
    mysql_data_seek($result, 0);
    while ($row = mysql_fetch_assoc($result)) {
        $line = "";
        $comma = "";
        foreach ($row as $value) {
            $line .= $comma . '"' . str_replace('"', '""', $value) . '"';
            $comma = ",";
        }
        $line .= "\n";
        $out.=$line;
    }
    $csv_file_name = 'songs_' . 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, "foo");

    exit;

I got this result with I want this desired result

How can I do this?

Answer 1

Well, your problem is that you can't retrieve all the data at the same time in an only MySQL query, as they are not related data. Your problem is just the output, so, you only will have to relate the 3 set of results in an only array. To do that:

• Execute the three querys, and save them in three unrelated arrays.

• Relate them with a key you'll share with all of them.

• Loop over all the arrays assigning values to a main "output" one.

With that, you'll have the array which you can output to get the CSV you want. For the sake of the example, and due I can't write a valid code with your vars and queries, I wrote the following example. It has the 3 different arrays you'll have to get from your database with mock data, but you can grab the idea. Just copy and paste and you'll have the live example:

<?php

$playlists  = array(
    array(
        'id'    => 1
    ,   'data'  => 'playlist1'
    )
,   array(
        'id'    => 2
    ,   'data'  => 'playlist2'
    )
,   array(
        'id'    => 3
    ,   'data'  => 'playlist3'
    )
);

$songs  = array(
    array(
        'id'    => 1
    ,   'data'  => 'song1'
    )
,   array(
        'id'    => 2
    ,   'data'  => 'song2'
    )
,   array(
        'id'    => 3
    ,   'data'  => 'song3'
    )
,   array(
        'id'    => 4
    ,   'data'  => 'song4'
    )
,   array(
        'id'    => 5
    ,   'data'  => 'song5'
    )
);
$rates  = array(
    array(
        'id'    => 1
    ,   'data'  => 'rating1'
    )
,   array(
        'id'    => 2
    ,   'data'  => 'rating2'
    )
,   array(
        'id'    => 3
    ,   'data'  => 'rating3'
    )
,   array(
        'id'    => 4
    ,   'data'  => 'rating4'
    )
,   array(
        'id'    => 5
    ,   'data'  => 'rating5'
    )
,   array(
        'id'    => 6
    ,   'data'  => 'rating6'
    )
);

// Count all the arrays and get the bigger:
$num        = 0;
$play_num   = count( $playlists );
$num        = ($play_num > $num) ? $play_num : $num;

$song_num   = count( $songs );
$num        = ($song_num > $num) ? $song_num : $num;

$rate_num   = count( $rates );
$num        = ($rate_num > $num) ? $rate_num : $num;


$output = array();
for ( $i = 0; $i<=$num; $i++ ) {
    $output[]   = array(
        'id_playlist'   => !empty( $playlists[$i]['id'] )   ? $playlists[$i]['id']   : ''
    ,   'data_playlist' => !empty( $playlists[$i]['data'] ) ? $playlists[$i]['data'] : ''
    ,   'id_song'       => !empty( $songs[$i]['id'] )       ? $songs[$i]['id']       : ''
    ,   'data_song'     => !empty( $songs[$i]['data'] )     ? $songs[$i]['data']     : ''
    ,   'id_rate'       => !empty( $rates[$i]['id'] )       ? $rates[$i]['id']       : ''
    ,   'data_rate'     => !empty( $rates[$i]['data'] )     ? $rates[$i]['data']     : ''
    );
}

foreach ( $output as $out ) {
    echo implode( ' - ', $out);
    echo '<br>';
}

Output:

1 - playlist1 - 1 - song1 - 1 - rating1
2 - playlist2 - 2 - song2 - 2 - rating2
3 - playlist3 - 3 - song3 - 3 - rating3
  -           - 4 - song4 - 4 - rating4
  -           - 5 - song5 - 5 - rating5
  -           -   -       - 6 - rating6