Creating .csv files using PHP from DB query

[showman13]

Good Afternoon.

I've been sporatically researching this for weeks, but haven't been able to locate any 'CLEAR', 'PRECISE' direction on creating .csv files from a query of multiple tables.

What I need to be able to do is gather the relavent information from the tables and combine it into a .csv file with a column header line and one or more lines of data.

This is to submit an order to the printer for business cards, brochures, sales agreements, etc when it is placed by one of our dealers.

I've see probably 50+ samples/examples/snippets/ etc. but haven't been able to glue together enough to create the desired results.

Does anyone have any experience creating .csv files in PHP from DB data?

Thanks in advance.
Douglas

[pixelsoul]

I have done it with MSSQL, but it would not be really any different in terms of the PHP that you would need to use to create the CSV file. I would think about the problem in two parts, the first would be getting my query ready to return the array of data that I want in the CSV file, then I would work on the code for generating the CSV file.

It would probably be best to try and get it all in one single query to the database so you can just return the one array. If you are pulling from different tables you could use SQL joins. If for some reason you can't do it all in one query, then you would need to probably combine the arrays together.

[diafol]

I can't see how it would matter where the data comes from. If you can create a csv file from an array, you can create it from a DB.

A frequently used piece of code (not mine!):

header("Content-type: text/csv");
header("Content-Disposition: attachment; filename=file.csv");
header("Pragma: no-cache");
header("Expires: 0");

$array = array(
    array("data11", "data12", "data13"),
    array("data21", "data22", "data23"),
    array("data31", "data32", "data23"));
outputCSV($array);

function outputCSV($data) {
    $outstream = fopen("php://output", "w");
    function __outputCSV(&$vals, $key, $filehandler) {
        fputcsv($filehandler, $vals); // add parameters if you want
    }
    array_walk($data, "__outputCSV", $outstream);
    fclose($outstream);
}

It's your $array (above) that you want to create - something like this - you may have to put double quotes around the string fields though - haven't tried it:

$array = array();
while($data = mysql_fetch_assoc($result))
{
    $array[] = array($data['id'], ....);
}

[pixelsoul]

If you can create a csv file from an array, you can create it from a DB.

That was my thought as well, but in reading it, it seemed the real problem he has is not being able to get the data into an array that can be used to generate the CSV. That is the reason I recommended he get his array worked out first.

[diafol]

Aha, yes reading again, this seems to be the case. OK Doug, show use your query and tell us the structure of the CSV (fields and type [int, varchar etc]) with header row that you want to produce.

[showman13]

Thank you both for your responses... it isn't about getting the data to the array. I can do that without any issues I think. And I would do it with a single query using joins between the 3 tables that the data will come from.

The issue was in creating the .csv so I could send the file to the printer as an attachment in an email, where they can apply it in their design software to insert the necessary fields in the proper places on the printed documents.

I think what you posted diafol will be the answer to my question... will give it a shot tonight when it is quiet

thanks again
douglas

[Atli]

Being somewhat overly cautious about performance, I see a potential issue with the method you provided, diafol. That is, it requires the entire result set to be placed in PHP memory before it can be added to the CSV output stream. For large data sets that may become an issue.

I would suggest merging the database fetching with the output generation, so each row of the database result would only have to be maintained in PHP long enough to print it. - This is an example based on old code I have used myself. (Though not for CSV specifically, I just adjusted that part for this thread.)

<?php

// The database query details. If you are using a more complex
// query, this will need to be revised. The part later where
// the header row is printed will have to be revised as well
// to match that! (Line 41)
$fields = array("id", "name", "pancakes!");
$table = "users";

try {
    $dsn = "mysql:host=localhost;dbname=test";
    $dbLink = new PDO($dsn, "user", "password");

    // By default PDO will return rows using the FETCH_BOTH method, so
    // that needs to be changed to either FETCH_NUM or FETCH_ASSOC.
    // Otherwise you'll get each field value twice per row.
    // I'm doing it here, as the default fetch mode, for simplicity. It
    // can also be done during the fetch.
    $dbLink->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
}
catch (Exception $e) {
    echo "Database connection failure!";
    trigger_error("Failed to connect to database: " . $e->getMessage(), E_USER_ERROR);
}

