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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.