Solved

Create an SQL query with a date function

Posted on 2010-08-12
5
317 Views
Last Modified: 2013-12-13
Hi

I need  to create an SQL query that will either be run on a weekly or monthly basis. The data collected from the query will then be exported in a csv format, then imported into an email marketing program.
The second time the query is run it will only need to retrieve new data that has been entered into the table since the previous query was run, not the data that was already captured from the previous running of the query.

Is this possible and how do I achieve this?

I'm using phpMyAdmin 3.2.0.1 Apache 2.2.11 php 5.3.0 with joomla 1.5
0
Comment
Question by:LLC0706
5 Comments
 
LVL 11

Expert Comment

by:mattibutt
ID: 33425106
anything is possible you need to use either stored procedures or triggers what it will do basically run always and also you can detail logical programming maybe part of the same stored procedure can provide details to php codes which can turn the data into the cvs format
i hope it gives you basic idea right now i see your problem vague break it down into smaller parts and accomplish one at a time
0
 
LVL 13

Assisted Solution

by:p_nuts
p_nuts earned 250 total points
ID: 33425201
you can do it in php but you would need a check on date ...

you could add a field last exported..

set this to null on changes or inserts and export only the fields that are null.

you could run this manually . or through crum jobs or at the end of your main page flush(); and than run a routine that checks there's been an export.
0
 
LVL 15

Expert Comment

by:gplana
ID: 33431261
to run in a weekly or monthly basis, you should use crontab (if your web server is a Linux Server). you can execute any command at your desired frequency. To execute a php page, you should put the command:

    curl your_web_page_full_path

Hope it helps.
0
 
LVL 2

Accepted Solution

by:
binghu earned 250 total points
ID: 33434567
Just add a new column to the table, eg. name it upto, which you can update to getnow() every time you finish collecting process.

The next time you want to run the collecting process, select * from table where upto is NULL
0
 

Author Closing Comment

by:LLC0706
ID: 33441994
Thanks for your help with this.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question