// Query the fields listed in the array above.
$sql = "SELECT `" . implode("`,`", $fields) . "` FROM `{$table}`";
$result = $dbLink->query($sql);

if ($result) {
    $fh = fopen("php://output", "w");
    if ($fh) {
        // Set headers to make the browser read this as a CSV file.
        // (Shamelessly copied from diafol's post :D)
        header("Content-type: text/csv");
        header("Content-Disposition: attachment; filename=file.csv");
        header("Pragma: no-cache");
        header("Expires: 0");

        // Print the header row.
        fputcsv($fh, $fields); 

        // Go through the rows and write them to the output stream.
        foreach ($result as $row) {
            fputcsv($fh, $row);
        }

        // And that's it!
        fclose($fh);
    }
    else {
        echo "Stream failure!";
        trigger_error("Failed to open output stream for CSV writing.", E_USER_ERROR);
    }
}
else {
    echo "Database failure!";
    trigger_error("Failed to query fields from database.", E_USER_ERROR);
}

[iamthwee]

Or maybe

http://phpexcel.codeplex.com/

[diafol]

Good point Atli, however, the OP stated:

What I need to be able to do is gather the relavent information from the tables and combine it into a .csv file with a column header line and one or more lines of data.

So, shouldn't be an issue. Your sol seems better though. :) And BTW - it's not my code! I've seen it posted in a few places, but I'm not sure who the original author is. I think it's based on an entry in manual.

[showman13]

Diafol, I sent you a private message...

[showman13]

OK, looks like I'm not going to be able to accomplish what I thought I could and I'm running short on time.

Is there any chance that one of you accomplished programmers in here would be willing to work with me outside this forum to get this little project completed? Probably wouldn't take you more than a couple of hours at most, and I would be more than happy to compensate you.

If you are interested, please send me a private message to work out the details.

thanks
Douglas

[chrishea]

I've been watching this thread and I'm still not sure that I understand why this is a problem (but I may be missing something). If you can assemble the information into an html table (and put that into a variable), it's easy to create a CSV file. Unless you have a massive amount of data, it shouldn't be a problem reading it from your db tables and creating an html table for the data.

I created an include module a while back that can be used to generate a number of different file types, including CSV. The module is quite simple and it's more of a demo of how easy it is. It includes some example code to use it. You can find it here.

[showman13]

OK, so now that I have the CSV files being created during the order process, I need to be able to attach them to an email to be sent to the printer to process the order.

I am creating basically a stack (array) of the CSV file names as they are being generated, and they are being written to a folder on the server. The names are all unique based on the product ID and the auto incremented item number in a table in the DB...

I have an email function that I have used for a couple of years that works fine, but it doesn't account for any attachments, because I have never included any.

Everything that I have been able to find in searching online has been extremely long drawn out processes with pages of code to accomplish this...

I guess my question is this:
Isn't there some relatively easy way to attach one or more CSV files to an email that is being sent out?

Any suggestions or directions regarding this would be greatly appreciated...

Here is the email function that I currently use:

//**********************************************
// Function to send emails
//  call with email to address / from address / subject / body of message
function mail_sender($to,$from,$subject,$message_body)
{
$message = "<html><head><title>".$sitename."</title></head><body>";
$message=$message.$message_body;
$message .= "</body></html>";
// Always set content-type when sending HTML email
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
$headers .= "From: <".$from.">" . "\r\n"; 
$message = wordwrap($message,70);
// Send email
mail($to,$subject,$message,$headers);
}
//***********************************************

[chrishea]

I use a module called Libmail. It works for me every time.
[Click Here]

[showman13]

Thank you chrishea for your response...

So, I guess that means that there probably isn't a short/easy way to attach CSV files to an email...

LOL

Now that libmail is what I call a VERY LONG script for a little issue... 664 lines long... wow.

I will check it out, and may use it if I can't find a shorthand version to just add attachments to what I am familiar with and already using.

Thanks Again
Douglas

[chrishea]

You can certainly roll your own (including attachments) any time you want to send an email but why bother? You just include the module, add half a dozen lines of code to your program and you're done (for the whole thing not just the attachments). Are you that concerned about minimizing the size of your module at the expense of your own time? How much time have you already spent doing the code, doing the post and so forth? How much time will you need the next time you need to do some email code in a year or two?

