PHP Code to Convert a MySQL Query to Csv

PHP code to convert a MySQL query to CSV

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

(the documentation for this is here: http://dev.mysql.com/doc/refman/5.0/en/select.html)

or:

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

Printing MySQL query results to CSV file in PHP

Here's an example:

// output headers so that the file is downloaded rather than displayed
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings
fputcsv($output, array('Column 1', 'Column 2', 'Column 3'));

// fetch the data
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
$rows = mysql_query('SELECT field1,field2,field3 FROM table');

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

You can modify it to suit your needs.
Source: http://code.stephenmorley.org/php/creating-downloadable-csv-files/

Query mysql and export data as CSV in PHP

If you want to write each MySQL row to a CSV file, you could use the built in PHP5 function fputcsv

$result = mysqli_query($con, 'SELECT * FROM table');
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

$fp = fopen('file.csv', 'w');

foreach ($row as $val) {
    fputcsv($fp, $val);
}

fclose($fp);

Which should return a comma separated string for each row written to file.csv:

row1 val1, row1 val2
row2 val1, row2 val2 
etc..

Also be sure to check permissions for the directory you are writing to.

Export MySQL data to .csv using PHP

Try this :

$rows = mysql_query("SELECT `name`, `gender` FROM TABLE");

while ($row = mysql_fetch_assoc($rows)) {

    if($row['gender'] == 1) {
        $row['gender'] = 'Male'; 
    } else {
        $row['gender'] = 'Female'; 
    }

    // Or ternary condition
    // $row['gender'] = ($row['gender'] == 1 ? 'Male' : 'Female');

    fputcsv($output, $row);
}

How can I output MySQL query results in CSV format?

From Save MySQL query results into a text or CSV file:

SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: That syntax may need to be reordered to

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

in more recent versions of MySQL.

Using this command, columns names will not be exported.

Also note that /var/lib/mysql-files/orders.csv will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.

If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.

PHP code to convert a MySQL query to CSV

SELECT * INTO OUTFILE "c:/mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM my_table;

(the documentation for this is here: http://dev.mysql.com/doc/refman/5.0/en/select.html)

or:

$select = "SELECT * FROM table_name";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=your_desired_name.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

exporting a mysql query in php version 4 to a csv

You can do this:

$csv_lines = array();

// Loop your records from the DB
while ($row = mysql_fetch_assoc($var1)){

    $columns = array();

    // Loop each column for the row
    foreach($row as $k=>$v){
        // Surround column item in double-quotes and escape double-quotes with double-double-quotes
        $columns[] = '"'.str_replace('"', '""', $v).'"';
    }

    // Join on a comma
    $csv_lines[] = implode(',', $columns);

    // Write to the file right away. You are using PHP4 so I imagine system memory is not plentiful
    // If you use this then there is no need for the "$csv_lines[] =" from above
    // nor the $csv_file_string after the while loop
    // fwrite($f, implode(',', $columns)."\n");
}

// fclose($f); // If you choose to write to the file during the while loop then close the file handle when you are finished

// Create full CSV file by joining on a newline
$csv_file_string = implode("\n", $csv_lines);

Trying to export MySQL query as CSV using PHP

You're mixing mysql_* and mysqli_* API's. mysql_fetch_assoc() will not work with the mysqli_ API. Your second while loop is the one using the wrong function call.

This line:

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

should be change to the correct function call:

// loop over the rows, outputting them
foreach($rows AS $row) fputcsv($output, $row);

---

Related Topics