PHP - fputcsv: put JSON values retrieved from MySQL field in separate columns

Question

I have a MySQL column that holds a JSON string of question numbers (0-165) as a key and question answers (score of 1-5) as a value. I need to retrieve all of the MySQL columns, put them into a .csv file with the JSON values in separate columns.

So far, I have a working PHP function that outputs the entirety of my MySQL table to an CSV file using fputcsv(). Here is the code:

        function buildcsv(){
            $start = $_POST['start'];
            $end = $_POST['end'];
            $result = mysql_query('SELECT fullname, institution, email, timestamp, responsesJSON
                FROM `members` WHERE timestamp BETWEEN "'.$start.'" AND "'.$end.'"');
            if (!$result) die('Couldn\'t fetch records');
            ob_start();
            $fp = fopen('php://output', 'w');
            if ($fp && $result) {
                while ($row = mysql_fetch_row($result)) {
                    fputcsv($fp, array_values($row));
                }
                fclose($fp);
                return ob_get_clean();
            }else{
                $error = "Error. Either there is no data or invalid date.";
                return $error;
            }

        }

And here is the output from the code:

Current CSV output

However, I need my CSV output to resemble the following mockup:

Desired CSV output

Is it possible to do this without changing my database structure? I have seen code on stackoverflow that puts JSON values into separate columns, but how would I integrate it into my existing code which retrieves all other MySQL columns?

Thank you all in advance.

Your code is vulnerable to SQL injections; you should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, Jan 06 '15 at 20:55
@RocketHazmat: The first screenshot is what my code above outputs, and the second screenshot is my desired output. — chakeda, Jan 06 '15 at 21:01
Instead of passing `array_values($row)` to `fputcsv()`, create an array with the structure/values you want and pass that instead. — gen_Eric, Jan 06 '15 at 21:04
@RocketHazmat: With your suggestion, I wrote this `code: while ($row = mysql_fetch_row($result)) { $personal = array($row['fullname'], $row['institution'], $row['email'], $row['timestamp']); $scores = json_decode($row['responsesJSON'], true); fputcsv($fp, $personal); fputcsv($fp, $scores); } ` However, I just get a blank CSV. What could I be doing wrong? Edit: sorry for the formatting disaster. — chakeda, Jan 06 '15 at 22:39

score 2 · Answer 1 · answered Jan 06 '15 at 21:05

2

Try this way

while ($row = mysql_fetch_row($result)) {
        $marks = json_decode($row['responsesJSON'],true);
        unset($row['responsesJSON']);
        fputcsv($fp, array_merge(array_values($row),array_values($marks)));
    }

That is not best solution, but just food for thought and point to start.

Hope it could help

answered Jan 06 '15 at 21:05

Alex

16,739
1
28
51

I copy pasted the code and it resulted in a corrupt CSV, but it is a good point to start from. +1 – chakeda Jan 06 '15 at 21:37
i don't have any data to test, and you just posted screenshots no data samples. so the main issue is if every json array has the same number of marks and all them are ordered. if not you have to do some transformation with the $marks before merging arrays. – Alex Jan 06 '15 at 21:44

score 1 · Accepted Answer · answered Jan 06 '15 at 23:02

Much thanks to @KimAlexander and @RocketHazmat's suggestions, I figured it out:

function buildcsv(){
    $start = $_POST['start'];
    $end = $_POST['end'];
    $result = mysql_query('SELECT fullname, institution, email, timestamp, responsesJSON
        FROM `members` WHERE timestamp BETWEEN "'.$start.'" AND "'.$end.'"');
    if (!$result) die('Couldn\'t fetch records');
    ob_start();
    $fp = fopen('php://output', 'a');
    if ($fp && $result) {
        while ($row = mysql_fetch_assoc($result)) {
            $personal = array($row['fullname'], $row['institution'], $row['email'], $row['timestamp']);
            $scores = json_decode($row['responsesJSON'], true);
            fputcsv($fp, array_merge($personal, $scores));
        }
        fclose($fp);
        return ob_get_clean();
    }else{
        $error = "Error. Either there is no data or invalid date.";
        return $error;
    }

}

PHP - fputcsv: put JSON values retrieved from MySQL field in separate columns

2 Answers2