When I first started doing PHP, I tried to do email code with attachments from scratch and I found it hard to get the attachment part working. That's when I went looking for an easier alternative. I didn't write this module (I did make a few changes) but it solved my problem. I no longer waste any time working on email code, it just works. As recently as last night, I had a need to send some emails from a system that I am writing and I just added Libmail and the job was done. I also have to send some text messages so I will use something that I wrote before as the basis for that. You don't need a Framework to be efficient (contrary to he impression some people have) but you do need a library of stuff that you can use so you can avoid re-inventing the wheel. It doesn't matter too much if it isn't the most concise or fastest running code as long as it does what it is supposed to do. Most of us aren't writing the next version of Facebook.

[showman13]

Well, I don't make a habit of reinventing the wheel every time I do a project...

I have a 'Functions' script that I include in most scripts and it contains everything that I use on a regular basis, including the mail function.

What I wanted was to just be able to add the logic to allow inclusion of the attachments to my existing mail function, so it would be available on every project that I do from this point forward.

And I may end up using what you suggested, but in the meantime will just keep my eyes open for other options.

thanks again.
Douglas

[showman13]

OK, let me get your opinion on this before I go through the process of implementing a test script...

Like I said, I have a 'functions' script that I include as needed

If I add this function in that collection:

//**********************************************
// NEW Function to send emails using libmail that can include attachments
// $attach is defined in calling script with 1 or more file names to attach
//  call with email address / from address / subject / body of message / attachments

function new_mailer($to,$from,$subject,$message_body, $attach)
{
  include "libmail.php";
    $mail = new Mail();
    $mail->From( "$from" );
    $mail->To( "$to" );
    $mail->Subject( "$subject" );
    $mail->Body( "$message_body" );
    $mail->Attach( "$attach" ) ;
    $mail->Send();  // send the mail
}
//***********************************************

Then in my script set up the variables and make the function call:

 //Send an email to billing department
$to = $billing_mail;
$from = $support_mail;
$subject = "Subject line for the email";
$message = "
    <p>Basic Information in the Email</p>
    <p>With all sorts of good information.</p>
";
$attachments="'printer/BC250-43.csv', 'printer/BC250-44.csv', 'printer/SA100-45.csv'";

new_mailer($to,$from,$subject,$message,$attachments);

Does that look like a sensible / functional way to do what I need to do?

Or am I approaching it incorrectly.

That libmail is actually very interesting... certainly wouldn't want to recreate that...

[showman13]

OK, I put up a little tester script that includes the site configuration and the functions...

When I run it, I get a print out that shows all the variables values, but when it tries to send the email, I get an error message that basically says it can't find the files that are supposed to be attached, so consequently doesn't send the email

here is the tester script that is located at domainname.com/email_tester.php

and the files are located in a folder at domainname.com/printer/filenames.csv

//script name : email_tester
include "all_inc/config_site.php";
include "all_inc/functions.php";
print "made it to email_tester<br>";
 //Send an email to billing department
$to = $billing_mail;
$from = $support_mail;
$subject = "Test Subject line for the email";
$message = "Basic Information in the Email";
$attachments="\"printer/BC250-26.csv\", \"printer/BC250-36.csv\", \"printer/SA100-38.csv\"";
print"
to - ".$to."<br>
from - ".$from."<br>
subject - ".$subject."<br>
message - ".$message."<br>
attachments - ".$attachments."<br>
";

new_mailer($to,$from,$subject,$message,$attachments);
print"The email has been sent<br>";

The function

//**********************************************
// NEW Function to send emails using libmail that can include attachments
// $attach is defined in calling script with 1 or more file names to attach
//  call with email address / from address / subject / body of message / attachments
function new_mailer($to,$from,$subject,$message_body, $attach)
{
  include "libmail.php";
    $mail = new Mail();
  $mail->From( "$from" );
    $mail->To( "$to" );
    $mail->Subject( "$subject" );
    $mail->Body( "$message_body" );
  $mail->Attach( "$attach" ) ;
    $mail->Send();  // send the mail
}
//***********************************************

