#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    4

    MySQL Export to CSV via PHP and Download


    Hi All,

    I'm hoping someone can advise of a script I can use that would download the results of a query to a CSV file and present it for download for a user please?

    I've found and doctored the following, but on lcicking the button, it either doesn't run, or the CSV that is download just contains the HTML coding of the web page.
    Likewise, if I un-comment the last echo line, the results appear on the html page, even before I've pressed the button.

    Any help would be greatly appreciated.

    PHP Code:
           <td>
                <form action="export.html" method="post" target="frame_e">    
                
                        <?php
            
    require_once('db_init.php');
            
    $selectedcategory $_POST["Category_ID"];

            
    /* vars for export */
    // database record to be exported
    $db_record 'XXX_TABLE_NAME_XXX';
    // optional where query
    $where 'WHERE 1 ORDER BY 1';
    // filename for export
    $csv_filename 'db_export_'.date('Y-m-d').'.csv';
    // database variables
    //$hostname = "XXX_HOSTNAME_XXX";
    //$user = "XXX_USER_XXX";
    //$password = "XXX_PASS_XXX";
    //$database = "XXX_DATABASE_XXX";
    //$port = 3306;
    //$conn = mysqli_connect($hostname, $user, $password, $database, $port);
    $conn mysqli_connect($DBHOST$DBUSER$DBPASS$DBNAME) or die('Could not connect to database server.');
    if (
    mysqli_connect_errno()) {
        die(
    "Failed to connect to MySQL: " mysqli_connect_error());
    }
    // create empty variable to be filled with export data
    $csv_export '';
    // query to get data from database
    $sql "select tblClient.*, tblLookUp_Category.* from tblClient inner join tblClient_Category on tblClient.Client_ID = tblClient_Category.Client_ID inner join tblLookUp_Category on tblClient_Category.Category_ID = tblLookUp_Category.Category_ID Where tblClient_Category.Category_ID = ".$selectedcategory."";
    $query mysqli_query($conn$sql);
    $field mysqli_field_count($conn);
    // create line with field names
    for($i 0$i $field$i++) {
        
    $csv_export.= mysqli_fetch_field_direct($query$i)->name.';';
    }
    // newline (seems to work both on Linux & Windows servers)
    $csv_export.= '
    '
    ;
    // loop through database query and fill export variable
    while($row mysqli_fetch_array($query)) {
        
    // create line with field values
        
    for($i 0$i $field$i++) {
            
    $csv_export.= '"'.$row[mysqli_fetch_field_direct($query$i)->name].'";';
        }
        
    $csv_export.= '
    '
    ;
    }
    // Export the data and prompt a csv file for download
    header("Content-type: text/x-csv");
    header("Content-Disposition: attachment; filename=".$csv_filename."");
    //echo($csv_export);
            
    ?>
    <input type="Submit" value="CSV">
            </form>            
            </td>
    Many Thanks,
    Graham
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,370
    Rep Power
    631
    Headers must the the very first thing output on a page. Even white space is not allowed.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    4
    Originally Posted by gw1500se
    Headers must the the very first thing output on a page. Even white space is not allowed.
    Thank you very much for pointing out this error, but I can't see in the script where it is occurring?

    Many Thanks,
    Graham
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,370
    Rep Power
    631
    What you posted shows some HTML before your PHP code as well as before you output the headers.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2014
    Posts
    40
    Rep Power
    4
    Originally Posted by gw1500se
    What you posted shows some HTML before your PHP code as well as before you output the headers.
    Many Thanks,

    The HTML at the top was included just for context to show where I was using the PHP. I'm afraid I can't see myself the html you make reference to towards the end of the script mixed in with the headers.


    Many thanks,
    Graham
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,370
    Rep Power
    631
    When the blank page is displayed look at the HTML source to see what is being output.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo