In message <002101c6adc5$e8ca1c40$6401a8c0@inosh>, Kong Jin Jie <jinjie@xxxxxxxxxxxxxxxxx> writes >Hi guys, I have a client which wants a system that does updating of their >customers in bulk at the end of their office hour everyday. They suggested that >they don't wish to do updating online and prefer doing updating in the xls file. > >So basically, they will upload the whole xls file at the end of their office >hour with all the details of their customers and will only update the record in >our MySQL server in which any of the records that was modified. > >My idea was to delete the whole table when the administrator upload the new xls >file and insert them row by row. I think this will take alot of processing of >the xls file and transaction to the MySQL server. > >Is there any way to do this with a faster method that doesn't take a long time >to update. It's best if there's a logic that can do update of the server on any >rows that was modified on the xls file. > >I'll be using PHP and MySQL to work on this project. I have written a script to change the csv output from the spreadsheet into a .sql file which inserts the data into a temp file. The temp file has the same fields as the main file (in your case, customers), but also has a boolean field, done, defaulting to 0. Then $qry="UPDATE customers INNER JOIN temp ON reference=id SET customers.this=that [etc], temp.done=1"; $qry="INSERT INTO customers( [stuff] ) SELECT temp.info FROM temp WHERE NOT temp.done"; -- Pete Clark Free advertising for your website, business, or organisation at: http://www.hotcosta.com/resources.php ------------------------ Yahoo! Groups Sponsor --------------------~--> Yahoo! Groups gets a make over. See the new email design. http://us.click.yahoo.com/XISQkA/lOaOAA/yQLSAA/saFolB/TM --------------------------------------------------------------------~-> PHP Data object relational mapping generator http://www.metastorage.net/ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/php-objects/ <*> To unsubscribe from this group, send an email to: php-objects-unsubscribe@xxxxxxxxxxxxxxx <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
