Solved

Create an SQL query with a date function

Posted on 2010-08-12
5
315 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for your help with this.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now