Ad:

PHP Discussion Thread
Unsolved
Views: 1782

Mar 2nd, 2009

Export Data to CSV format, new data since last download

Expand Post »

Thank you for taking the time to read this. I already have code to download as *.csv/*.xls file from my MySQL database (in place)

I need to add a "flag" into the DB as for when the last download was made, and then upon next download, only $fetch_newRecords and reset flag.

Any help, greatly appreciated. I have done a seacrh below, and read thru all but the one with 100+ replies

http://www.daniweb.com/forums/search7477118.html

Thank-you in advance !!

Tro~~

Similar Threads

Help with automatic update problem and more in Viruses, Spyware and other Nasties

trochia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Jan 2008

Permalink

Mar 2nd, 2009

Re: Export Data to CSV format, new data since last download

add column to table download_date with a default value = 0
any newly created rows will have a download date value of zero
a simple table update to post a date to the field for any that have been downloaded already

when downloading update table so that the time the new data was downloaded is recorded.
Then at any later stage reports can be done should any data go missing, of downloads by date and recreate any spreadsheets that go fubar

mysql Syntax (Toggle Plain Text)
/* dbconnect */
SELECT * FROM table WHERE download_date = 0
/* output to csv php script */
/* if (csv file ok ) */
UPDATE table SET download_date = NOW() WHERE download_date = 0
/* else print error message suggest retry etc */
/* dbconnect */
select * from table where download_date = 0
/* output to csv php script */
/* if (csv file ok ) */
update table set download_date = now() where download_date = 0
/* else print error message suggest retry etc */

code NOT correct for your application.
consider answer as a thought exercise / aid
without dbase structure any code would be so wrong as to be ridiculous

Last edited by almostbob; Mar 2nd, 2009 at 11:02 am.

almostbob

Reputation Points: 562

Solved Threads: 368

Posting Maven

Offline

2,970 posts
since Jan 2009

Permalink

Mar 2nd, 2009

Re: Export Data to CSV format, new data since last download

ab, thank's very much for the reply

1) I have just added field of downloadFlag to the table.

2) Type = INT and Default of "0"

As for below, will I also need to to add "time" somewhere into the table?

THX!!

Click to Expand / Collapse Quote originally posted by almostbob ...

add column to table download_date with a default value = 0
any newly created rows will have a download date value of zero
a simple table update to post a date to the field for any that have been downloaded already

when downloading update table so that the time the new data was downloaded is recorded.
Then at any later stage reports can be done should any data go missing, of downloads by date and recreate any spreadsheets that go fubar
mysql Syntax (Toggle Plain Text)
SELECT * FROM table WHERE download_date = 0
select * from table where download_date = 0
and output to csv via your php script
mysql Syntax (Toggle Plain Text)
UPDATE table SET download_date = NOW() WHERE download_date = 0
update table set download_date = now() where download_date = 0

trochia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Jan 2008

Permalink

Mar 2nd, 2009

Re: Export Data to CSV format, new data since last download

Click to Expand / Collapse Quote originally posted by trochia ...

As for below, will I also need to to add "time" somewhere into the table?

THX!!

