PHP - MySQL Data Output to MS Excel

[PimCaris]

Dear Forum Members,

 

I'm trying to create a snippet of PHP code which can select a specific number of rows from a Database table (Database "formulier", "tables under invoer"), and output this data into a .cvs file.

The script: export.php

This script will be running on a website, where after people click on a button and submit a client-specfic ID and Last Name, they get a .csv to download for administrative use.

 

Needless to say, I want the script to generate a .csv file with all entries (different dates of entries, total 23 variables) of one ID-number from the database.

I've started to work on this, and whilst it works for the larger part, I'm really stuck at the moment.

I hope that some of you can shed some light on the issue.

 

Attached are the scripts I'm currently running.

formulier.htmlinsert.php

Please note that they're definetly not final, and still insecure.

That's not the issue right now.

 

Kind regards,

[dalecosp]

I've had good luck with the PHPExcel package for this sort of thing.  Brief example:

include "includes/PHPExcel.php";
 
 
      $template_file="includes/Load_Form.xlt";
 
      $objPHPExcel = PHPExcel_IOFactory::load($template_file);
 
      $dater=date("m/d/Y");
 
      $objPHPExcel->getProperties()
            ->setCreator("Foo.com")
            ->setLastModifiedBy("Foo.com/excel.php")
            ->setTitle("Foo.com Sample Inventory")
            ->setSubject("Products for Sale")
            ->setDescription("This is all data on products on FOO.com");
 
      $objPHPExcel->setActiveSheetIndex(0); // first sheet in the active sheet
      $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(1);

     $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
 
   /* Looping structure for data dump */
 
      $sheetrow=6; // Set header cell rows
 
      $objValidatin = $objPHPExcel->getActiveSheet()->getCell('A6')->getDataValidation();
      $objValidatin->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
      $objValidatin->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
      $objValidatin->setAllowBlank(false);
   
     //blah, blah .....
 
     //some kind of loop around this
 

            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, $sheetrow, "Products for Sale");
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1, $sheetrow, $category);
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2, $sheetrow, $brandname);
 
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3, $sheetrow, str_replace($bad_char_list,$repl_char_list,$title));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4, $sheetrow, str_replace("\n",'',nl2br(str_replace($bad_char_list,$repl_char_list,$description))));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5, $sheetrow, $price);
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6, $sheetrow, urldecode($postcurrency));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(7, $sheetrow, urldecode($condition));
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(8, $sheetrow, urldecode($product_type));

Made life pretty easy where "Excel" is concerned.

[Psycho]

Needless to say, I want the script to generate a .csv file with all entries (different dates of entries, total 23 variables) of one ID-number from the database.

I've started to work on this, and whilst it works for the larger part, I'm really stuck at the moment.

I hope that some of you can shed some light on the issue.

 

Wait. So what IS the issue. The title suggests you want an Excel file, but the content of your post states you want a CSV file. Then you state it "works" for the most part and you are now "stuck". But, you don't state what problem you are currently facing.

[Psycho]

OK, I've looked at your code and there seemed to be quite a few problems. For example, you were using mysql and mqsqli functions.  I have rewritten it as two functions. One to get the data from the database and another one to create/output the CSV file. That way you can reuse the output CSV function for other datasets as needed.

 

 

<?php
 
function getInvoerData()
{
 
    //Connect to database
    $con = mysqli_connect("localhost", "root", "root", "formulier");
    //Check if connection failed
    if (!$con)
    {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      exit;
    }
 
    //Create and run query to get the data
    $ID         = intval($IDnummer_E);
    $Achternaam = mysqli_real_escape_string($con, $Achternaam_E);
    $query = "SELECT * FROM invoer WHERE IDnummer = '{$ID}' AND Achternaam = '{$Achternaam}'";
    $result = mysqli_query($con, $query);
    //Check if query failed
    if(!$result)
    {
        echo 'Could not run query: ' . mysqli_error($con);
        exit;
    }
 
    //Put data into array
    $data = array();
    while($row = mysqli_fetch_assoc($result))
    {
        $data[] = $row;
    }
    return $data;
}
 
function csv_export($data, $baseName)
{
    //Add a time stamp to file name, to avoid duplicates
    $date = new DateTime();
    $ts = $date->format('Y-m-d His'); //Note: file names cannot have ':'
    $filename = "{$baseName}_{$ts}.csv";
 
    //Set header to expect a CSV file and bring up the save dialog
    header("Content-Type: text/csv");
    header("Content-Disposition: attachment; filename={$filename}");
    header("Pragma: no-cache");
    header("Expires: 0");
 
    //Open up the output buffer as a "file"
    $fh = fopen('php://output', 'w');
 
    //Create the header row
    fputcsv($fh, array_keys($data[0]));
    //Output the data
    foreach($data as $row)
    {
        //Add the current line to the output buffer
        fputcsv($fh, $row);
    }
 
    // Close the output buffer
    fclose($fh);
}
 
 
 
//Execute the functions
$data = getInvoerData($_POST["IDnummer"], $_POST["Achternaam"]);
csv_export($data, 'report');
exit();
 
?>