mysql - Convert JSON to Excel file with php pdo -
i have file give me json output mysql database:
<?php require 'core/init.php'; $general->logged_out_protect(); $username = htmlentities($user['username']); $user_id = htmlentities($user['id']); try { $result = $db->prepare('select id,br,sufiks,kupac,datum,rok,status racuni user_id=:user_id'); $result->bindparam(':user_id', $user_id); $result->execute(); //$res = $result->fetchall(pdo::fetch_assoc); /* extract information $result */ foreach($result $r) { $temp = array(); // following line used slice pie chart $temp['id'] = (int) $r['id']; $temp['br'] = $r['br'].$r['sufiks']; $temp['kupac'] = (string) $r['kupac']; $temp['datum'] = (string) $r['datum']; $temp['rok'] = (string)$r['rok']; $temp['status'] = (string)$r['status']; // $rs = $db->prepare('select sum(radnih_sati) track_radnici user_id=:user_id , id_radnika=:id'); // $rs->bindparam(':user_id', $user_id); // $rs->bindparam(':id', $r['id']); //$rs->bindparam(':radnici', $radnici); // $rs->execute(); // $sumrows = $rs->fetchcolumn(); // $temp['7'] = (int) $sumrows; // values of each slice $rows[] = $temp; } $table['data'] = $rows; $jsontable = json_encode($table); //echo $jsontable; } catch(pdoexception $e) { echo 'error: ' . $e->getmessage(); } echo $jsontable; ?>
and fine code give me output:
{"data":[{"id":1,"br":"1-2015","kupac":"adakolor","datum":"2015-05-19","rok":"2015-05-21","status":"placeno"},{"id":2,"br":"2-2015","kupac":"milenk","datum":"2015-05-27","rok":"2015-05-28","status":""}]}
how can convert json output excel file? best way this? or other way witout json? suggest?
this deviating request not involve json
.
if want excel, can use into outfile
, generate csv
excel can read. maybe need .xlsx file?
example
select id,br,sufiks,kupac,datum,rok,status outfile 'c:/excel.csv' character set latin1 fields terminated '\t' -- tab separated lines terminated '\r\n' racuni user_id=:user_id;
if want columns come along have add line before select this.
select 'id','br','sufiks','kupac','datum','rok','status' union select id,br,sufiks,kupac,datum,rok,status outfile 'c:/excel.csv' character set latin1 fields terminated '\t' -- tab separated lines terminated '\r\n' racuni user_id=:user_id;
i not sure work give try.
$result = $db->prepare('select ''id'',''br'',''sufiks'',''kupac'',''datum'',''rok'',''status'' union select id,br,sufiks,kupac,datum,rok,status outfile ''c:/excel.csv'' character set latin1 fields terminated ''\t'' lines terminated ''\r\n'' racuni user_id=:user_id;'); $result->bindparam(':user_id', $user_id); $result->execute();
then iteration likley not needed?
Comments
Post a Comment