Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Rodger Voelkel
Rodger Voelkel
21,736 Points
Posted by Rodger Voelkel
Rodger Voelkel
21,736 Points

Export Class

I started working on a export class for a project i am on, but for some reason it seems to cut off the first record when returning data.

class Export {

    function query_to_csv($query, $filename, $attachment = false, $headers = true) {
        $conn = new PDO('mysql:host='.Config::get('mysql/host').';dbname='.Config::get('mysql/db').';charset=utf8', Config::get('mysql/username'), Config::get('mysql/password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); 
        $result = $conn->prepare($query);
        $result->execute();

        if($attachment) {
            // send response headers to the browser
            header( 'Content-Type: text/csv' );
            header( 'Content-Disposition: attachment;filename='.$filename);
            $fp = fopen('php://output', 'w');
        } else {
            $fp = fopen($filename, 'w');
        }

        if($headers) {
            // output header row (if at least one row exists)
            $row = $result->fetch(PDO::FETCH_ASSOC);
            if($row) {
                fputcsv($fp, array_keys($row));
            }
        }

        while($row = $result->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($fp, $row);
        }

        fclose($fp);
    }
}

Anyone have any ideas why this would be happening? I am thinking its because i throw in the headers, but i am not sure.

6 Answers

Rodger Voelkel
Rodger Voelkel
21,736 Points
Rodger Voelkel
Rodger Voelkel
21,736 Points

I solved this incase your curious this was the solution...

class Export {

    function query_to_csv($query, $filename, $attachment = false, $headers = true) {
        $conn = new PDO('mysql:host='.Config::get('mysql/host').';dbname='.Config::get('mysql/db').';charset=utf8', Config::get('mysql/username'), Config::get('mysql/password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); 
        $result = $conn->prepare($query);
        $result->execute();

        if($attachment) {
            // send response headers to the browser
            header( 'Content-Type: text/csv' );
            header( 'Content-Disposition: attachment;filename='.$filename);
            $fp = fopen('php://output', 'w');
        } else {
            $fp = fopen($filename, 'w');
        }

        if($headers) {
            // output header row (if at least one row exists)
            $row = $result->fetch(PDO::FETCH_ASSOC);
            if($row) {
                fputcsv($fp, array_keys($row));
                fputcsv($fp, $row);
            }
        }

        while($row = $result->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($fp, $row);
        }

        fclose($fp);
    }
}

The reason is that the fetch method called to create my headers was fetching the first row in my dataset, so when i then went on to loop through the records the next fetch method was going to the next row in the dataset and processing, so i was getting the array keys to make the headers but not the array values of the first row. Easiest solution was to output the array values immediately after the keys then do the loop for the rest of the dataset. Works like a champ now.

Michael Collins
Michael Collins
433 Points
Michael Collins
Michael Collins
433 Points

How are you instantiating your class?

Usually, you'd create a class with a constructor like this

class Export {

  function __construct() {

  }

}

Or, the older way would be this, with a function the same name of the class. If PHP can't find the __construct() function, it'll look for the old method.

class Export {

  function Export() {

  }

}

Then we'd invoke the class like this:

$e = new Export('arguments');
$e->query_to_csv('or arguments could go here too');
Rodger Voelkel
Rodger Voelkel
21,736 Points
Rodger Voelkel
Rodger Voelkel
21,736 Points

I dont have a construct most of my classes do but for this one i didnt see the need. All i do is invoke the method...

Export.query_to_csv("Arguments");

it works just fine, but it cuts the first record out of the csv that it creates. Everything else is there and the headers are there, but if i give it the argument of headers false the first record is there but no headers.

Michael Collins
Michael Collins
433 Points
Michael Collins
Michael Collins
433 Points

I've never seen that before, not in PHP. That looks more like javascript object syntax.

In PHP you can also call a static method like this 

Export::query_to_csv("Arguments");

But, if you are going to do a static method, you hardly need a class. You might as well just declare a function.

So, where did you read that Export.query_to_csv("Arguments") was an appropriate way to call a class method in PHP? I"m not necessarily saying you are wrong (though I do suspect that's not correct). However, I've never seen that before. If that's acceptable method for calling a class method, I'd like to learn about it.

Rodger Voelkel
Rodger Voelkel
21,736 Points
Rodger Voelkel
Rodger Voelkel
21,736 Points

Sorry I typed that wrong i do it the way you stated. The reason i have it as a class is that i am adding new export methods to it. So that method is query_to_csv, i am adding other export options as well like PDF. While its not necessary to have a class for this i felt it was the best way to keep them all contained while i work on them.

Michael Collins
Michael Collins
433 Points
Michael Collins
Michael Collins
433 Points

Thanks for the clarification. Can you describe what you are passing in? Assuming your are doing this 

Export::query_to_csv($query, $filename, $attachment, $headers);

What are the values you are passing in for each of those arguments?

Rodger Voelkel
Rodger Voelkel
21,736 Points
Rodger Voelkel
Rodger Voelkel
21,736 Points

$query is a sql query string i.e. "SELECT * FROM Table".

$filename is a string of what i want the exported file to be named.

$attachment is a boolean True/False if i want the export to be as an attachment or not.

$headers is another boolean True/False to include a header row or not.

Like i have stated the script works fine except that when i include headers it replaces the first row with headers but doesnt give me the first record. Its like its sending the first record as headers then the rest of the records. When i send a false for headers i get the records as expected, but when i send true for headers i lose the first record but have headers.

Michael Collins
Michael Collins
433 Points
Michael Collins
Michael Collins
433 Points

I"m trying to get enough information from you to answer this question. It's tough going though ...

I'm more than a little familiar with what SQL queries look like "generally". What I'd like to know is -> what does your SQL query look like? The fact that your record is getting cut off would seem to linked to your SQL query. Maybe you could share that with me? Is that proprietary information or something?

Also, can you be more explicit about what you mean by "cutting off"? Are you getting 1 record when you expect several? OR, do you mean that you are only getting part of a record?

If you are getting some type of output, it would be helpful if you'd share the output you are getting -> Copy/Paste the exact output here along with the exact query.