how to convert mysql/json file to csv file with PHP

Question

I am collecting ADSB data on a Raspberry Pi via a JSON file; I am using someones PHP code that is producing a SQL data base from the JSON file.However I need a csv file to feed into an analsis program (SPSS) that I have developed. Here is the PHP code that creates the sql database

    // generate sql insert statement per aircraft in range of user set alt_geom/latitude/longitude and optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
    #var_dump($hex_code_array); var_dump($flight_code_array); // show arrays for debug
    if ($user_set_array['filter_mode_database'] && $user_set_array['filter_mode_database_limited']) {
        if (($ac_alt_geom != '' && $ac_alt_geom < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
            $sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', '$ac_dist', ";
            $sql .= "'$ac_alt_geom', '$ac_lat', '$ac_lon', '$ac_track', '$ac_gs', '$ac_baro_rate', '$ac_seen_pos', '$ac_seen', ";
            $sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_alt_baro', '$ac_mlat', '$ac_tisb', '$message_rate');";
            $sql .= PHP_EOL;

As I am going up a steep learning curve with PHP I have adapted some code that I found else where on Stack but its not producing a csv file. My objective was to take the variables that create the sql arrays and continually update the csv file .

 $data= "id,now,hex,flight,distance,altitude,lat,long,track,gs,baro-rate,seen-pos,seen,rasi,messages,category,squark,alt-baro,mlat,tisb,rec-mag-sec\n"; 
$i = 1;
#echo $data;
$data = array(
array(  '$i' ),
array(  '$ac_id' ),
    array(  '$ac_now' ),
array(  '$ac_hex' ),
array(  '$ac_flight' ),
array(  '$ac_distance' ),
array(  '$ac_alt_geom' ),
array(  '$ac_lat' ),
array(  '$ac_long' ),
array(  '$ac_track' ),
array(  '$ac_gs' ),
array(  '$ac_baro_rate' ),
array(  '$ac_seen_pos' ),
array(  '$ac_seen' ),
array(  '$ac_rssi' ),
array(  '$ac_messages' ),
array(  '$ac_category' ),
array(  '$ac_squark' ),
array(  '$ac_alt_baro' ),
array(  '$ac_mlat' ),
array(  '$ac_tisb' ),
array(  '$ac_rec_mag_sec' ),
);
}
function outputCSV($data) {
    $outputBuffer = fopen("php://output", 'w');
    foreach($data as $val) {
        fputcsv($outputBuffer, $val);
    }
    fclose($outputBuffer);
}

$filename = 'aircrafts';
header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=($filename).csv");
header("Pragama: no-cache");
hwader("Expires: 0");

outputCSV($data);

Possible duplicate of stackoverflow.com/questions/20667418/…? — Nosajimiki
– Nosajimiki, Commented Feb 15, 2019 at 14:59
Hi @Nosajimiki I am not sure you are right, sorry if I am wrong :), I originally I read the data from a json file but deleted data that was incomplete and fed the data into COLUMN VARIABLES rather than rows of data. So there are three things that I need to know. one, have I declared the array statement correctly as the variables seem to treated as text. Two, how do I loop over elements of the col arrays. Three, have I used the fputcsv command correctly. I have done extensive searching for similar examples as mine but cannot find any. Thanks for your help — Davlinds
– Davlinds, Commented Feb 17, 2019 at 10:58

Nosajimiki · Accepted Answer · 2019-02-18 17:27:04Z

First of all, it looks like you are mixing and matching 2 separate methods of working with an array. If you are working with data that starts off as comma separated string like this:

$data= "1,'2019-2-15 12:00:00','#f6F0C1',1040,345,12500,32.1465,80.1263, ...";

Then you can convert $data into an array like this:

$data = explode(",", $data);

Note that when doing this, you are not using variable names but actual raw data that you want in the csv.

However, if your data is coming from a bunch of variables, then you declare your array like this:

$data= array($i, $ac_id, $ac_now, $ac_hex, $ac_flight, $ac_distance, $ac_alt_geom, $ac_lat ...);

Of note, since each element of your $data array is only one value, you don't need to make arrays out of them. Also, because you want to call these values as variables, you don't want to put them in quotes. If you put them in quotes, they will be evaluated as strings instead of variables.

Once you have used the appropriate method above to make an array called $data, you need to push it to your CSV. Another thing to note here is that you are only trying to put one line into the CSV at a time; so, you do not need to loop it at all. Looping is used for when you have an array of arrays where each subarray represents a single line in the CSV file. Since you are just doing a single line at a time, you can simplify your code as shown below.

function outputCSV($data) {
    $outputBuffer = fopen("php://output", 'w');  
    fputcsv($outputBuffer,$data); 
    fclose($outputBuffer); 
}

Collectives™ on Stack Overflow

how to convert mysql/json file to csv file with PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related