Generate multiple CSV files - mysql PHP

Question

I have made this PHP script that should take an array and for each element in the array - generate a csv file. Unfortunately something is wrong. It doesn't store any of the files in the directory specified. But it doesn't return any errors neither. Maybe someone can see the problem?

$ids = json_decode($_POST['jsonarray']); // array sent with ajax
$start = $_POST['start']; // date sent with ajax
$end = $_POST['end']; // date sent with ajax

$start_date = date('yyyy-mm-dd', strtotime($start)); // format dates to sql firendly
$end_date = date('yyyy-mm-dd', strtotime($end));

$toZip = array(); // Prepare array to files for zip

if(is_array($ids)) {
    foreach ($ids as $key => $qr)
    {
        // Get labels first
        // Here we prepare the first line in the .CSV file
        $tb = $qr . '_labels';
        $sql = $user_pdo->query("SELECT * FROM $tb");
        $head_array = array('Log ID', 'Timestamp');
        while ($row = $sql->fetch(PDO::FETCH_ASSOC))
        {
        // This array is the first line in the .CSV file
            $head_array[] = $row['label'];
        }

        // Get ready for looping through the database
        $table = $qr . '_data';
        $results = $user_pdo->prepare("SELECT * FROM $table WHERE timestamp BETWEEN :start_date AND :end_date;");
        $results->bindParam(':start_date', $start_date, PDO::PARAM_STR);
        $results->bindParam(':end_date', $$end_date, PDO::PARAM_STR);
        $results->execute();

        // Pick a filename and destination directory for the file
        $filename = "temp/db_user_export_".time().".csv";

        // Actually create the file
        // The w+ parameter will wipe out and overwrite any existing file with the same name
        $handle = fopen($filename, 'w+');

        // Write the spreadsheet column titles / labels
        fputcsv($handle, $head_array);


        // Write all the user records to the spreadsheet
        foreach($results as $row)
        {
                // amount of rows is unknown
            $rows = $row->rowCount();
            $insert_array = array();
            for ($i=0; $i<=$rows; $i++)
            {
                // function goes here
                $insert_array[] = $row[$i];
            }

            fputcsv($handle, $insert_array);
        }

        // Finish writing the file
        fclose($handle);

        $toZip[] = $filename;
    }
}

Example on var_dump($ids);

array(4) {
  [0]=>
  string(5) "t23ry"
  [1]=>
  string(5) "6us32"
  [2]=>
  string(5) "se43z"
  [3]=>
  string(5) "o00gq"
}

The return value for json_decode is, according to PHP.net's docs, "mixed". At times json_decode will return an object. — SamA
– SamA, Commented Oct 17, 2013 at 16:24
What may be the problem that your files are not getting written is the temp/ folder declaration in $filename = "temp/db_user_export_".time().".csv"; - Try setting it to $filename = "db_user_export_".time().".csv"; for testing purposes. — Funk Forty Niner
– Funk Forty Niner, Commented Oct 17, 2013 at 16:29
I have tested some more.. I can create the csv files now, but only the first row is written. no additional content is made. (the problem was that all the .csv files had the same name $filename = "temp/db_user_export_".time().".csv"; but by adding the unique $qr-variable that solved that issue.) — Corfitz.
– Corfitz., Commented Oct 17, 2013 at 16:36

SirDerpington · Accepted Answer · 2016-08-03 10:25:19Z

1

I found the answer. After a long time searching and playing around, I saw that this function

    foreach($results as $row)
    {
            // amount of rows is unknown
        $rows = $row->rowCount();
        $insert_array = array();
        for ($i=0; $i<=$rows; $i++)
        {
            // function goes here
            $insert_array[] = $row[$i];
        }

        fputcsv($handle, $insert_array);
    }

didn't work because of following:

$rows = $row->rowCount(); has to be $rows = count($row);
The number of string in the returned $row array was higher than expected so I needed to change my select statement to $results = $user_pdo->query("SELECT * FROM $table WHERE timestamp >= '$start' AND timestamp <= '$end'";, PDO::FETCH_NUM);. This will only give me the rows in numeric order, which will make the $row[$i] -> array work.
Also as you can see, I changed the prepared statement to a query instead, and also changes the start date and end date variables to be unformatted.

This really took some time, but it is finally working. Thanks a lot for all the support guys.

edited Aug 3, 2016 at 10:25

SirDerpington

11.5k4 gold badges52 silver badges55 bronze badges

answered Oct 17, 2013 at 17:59

Corfitz.

1,8844 gold badges32 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Funk Forty Niner Over a year ago

Ah yes, very good. I'm glad it finally worked out. I didn't know what else to tell you earlier. Problem solved :) cheers (+1)

SamA · Accepted Answer · 2013-10-17 16:39:25Z

0

fputcsv only outputs a line at a time. Change this:

        for ($i=0; $i<=$rows; $i++)
        {
            // function goes here
            $insert_array[] = $row[$i];
        }

        fputcsv($handle, $insert_array);

To this:

        for ($i=0; $i<=$rows; $i++)
        {
            // function goes here
            fputcsv($handle, $row[$i]);
        }

answered Oct 17, 2013 at 16:39

SamA

5873 silver badges6 bronze badges

2 Comments

Corfitz. Over a year ago

I know :) Thats why I make an array of all the $row['column'] before I write the line.

SamA Over a year ago

Giving fputscsv an array of arrays will not result in all of the content being written based on what I am seeing in the PHP.net docs.

Collectives™ on Stack Overflow

Generate multiple CSV files - mysql PHP

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related