CODEWITHSUNDEEP
phpmysqldatabase
spaantje
spaantje

Reputation: 58

Copy virtual folder structure to another user

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.

Upvotes: 1

Views: 80

Answers (2)

Marshall Davis
Marshall Davis

Reputation: 3650

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.

Upvotes: 0

Marshall Davis
Marshall Davis

Reputation: 3650

@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.

Upvotes: 1

Related Questions