How to export data from a mysql database to a CSV file

I have to create a query and export to csv file. The application is an ins agent who  has under him members for a website. The report will be weekly. The user wants to see all the new members under the agent from last  Friday to the current Friday. The date when member  joined is a field on the site. What would the query look like. Is there a way to automatically calcualte the date from the weekend before or will it have to been entered. Can the query to export be trigger weekly or does it have to be manually.
Thanks
HidesignAsked:
Who is Participating?
 
R. Toby RichardsNetwork AdministratorCommented:
Keep in mind that the target CSV file must not exist, and the MySQL process must have access to write to the folder where the CSV file will be created. Here's some sample code:

SELECT orderNumber, status, orderDate, requiredDate, comments
FROM orders
WHERE status = 'Cancelled'
INTO OUTFILE 'C:/tmp/cancelled_orders.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Open in new window

0
 
HidesignAuthor Commented:
Thanks - where would I have the user run the query from.
How would I code the date range in the where clause
0
 
R. Toby RichardsNetwork AdministratorCommented:
For running the query, I'd recommend Heidi SQL.

where date between [start date] and [end date]

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
HidesignAuthor Commented:
Does HeidiSQL generate the SQL to be standalone code or do I do I need to run the query within heidisql
Thanks
0
 
R. Toby RichardsNetwork AdministratorCommented:
Really, the best way to do it would be to set up a script that--depending on your os--is run by Task Scheduler or cron.
0
 
HidesignAuthor Commented:
Ok - would that be a php script.
0
 
R. Toby RichardsNetwork AdministratorCommented:
What is the OS of the MySQL server? It would either be a bash or dos script.
0
 
NerdsOfTechTechnology ScientistCommented:
if you are using scripting such as PHP, you can also use the fputcsv() with more functionality (overwriting ability, etc.)
0
 
HidesignAuthor Commented:
I'm not familiar with that function. Would I still use the mysql code to query the database. Is the php to make the query automatic
0
 
R. Toby RichardsNetwork AdministratorCommented:
What is the OS of the MySQL server?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.