How can I export a csv file of the sql data with php? [duplicate]

Question

I currently have an app that displays about 4,000 items. I am pulling this from a mysql database. I am trying to create a button that will force a download of all of the items from the database into a csv file. How can I go about this?

csv.php

<?php

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');

$output = fopen('php://output', 'w');

fputcsv($output, array('Column 1', 'Column 2', 'Column 3'));

mysql_connect('localhost', 'root', 'root');
mysql_select_db('wp_xroads');
$rows = mysql_query('SELECT first_name,last_name,phone FROM bags');

while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
?>

html

<div class="col-md-2">
  <!-- Button to trigger csv file exporting -->
    <form action="csv.php" method="get">
        <a class="btn btn-success" type="submit" target="_blank">
          Export CSV <span class="badge"><span class='glyphicon glyphicon-save-file'></span></span>
        </a>
    </form>
</div>

thanks in advance. -ken

@yesitsme , how would I link the the button in the html to the script? — Ken Ryan
– Ken Ryan, Commented Jul 20, 2015 at 23:37
I'm crying, just completed the answer with complete instructions how OP can achieve it and now can't post it — Shehary
– Shehary, Commented Jul 20, 2015 at 23:58

Drew · Accepted Answer · 2015-07-20 23:55:37Z

2

the SELECT INTO OUTFILE is the opposite of LOAD DATA FILE. This puts the results of the select statement into the specified file. No slow, zanny, non-performant php looping. The export_options clause is in the same flavor of the options in LOAD DATA INFILE.

https://dev.mysql.com/doc/refman/5.1/en/select-into.html

SELECT col1,col2,col3 INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;


SELECT first_name,last_name,phone INTO OUTFILE '/tmp/myBags.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM bags;

edited Jul 20, 2015 at 23:55

answered Jul 20, 2015 at 23:39

Drew

25k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Drew Over a year ago

just left ya a comment (notice his * and from) @BK435

Ken Ryan Over a year ago

hey @drew-pierce, Is what you provided the query I would be using to get the csv file? would I just link the php file to an <a> tag? Thanks for your help.

Drew Over a year ago

Check out bottom of edit above. how you call it is up to you (remember, they are writing to your file system :<). But it could be in a protected area somewhere in your app. Just about anywhere. So you want this to be more of an admin rights thingie.

Drew Over a year ago

That select statement can be as complicated as you want. It can be a huge join, computed columns, you name it.

Ken Ryan Over a year ago

Let me give it a try :) thank you

Collectives™ on Stack Overflow

How can I export a csv file of the sql data with php? [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related