Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create an SQL query with a date function

Posted on 2010-08-12
5
Medium Priority
?
325 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 1000 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 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

597 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