1

I have a upload script with virtual folder structure (so no actual structure) linked to a user. So i can make a different structure for every user.

This structure is (only) saved in the database. And files are linked in the database but are all saved in one folder. But now i want to copy a complete structure to another user. The structure can have infinite sub folders.

The database looks something like: 

 id   | user | parent | name
  1   |  6   |   0    | Folder 1
  2   |  6   |   0    | Folder 2
  3   |  6   |   1    | Sub of Folder 1
  4   |  6   |   1    | 2nd Sub of Folder 1
  5   |  6   |   2    | Sub of Folder 2
  6   |  6   |   3    | Sub of Sub of Folder 1
  7   |  6   |   6    | Sub of Sub of Sub of Folder 1

What is the best way to copy this to another user? Is it possible with just mysql?

(Keep in mind that id is auto increment index and parent refers to id) Hope someone can help.

spaantje
  • 58
  • 6
  • 1
    Use INSERT INTO SELECT (...) FROM maybe ? http://stackoverflow.com/questions/5253302/insert-into-select-for-all-mysql-columns – Maximus2012 Mar 31 '15 at 21:27

2 Answers2

1

@Maximus2012 is on to something.

INSERT INTO table (
  user,
  parent,
  name
)
SELECT (
  7 as user,
  parent,
  name
)
FROM table
WHERE user = 6;

What this does is select the given columns from the table and use the results to insert values into the table. Remember that you must specify the columns in the INSERT query and then in the same order for the SELECT subquery. The 7 as the user should obviously be the appropriate ID of the new user. However, that demonstrates how you would include a concrete value in the subquery.

Marshall Davis
  • 3,337
  • 5
  • 39
  • 47
  • But than the parent doesn't link to the new id of the folder? – spaantje Apr 01 '15 at 07:59
  • @spaantje Not sure I understand your question. You stated that the ID column had a default auto-increment. If the `id` column is omitted in the `INSERT` then a new `id` will be inserted by incrementing the highest existing `id` by `1`. This will duplicate all values from user `6`'s file structure into user `7`'s structure. – Marshall Davis Apr 01 '15 at 14:21
  • @spaantje I see now. I'll see what I can do. Does it have to be done in SQL? If each insert is done via a script, you could use the returned `id` from each insert. – Marshall Davis Apr 01 '15 at 14:29
  • See [this SQL FIddle](http://sqlfiddle.com/#!9/9ae16/1). I still have an issue with the Sub of Sub of Sub of Folder 1. – Marshall Davis Apr 01 '15 at 15:42
0

See this SQL Fiddle.

One caveat is that you may run into race conditions if the table is modified during the query. I am sadly not familiar enough with MySQL to ensure this doesn't happen. Look into either locking the table for writes while duplicating (this may happen on writes anyway) or determine if a transaction would make it safe.

The schema described there and the INSERT ... SELECT state does perform as requested and will lead to the method that works best for your full use case.

Marshall Davis
  • 3,337
  • 5
  • 39
  • 47