PHPJunkyard support forum

Code: Select all

<?php

/*

 * PHP code to export MySQL data to CSV

 * http://911-need-code-help.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html

 *

 * Sends the result of a MySQL query as a CSV file for download

 */



/*

 * establish database connection

 */



$conn = mysql_connect('localhost', 'databaseusername', 'databasepassword') or die(mysql_error());

mysql_select_db('databasename', $conn) or die(mysql_error($conn));



/*

 * execute sql query

 */



$query = sprintf('SELECT `hesk_tickets`.`id` AS "Ticket Number", `hesk_tickets`.`dt` AS "Date Created", `hesk_users`.`name` AS Owner, `hesk_categories`.`name` AS Category, `hesk_tickets`.`name` AS "Contact", `hesk_tickets`.`subject` AS Subject, `hesk_status`.`name` AS "Status", `hesk_priority`.`name` AS Priority, `hesk_tickets`.`time_worked` As "Time Worked"

FROM hesk_tickets, hesk_categories, hesk_users, hesk_status, hesk_priority

WHERE (

hesk_categories.id = hesk_tickets.category

AND hesk_users.id = hesk_tickets.owner

AND hesk_status.id = hesk_tickets.status

AND hesk_priority.id = hesk_tickets.priority

)');

$result = mysql_query($query, $conn) or die(mysql_error($conn));



/*

 * send response headers to the browser

 * following headers instruct the browser to treat the data as a csv file called export.csv

 */



header('Content-Type: text/csv');

header('Content-Disposition: attachment;filename=hesk_tickets.csv');



/*

 * output header row (if atleast one row exists)

 */



$row = mysql_fetch_assoc($result);

if ($row) {

    echocsv(array_keys($row));

}



/*

 * output data rows (if atleast one row exists)

 */



while ($row) {

    echocsv($row);

    $row = mysql_fetch_assoc($result);

}



/*

 * echo the input array as csv data maintaining consistency with most CSV implementations

 * - uses double-quotes as enclosure when necessary

 * - uses double double-quotes to escape double-quotes 

 * - uses CRLF as a line separator

 */



function echocsv($fields)

{

    $separator = '';

    foreach ($fields as $field) {

        if (preg_match('/\\r|\\n|,|"/', $field)) {

            $field = '"' . str_replace('"', '""', $field) . '"';

        }

        echo $separator . $field;

        $separator = ',';

    }

    echo "\r\n";

}

?>