1

I need to export a table named 'app_users' from my database, into a spreadsheet, a csv for example. How do I do this? Also, how do I send this file to browser? I am using Zend framework.

Sonu Jha
  • 719
  • 3
  • 13
  • 23

2 Answers2

1

Per this answer you could run the following SQL query if you have direct access to the filesystem the db server is on:

SELECT *
FROM app_users
INTO OUTFILE '/tmp/app_users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

EDIT: You'll probably also need to delete this file using php after it's created and served to the user, otherwise the next time the query above is run it might refuse to overwrite the file (that's the default).

Community
  • 1
  • 1
Ryan Weir
  • 6,377
  • 5
  • 40
  • 60
  • Thanks Ryan! and how do I send it to the browser so that the user is prompted with a "save" dialog? – Sonu Jha Jul 05 '13 at 14:07
  • If you're executing this sql command in php on the server, add code after it in php that will serve the file back to the user's browser. See http://stackoverflow.com/a/2028918/808532 for an example, although you'll need to change the header types to csv. – Ryan Weir Jul 05 '13 at 14:13
  • it worked like a charm! Thanks! But, the csv file does not contain any field names in the top row. Is there a way to do that? – Sonu Jha Jul 08 '13 at 07:41
  • 1
    Unfortunately you have to do it manually with a UNION in the query along the lines of `SELECT 'first', 'second', 'third' UNION ALL SELECT * FROM app_users`...see http://www.pressingquestion.com/1528089/Include-Headers-When-Using-Select-Into-Outfile%3F – Ryan Weir Jul 08 '13 at 07:49
  • also, I am trying to delete this file using unlink(). But since the owner of teh file is mysql, I cannot. How do I fix this? – Sonu Jha Jul 08 '13 at 08:05
0

by clicking export button in mysql toolbar to sql database

user2551695
  • 7
  • 3
  • i need to send the file to the browser when a visitor clicks on a download button in my website! :) – Sonu Jha Jul 05 '13 at 14:06