How to convert PHP code to MySQL query to CSV

0 votes
What is the most efficient way to convert a MySQL query to CSV in PHP please?

It would be best to avoid temp files as this reduces portability (dir paths and setting file-system permissions required).

The CSV should also include one top line of field names.
Aug 20, 2020 in PHP by kartik
• 37,490 points
224 views

1 answer to this question.

0 votes

Hello @kartik,

Try this:

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

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";

Hope it helps!!

Thank you!!

answered Aug 20, 2020 by Niroj
• 82,680 points

Related Questions In PHP

0 votes
1 answer

How to convert timestamp to date in MySQL query?

Hello @kartik, Try this: DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS ...READ MORE

answered Aug 19, 2020 in PHP by Niroj
• 82,680 points
2,375 views
0 votes
1 answer

What is Php Mysql Database? How php connect to database?

Hello kartik, MySQL is the most popular database ...READ MORE

answered Feb 25, 2020 in PHP by Dey
249 views
0 votes
1 answer

How to retrieve or obtain data from the MySQL database using PHP?

Hello kartik,  Actually there are many functions that  ...READ MORE

answered Mar 27, 2020 in PHP by Niroj
• 82,680 points
362 views
0 votes
0 answers
+1 vote
1 answer

How to make anchor tag with routing using Laravel?

Hey @kartik, First you have to go to ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,680 points
6,643 views
0 votes
1 answer

What is redirection in Laravel?

Named route is used to give specific ...READ MORE

answered Mar 18, 2020 in Laravel by Niroj
• 82,680 points
777 views
0 votes
1 answer

How to install Laravel via composer?

Hello, This is simple you just need to ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,680 points
581 views
+1 vote
1 answer

What are named routes in Laravel and How can specify route names for controller actions?

Hey @kartik, Named routing is another amazing feature of ...READ MORE

answered Mar 23, 2020 in Laravel by Niroj
• 82,680 points
7,056 views
0 votes
1 answer

How to convert from MySQL datetime to another format with PHP?

Hello, To convert a date retrieved from MySQL ...READ MORE

answered May 19, 2020 in PHP by Niroj
• 82,680 points
187 views
0 votes
1 answer

How to output MySQL query results in CSV format?

Hello @kartik, Using this command columns names will ...READ MORE

answered Aug 18, 2020 in PHP by Niroj
• 82,680 points
335 views