PHP RSS PHP RSS

Export Data to CSV format, new data since last download

Reply

•
•
Join Date: Jan 2008
Posts: 11
Reputation: trochia is an unknown quantity at this point 
Solved Threads: 0
trochia trochia is offline Offline
Newbie Poster

Export Data to CSV format, new data since last download

 
0
  #1
Mar 2nd, 2009
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~~
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 1,352
Reputation: almostbob has a spectacular aura about almostbob has a spectacular aura about 
Solved Threads: 163
almostbob's Avatar
almostbob almostbob is offline Offline
Nearly a Posting Virtuoso

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

 
0
  #2
Mar 2nd, 2009
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) 
  1. /* dbconnect */
  2. SELECT * FROM table WHERE download_date = 0
  3. /* output to csv php script */
  4. /* if (csv file ok ) */
  5. UPDATE table SET download_date = NOW() WHERE download_date = 0
  6. /* 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.
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 11
Reputation: trochia is an unknown quantity at this point 
Solved Threads: 0
trochia trochia is offline Offline
Newbie Poster

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

 
0
  #3
Mar 2nd, 2009
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!!

•
•
•
•
Originally Posted by almostbob View Post
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) 
  1. SELECT * FROM table WHERE download_date = 0
and output to csv via your php script
mysql Syntax (Toggle Plain Text) 
  1. UPDATE table SET download_date = NOW() WHERE download_date = 0
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 1,352
Reputation: almostbob has a spectacular aura about almostbob has a spectacular aura about 
Solved Threads: 163
almostbob's Avatar
almostbob almostbob is offline Offline
Nearly a Posting Virtuoso

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

 
0
  #4
Mar 2nd, 2009
•
•
•
•
Originally Posted by trochia View Post
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.
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 11
Reputation: trochia is an unknown quantity at this point 
Solved Threads: 0
trochia trochia is offline Offline
Newbie Poster

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

 
0
  #5
Mar 2nd, 2009
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..
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 1,352
Reputation: almostbob has a spectacular aura about almostbob has a spectacular aura about 
Solved Threads: 163
almostbob's Avatar
almostbob almostbob is offline Offline
Nearly a Posting Virtuoso

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

 
0
  #6
Mar 2nd, 2009
line 22
php Syntax (Toggle Plain Text) 
  1. $values = mysql_query("SELECT * FROM ".$table." where download_date = 0");
or
line 12
php Syntax (Toggle Plain Text) 
  1. $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) 
  1. mysql_query("UPDATE table SET download_date = NOW() WHERE download_date = 0");
  2. exit;
  3. ?>
that everything else is complete before the zeros are zapped
Last edited by almostbob; Mar 2nd, 2009 at 11:42 am.
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 11
Reputation: trochia is an unknown quantity at this point 
Solved Threads: 0
trochia trochia is offline Offline
Newbie Poster

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

 
0
  #7
Mar 4th, 2009
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2009
Posts: 1,352
Reputation: almostbob has a spectacular aura about almostbob has a spectacular aura about 
Solved Threads: 163
almostbob's Avatar
almostbob almostbob is offline Offline
Nearly a Posting Virtuoso

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

 
0
  #8
Mar 4th, 2009
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
Failure is not an option It's included free, you don't have to do anything to get it
If at first you dont succeed, join the club
Of course its always in the last place you look, you dont keep looking after you find it

Please mark solved problems, solved
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 11
Reputation: trochia is an unknown quantity at this point 
Solved Threads: 0
trochia trochia is offline Offline
Newbie Poster

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

 
0
  #9
Apr 14th, 2009
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2008
Posts: 11
Reputation: trochia is an unknown quantity at this point 
Solved Threads: 0
trochia trochia is offline Offline
Newbie Poster

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

 
0
  #10
May 20th, 2009
Bob ??

You still out here?

Thx...Jim
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the PHP Forum
Thread Tools Search this Thread



ajax apache api array beginner binary body broken buttons cakephp checkbox class cms code cron curl database date date/time display dynamic ebooks echo email error file files folder form forms function functions google href htaccess html image include insert integration ip java javascript joomla limit link login loop mail mediawiki menu mlm msqli_multi_query multiple mysql number oop paypal pdf php phpincludeissue phpmyadmin problem query radio random recursion regex remote script search server sessions sms soap source sp space speed sql static subdomain syntax system table tag tutorial update upload url validator variable vbulletin video web websphere white wordpress xml youtube
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC