Query MySQL and Export Data as CSV in PHP

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.

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/

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);
}

Use PHP to get Column Names and Data for CSV export (MYSQL)

Since you're using mysqli_fetch_assoc, the name of the columns are the keys of the $row array in each iteration. You can put that in the file in the first iteration:

echo "Starting Write to CSV \n";
$first = true;
while($row = mysqli_fetch_assoc($result)){
    if ($first) {
        fputcsv($fp, array_keys($row));
        $first = false;
    }
    fputcsv($fp, $row);
    // ..
}

PHP: exporting mysql database into CSV based on 2 columns?

This should work - the concept you're looking for is inner-joining the table to itself in order to match different rows to each other, based on the referral codes:

select 
  A.email as 'referrer', 
  B.email as 'referred'
from 
  users A
  inner join users B
    on A.referral_code = B.referral_code_used

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.

Export a mysql table into CSV using PHP code

Before for loop initialize $header to empty string.

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

    echo $header;//remove this line
}

EDIT

Also initialize $data outside while loop.

$data = '';
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";
}

---

Related Topics