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
$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?
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>';
}
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments