Solved

Create an SQL query with a date function

Posted on 2010-08-12
5
318 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

840 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