Copy selected information from multiple tables without using a lot of queries?

Question

Hi, i'm using php and mysql for a webpage, and i want to reuse information which i have in three database tables, similar to a 'save as' operation.

The tables are related as a 1-many and then 1-many, a tree structure.

I have so far implemented it by flatten out the tree structure with php (similar to one giant table) and storing it in an array. Then looped over every row, and even every part of the row, and for each one inserted into the corresponding table with a query. It sort of works, but with a lot of code to see if every post even has the information from the third table (it could be empty).

Now, my question is, could this be implemented in a more efficient way? There could be a lot of queries to the database this way.

The problem here seems to be how to relate the new posts. I have looked at 'SELECT INTO', but as i need to relate the new keys, i don't see how it could work. Also, mysql_insert_id only give me the latest id, not all of them.

Answer 1

The usually fastest and most elegant method to duplicate a row that I know of is:

Duplicating the row:

INSERT INTO tablename SELECT * FROM tablename WHERE id = 1;

querying the ID of the new record:

SELECT LAST_INSERT_ID() AS lastID;  

Storing that ID in a PHP variable (not shown here) and then updating the key in dependent tables:

UPDATE related_table_name SET key = '$lastID'

(id needs to be an auto-increment column for this to work, and LAST_INSERT_ID() should be used immediately after the insert.)

Reference:

• LAST_INSERT_ID()

• INSERT ... SELECT