And here are the results I get when I run it...

made it to email_tester
to - [e-mail]
from - [e-mail]
subject - Test Subject line for the email
message - Basic Information in the Email
attachments - "printer/BC250-26.csv", "printer/BC250-36.csv", "printer/SA100-38.csv"
Class Mail, method attach : file "printer/BC250-26.csv", "printer/BC250-36.csv", "printer/SA100-38.csv" can't be found

Any suggestions or ideas???

[showman13]

And in the tester script, I tried assigning the attachments variable different ways like:

$attachments="'printer/BC250-26.csv', 'printer/BC250-36.csv', 'printer/SA100-38.csv'";
Results
Class Mail, method attach : file 'printer/BC250-26.csv', 'printer/BC250-36.csv', 'printer/SA100-38.csv' can't be found

$attachments='"printer/BC250-26.csv","printer/BC250-36.csv","printer/SA100-38.csv"';
Results
Class Mail, method attach : file "printer/BC250-26.csv","printer/BC250-36.csv","printer/SA100-38.csv" can't be found

Apparently I'm missing something...

[diafol]

$fArray = array( 'printer/BC250-26.csv', 'printer/BC250-36.csv', 'printer/SA100-38.csv' );
foreach ($fArray as $file)
{
    if(file_exists($file))
    {
        echo $file . " exists<br />";
    }else{
        echo $file . " does not exist<br />";
    }
}

CHeck to see they actually exist.

[showman13]

Thank you diafol...

that was a helpful little utility snippet

it shows that they all exist

made it to email_tester
printer/BC250-26.csv exists
printer/BC250-36.csv exists
printer/SA100-38.csv exists
to - [e-mail]
from - [e-mail]
subject - Test Subject line for the email
message - Basic Information in the Email
attachments - "printer/BC250-26.csv","printer/BC250-36.csv","printer/SA100-38.csv"
Class Mail, method attach : file "printer/BC250-26.csv","printer/BC250-36.csv","printer/SA100-38.csv" can't be found

[showman13]

In all these tests, an email has never been sent because of the error, so I decided to make some adjustments and see if it was being sent if the error didn't occur...

I removed the attachments from the tester script, and modified the function to handle it with or without attachments, and it sent the email just fine.

Only issues is in how the attachments are located or named or something because while we know they exist and where they are, the email script doesn't seem to see them.

am I structuring the $attachment variable data incorrectly possibly?

[chrishea]

Attachments require the file name and a type as in:

$m->Attach( "files/file1.jpg", "image/jpeg" ) ;
$m->Attach( "files/file2.jpg", "image/jpeg" )

Use multiple attach statements for as many files as you want to attach.

[showman13]

That did the trick... thank you.
Just realized that there is an issue with sending an html email...

The author doesn't like html and frowns on others using it, so the documentation on it is a bit on the light side...

I got the attachments working just fine by sending the list of file names in an array to my function, and then in the function, I just walk through the array and do individual attachments...

//**********************************************
// NEW Function to send emails using libmail that can include attachments
//  call with email address / from address / subject / body of message / attachments
// $attach is defined in calling script with 1 or more file names to attach
//  attachments must be added individually, so pass them in an array
  // then iterate through them in function, and apply

function new_mailer($to,$from,$subject,$message_body,$attach)
{
  include "libmail.php";
    $mail = new Mail();
  $mail->From( "$from" );
    $mail->To( "$to" );
    $mail->Subject( "$subject" );
    $mail->Body( "$message_body" );
  if (isset($attach)){
    foreach($attach as $att) {
      $mail->Attach( "$att", "file/csv" ) ;
    }
  }
    $mail->Send();  // send the mail
}
//***********************************************

[showman13]

This is all that is said regarding sending an html email, and I have a question if anyone has an answer...

begin quote

Send a mail in HTML format

Fist point: I personally hate receiving HTML mails: I don't recommend to use it.

To send a HTML mail with libMail, you must attach your HTML source as a file:

$file = "mail.html";
$mail->Body( "This mail is formatted in HTML - shame on me" );
// inline intructs the mail client to display the HTML if it can
$mail->Attach( $file, "text/html", "inline" );

If your HTML page contains some images or links don't forget either to :

