0

Hello this is my first time i post but hopefully i won't mess up to much.

Basically i'm trying to to copy two tables into a new table, the data in table 2 and 3 are temp data that i update with two csv files. It's just basic data that share the same ID so thats the Primary Key and i want these to be combined into a new table. This is supposed to be done just once a day handling about 2000 lines Below follows a better description of what i'm looking for.

  • 3 tables, Core, temp_data1, temp_data2
  • temp_data1 has id, name, product
  • temp_data2 has id, description
  • id is a unique since it's the product_nr of the product
  • First copy the data from temp_data1 to Core. Insert new line if the product does not exist, if it do exist it should update the row with the information
  • Next update Core with the description where id=id and do not insert if id do not exist (it should not exist)

I'm looking for something that can be done in one push of a button, first i upload the csv file into the two different databases (two different files) next i push a button to merge the two tables to the Core one. I know you can do this right away with the two csv files and skip the two tables but i feel like that is so over my head it's not even funny.

I can handle programming php it's all the mysql stuff that's messing with my head.

Hopefully you guys can help me and in return i will help out any other place i can.

Thanks in advance.

Tman
  • 146
  • 2
  • 10

1 Answers1

0

I'm not sure I understand it correctly, but this can be done using only sql script, using INSERT INTO...SELECT...ON DUPLICATE KEY UPDATE... - see http://dev.mysql.com/doc/refman/5.6/en/insert-select.html

sebapalus
  • 536
  • 3
  • 6
  • Just by looking at this code it seems to be correct but does it update the whole row or just the one cell? – Tman Nov 05 '13 at 09:59
  • that depends on how your insert statement looks, see the manual page and syntax - by using `ON DUPLICATE` you can specify what should be updated – sebapalus Nov 05 '13 at 10:38
  • I will check back and update this thread if it's successful. Thank you for you help and point me in the right direction – Tman Nov 05 '13 at 12:08
  • Now i have one more thing that is strange, i have roughtly 1800 lines of database information that should be moved from table 1 to table 2, but it only processes 1540 of the lines, but when i reload the page it processes the rest. Any idea why it is like this? – Tman Nov 06 '13 at 09:23
  • Does the SQL query alone work correctly when run directly on the database? – sebapalus Nov 06 '13 at 09:25
  • This is the code im using for the update, the other one works now and update all rows but this one seems to time out since it does not return the # of rows affected, it just leaves a blank page `code``code` – Tman Nov 06 '13 at 10:22
  • you shouldn't do such queries in a loop, it's very perf-intensive. See http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query - it can be done in a single SQL query – sebapalus Nov 06 '13 at 10:52
  • Yes i noticed this and i have made a change but it does not update this is the code `code`mysql_query("UPDATE `tcc_core` JOIN `tmp_ac_prevent` ON `tmp_ac_prevent.st` = `tcc_core.serviceticket` SET `tcc_core.autoclosure` = `tmp_ac_prevent.age`");`code` Basically i want to copy the cell age from tmp_ac_prevent to the cell autoclosure in tcc_core where `tmp_ac_prevent.st` = `tcc_core.serviceticket`. But nothing happends. Any idea why? – Tman Nov 06 '13 at 11:37
  • your sql query is ok. have you tried running this query directly on the database? maybe you have type conflict between autoclosure and age columns? or you have more than one record in tmp_ac_precent for the same serviceticket? It's hard to tell based on just a short code sample. Generally: test your queries directly on the databse (phpmyadmin, mysql workbench) before adding to your code. This seems like a new problem unrelated to the current question thread. – sebapalus Nov 06 '13 at 11:47
  • I know what i did wrong not, it was the ticks ` around the tcc_core.autoclosure that messed it up. It works flawless now and thank you for you patients and the awesome pointers you gave me on how to easly troubleshoot the code. Thanks again – Tman Nov 06 '13 at 12:07