no,
The table update after successfully downloading those records with a value of 0
will place a timestamp in the flag column
now() is the sql equivalent of php time() timestamps are numeric and represents the number of seconds from 1/1/1970
currently in the range of 123460000 (last month included the unix milestone 1234567890 o'clock)

using a timestamp is better than a text date time, its smaller in the database, faster, can be compared easier as to prior or later dates, and on output can be whatever date format needed.

Last edited by almostbob; Mar 2nd, 2009 at 11:15 am.

almostbob

Reputation Points: 562

Solved Threads: 368

Posting Maven

Offline

2,970 posts
since Jan 2009

Permalink

Mar 2nd, 2009

Re: Export Data to CSV format, new data since last download

Bob, again I thank-you...and yes, agree with the unix timestamp, just wasn't sure if it was automatically applied. I'm an "old school" programmer (from paper tape days), and Basic/C/C++ more literate (have forgotten more than I knew sometimes) and can do some simple php/mysql routines...and 'hack' my way thru others..

I am using the code located at the bottom of this thread ( I had posted earlier) http://www.daniweb.com/forums/thread115803.html

As of now, nothing I would suspect is 'flagged as 1'..(laugh), but I am confused on where to add the logic ( or how) into my existing script.

Sorry to be a pain, but I have searched a lot for doing this, and spend more time reading things that lead nowhere...lol... and my little pea brain at age 50 is over caffinated..

trochia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Jan 2008

Permalink

Mar 2nd, 2009

Re: Export Data to CSV format, new data since last download

line 22

php Syntax (Toggle Plain Text)
$values = mysql_query("SELECT * FROM ".$table." where download_date = 0");
$values = mysql_query("SELECT * FROM ".$table." where download_date = 0");

or
line 12

php Syntax (Toggle Plain Text)
$result = mysql_query("SHOW COLUMNS FROM ".$table."  where download_date = 0");
$result = mysql_query("SHOW COLUMNS FROM ".$table."  where download_date = 0");

or both, but I think line 22
not sure, not perfect in sql
test run it a few dozen times

the table update gets pushed into a php mysql statement
right before the exit; line 35

php Syntax (Toggle Plain Text)
mysql_query("UPDATE table SET download_date = NOW() WHERE download_date = 0");
exit;
?>
mysql_query("UPDATE table SET download_date = NOW() WHERE download_date = 0");
exit;
?>

that everything else is complete before the zeros are zapped

Last edited by almostbob; Mar 2nd, 2009 at 11:42 am.

almostbob

Reputation Points: 562

Solved Threads: 368

Posting Maven

Offline

2,970 posts
since Jan 2009

Permalink

Mar 4th, 2009

Re: Export Data to CSV format, new data since last download

Bob, again I thank-you for the reply...and I am very sorry about my late one. I haven't had a chance to go play with this yet, as I had put up a form that tagged into a DB...for 4 days,,and the guy was happier than hell, but his next in command...wasn't. (two days ago)

Thing is, it's just an e-mail form, w/some JS and the "little spinning Ajax"deal..that stays on the same page...and I'm totally lost and now trying to figure how (if I can) just had my post/db stuff, to their existing process...and it is ticking me off.

How's your js/ajax? The AJAX doesn't connect to anything, just stays on the same page for error stuff.. but it's 'greek' to me...

It's using MooTools, and I looked at it years ago, but never messed w/it.

I'd be happy to paypal you something...if you could direct me..

Jim

Last edited by trochia; Mar 4th, 2009 at 5:01 pm.

trochia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Jan 2008

Permalink

Mar 4th, 2009

Re: Export Data to CSV format, new data since last download

sorry, I have very little javacsript except for a form validation
have never got my attempted AJAX to crawl, let alone fly
I read the guides, and everything gets blurry

almostbob

Reputation Points: 562

Solved Threads: 368

Posting Maven

Offline

2,970 posts
since Jan 2009

Permalink

Apr 14th, 2009

Re: Export Data to CSV format, new data since last download

Hope you and your family had a Happy Easter Bob, and I am so sorry about my late reply (and thank's for your help). I had read thru it the day after your post and did a mock up and started on it, but I ran into a couple of problems and got flustered as to how I was doing this.

Then? I got sidetracked as my step mother fell, (she's 83) and my dad who is 75 who has a manufacturing business had to stay home with her and I ended up baby sitting his business for a few weeks.

Again, I am sorry about the late reply and sure appreciate your help, but after further thinking (now that my head is somewhat back on what I am trying to do) is that I really need to make this more of a universal script (or more intense for lack of better words)for the long run. (I think)

What I have is a shopping cart that is based on osCommerce originally

The cart db has (4) tables where it keeps track of customers, addressSTUFF and then the "basket" of which contains data of someone who didn't buy (complete purchase yet) and "orders".

The mentioned XLS export script which works fine...but myself, I am having a trouble with some JOINS trying to even get 2 to jive, let alone the 4 I want.

So, thinking deeper... Because the frigging cart code is already there, instead of messing up its present functioning, is to just create a whole seperate table (let us say export)... then create a routine and "pull" what I need from the (4) tables and place them into a table I create export

Am I making any sense? (laugh)... What it boils down to, if I can explain this properly in text...and convey it...is because this cart keeps the (4) tables, I would almost have to go in and add the downloadFlag to all tables... (I think)

My thoughts? Well, to do the script which "pulls" and adds to or appends the export table I will create...and just keep track of it uniquely.

Again, my thank's to date for your help...and sorry about my late reply... I'm not the type that just asks for help, and never to be seen again!!

Jim

trochia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Jan 2008

Permalink

May 20th, 2009

Re: Export Data to CSV format, new data since last download

Bob ??

You still out here?

Thx...Jim

trochia

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

12 posts
since Jan 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: access gmail

Next Thread in PHP Forum Timeline: php/mysql syntax to search for 3 user options

DaniWeb Message

Cancel Changes

User Name
Password