rewrite them in absolute : /mahomepage.html becomes http://chez.moi.com/mahomepage.html
define a BASE HREF in the HEAD section. All relative links will be relative to this URL

    <head>
        <base href="http://chez.moi.com/">
    </head>

end quote

So, it says that I need to first have the html source file to be attached.
I tried creating a variable that contained the complete html document, but it didn't display it in the email when it was sent.

Is there a way to make the attachment from an internal variable as opposed to an external file?

[showman13]

The End Result

I decided that I really didn't need to send HTML emails, so opted to send text mails with attachments.
I am including the end result of the code (removed some big blocks that aren't important to the process)

Hopefully this will be helpful to someone in the future.
I want to say thank you to all those that helped me get this worked out.

<?php

//  Recap for Dani Web

// Query the data that matches the field names for csv file...
  // I do this in parts, because some of the data is static for all records
  // and some of it is different for each csv file to be created.
  // by creating them as separate datafield collections they can be concatenated
  // at the time of creating the csv files

// get dealer data  - This is the same in all of my csv files
$p_sSql = "
  SELECT *
  FROM dealers
  WHERE del_id = ". $dealer_cart['del_id'];
$_result = mysql_query($p_sSql);

$dealer =  mysql_fetch_array($_result);

// create field list of static information across all files
$dealer_fielddata = '"'.$dealer['user'].'","'.
$dealer['email'].'","'.
$dealer['company'].'","'.
$dealer['addr1'].'","'.
$dealer['add2'].'","'.
$dealer['city'].'","'.
$dealer['state'].'","'.
$dealer['zip'].'","'.
$dealer['phone'].'"';

// Query data for each Active line item in Cart - each creates separate CSV file
$p_sSql = "
  SELECT *
  FROM dealer_item
  WHERE cart_id = ". $cart_id."
  AND status='A'
";
$_result = mysql_query($p_sSql);

if (($_result) && (mysql_errno() == 0) && (mysql_num_rows($_result) > 0)){
  // create empty array to stack filenames on for use in sending in email
  $stack=array();
    while ($_rows = mysql_fetch_array($_result)) {
// CSV field names - Start with base list with for each file creation
$_csvFields = 'item_id, product_id, qty, user, email, company, addr1, addr2, city, state, zip, phone';
      // create file name for csv files in printer/orders folder
      // initial $csv_filename - will have unique data appended later
        // so each filename remains unique
        $csv_filename = 'printer/orders/'.$_rows['product_id'];// base filename

//######  This is a way to add optional data into a csv file creation ######
    // including adjusting the field names and corresponding data
    // In this case (my use), if it is a Business card order, then it requires
      // a few additional pieces of data be sent to the printer for the order

    // get first 2 characters from product ID - check for BC
    $bc_pref=(substr($_rows['product_id'], 0, 2));
      // if $bc_pref is 'BC' - Business Card order - include back color in
      // file name - need to query the BC_profile table based
      // on profile_id to get remaining fields
    if ($bc_pref=='BC'){
      $bc_sSql = "
        SELECT full_name, title, cell, email, card_back FROM BC_profile
        WHERE profile_id = ". $_rows['profile_id'] ."
        AND status='A'
      ";
          $bc_result = mysql_query($bc_sSql);
      $bc_rows = mysql_fetch_array($bc_result,MYSQL_ASSOC);
      // $bc_rows[0-4] contain Business Card specific info

      // need to append the NEW BC field names to the $_csvFields variable
      $_csvFields .= ', full_name, title, cell, card_email, card_back';
      // Create BC field data variable containing returned values for this file
      $BC_fielddata = '"'.
          $bc_rows[0].'","'.
            $bc_rows[1].'","'.
            $bc_rows[2].'","'.
            $bc_rows[3].'","'.
            $bc_rows[4].'"';
      // Append order detail to the filename, for the Printer's use
      $csv_filename .= '-'.$bc_rows[4];// add back color to csv file name
    }
//######  ENDS addition of optional fields and data ######

    //  Append the unique identifier and the file extension
      // in this case the 'item_id' is a unique record number from the DB table
    // completes csv file name to be created
        $csv_filename .= '-'.$_rows['item_id'].'.csv';
    // Create field data variable containing unique values for this csv file
        $item_fielddata = '"'.
        $_rows['item_id'].'","'.
        $_rows['product_id'].'","'.
        $_rows['qty'].'"';
    //  Consolidate field data for all orders in same order as csv fields
    $field_data=$item_fielddata.','.$dealer_fielddata;
    //  if a BC order then append the $BC_fielddata to $field_data
    if ($bc_pref=='BC'){
      $field_data.=','.$BC_fielddata;
    }
    if (!createCSVFile($csv_filename, $_csvFields, $field_data)) {
      // this basically creates the CSV file (hopefully) but if it doesn't
        // this is the error checking routine that emails me that it failed
      //Send an email to billing department
      $to = $billing_mail;
      $from = $support_mail;
      $subject = "CSV File creation failed";
      $message =$subject."===========>Investigate DC Order ID ".$orderid.".\n";
      new_mailer($to,$from,$subject,$message);
        }else{// after successful csv file creation update to Ordered
      // This marks the Shopping Cart as 'O'rdered
          $status_u="
        UPDATE dealer_item
        SET status='O'
        WHERE item_id=".$_rows['item_id'];
          mysql_query($status_u);

      // ##  IMPORTANT - add new csv file name to stack for later use
      array_push($stack, "$csv_filename");
        }
    // in my case all printed products will come from a single vendor.
    // at some point, I will change this to identify multiple vendors and be
    // able to send multiple emails/ orders to multiple vendors if needed
    $last_product=$_rows['product_id'];// get product id for vendor search
    } // while

  // AT this point, each line item has had a separate CSV file created
    //================ Get vendor ID ========================
    $v_sSql = "
    SELECT vendor_id
    FROM dealer_product
    WHERE product_id = '".$last_product."'";
  $v_result = mysql_query($v_sSql);
  $v_rows = mysql_fetch_array($v_result);
  $vendor_id=$v_rows[0];// get vendor ID for vendor info query
    //================ Lookup Printer Here and complete printer order ========================

  $p_sSql = "
    SELECT *
    FROM product_vendor
    WHERE vendor_id = ". $vendor_id;
  $_result = mysql_query($p_sSql);
  if (($_result) && (mysql_errno() == 0) && (mysql_num_rows($_result) > 0)){
    $vendor =  mysql_fetch_array($_result,MYSQL_ASSOC);
    $vendor_name=$vendor['company'];
    $vendor_contact=$vendor['contact'];
    $vendor_email=$vendor['email'];

    //Send an email to Printing Vendor
    $to = $vendor_email;
    $from = $billing_mail;
    $subject = "Purchase Order for ".$sitename." Dealer";
    $message ="To: \n".$vendor_name."\n".$vendor_contact."\n\n";
    $message .= $subject."\n\nDealer printing order to be processed:";
    $message .= "\n\n" . "Order ID: " .  $orderid ;
    $message .= "\n" . "Process Date: " . date("Y/m/d h:i:s", mktime());
    $message .= "\n\n" . "Please find attached a CSV file for each product ordered.";
    // the magic of emailing the .CSV files happens in the new_mailer function
    new_mailer($to,$from,$subject,$message,$stack);
  }else{
    // error routine to notify that vendor lookup failed
  }
}else{
  // some error routine
}


// Here is the new_mailer function I use
//**********************************************
//  This is a url that you can go to for libmail if you would like.
// http://innovationsdesign.net/wb_2.7/pages/tech-resources/downloads/libmail.php

// NEW Function to send emails using libmail that can include attachments
// $attach is defined in calling script with 1 or more file names to attach
//  call with email address / from address / subject / body of message / attachments
//  attachments must be added individually, so pass them in an array
  // then iterate through them in function, and apply
function new_mailer($to,$from,$subject,$message_body,$attach)
{
  include "libmail.php";
    $mail = new Mail();
  $mail->From( "$from" );
    $mail->To( "$to" );
    $mail->Subject( "$subject" );
    $mail->Body( "$message_body" );
  if (isset($attach)){
    foreach($attach as $att) {
      $mail->Attach( "$att", "file/csv", "attachment" ) ;
    }
  }
    $mail->Send();  // send the mail
}
//***********************************************

?>