On insert, how can I change the duplicate name to something else in the database?

Question

I am having a challenge working with multiple documents, particularly dealing with duplicate filenames in the database. I am trying to create a system in which an admin can add HTML documents. He will input two fields: name of the document and its source code.

When adding a document, the name (e.g. "A Page") and the filename (based on the name e.g. "a_page.html") will be inserted to the database and a file with the source code inside will be created on the server.

The problem now is what happens if I try to add another document with the same name e.g. "A Page". I would like the filename to be "a_page_2.html" to not overwrite the existing one. And if another with the same name, "a_page_3.html" and so on.

Is there a simple SQL statement for achieving?

In MySQL there's the ON DUPLICATE KEY syntax. You might want to look into that — GordonM, Feb 17 '13 at 09:26
http://stackoverflow.com/questions/4702059/insert-contacts-into-database-but-does-not-want-to-duplicate-already-existing-co — Mithun Satheesh, Feb 17 '13 at 09:36
Oh yes, I've looked at that. However, if there is a duplicate, I am trying to update the new record not the old record. — Mikey, Feb 17 '13 at 13:54

Your Common Sense · Accepted Answer · 2013-02-17T09:47:28.740

1

No, there is no simple statement. There could be a_page_2.html already.

So, just make your document names already unique:

insert new record in the database
get unique id
create the file using this id, by putting together given name and id

When selecting document name, you could do just

SELECT concat(name,'_',id) as name ...

It is similar to the way Stackoverflow creates question urls:

/questions/14919687/on-insert-how-can-i-change-the-duplicate-name-to-something-else-in-the-database/
            ^ id    ^ conventional name that can have 1000s duplicates

You can also get rid of conventional names at all, leaving ids only:
http://stackoverflow.com/questions/14919687/ still works

edited Feb 17 '13 at 09:47

answered Feb 17 '13 at 09:38

Your Common Sense

156,878
40
214
345

But then he'll get stuff like fileA_151235 instead of fileA_2 – Roy Feb 17 '13 at 09:43
Hm... that is an interesting solution. Unfortunately for me, I am moving files from an old system to a new system having names already as "a_page.html", so I would need to rename them all. I would have to write a script to rename all files. – Mikey Feb 17 '13 at 14:11

score -1 · Answer 2 · answered Feb 17 '13 at 10:40

Make the file name column UNIQUE
try to insert and if you got a duplicate key error, repeat with added number until successful

With PDO it could look like that:

class QueryException extends RuntimeException { }
class DuplicateKeyException extends QueryException { }

function executeInsert(PDOStatement $stmt) {
    try {
        $stmt->execute();
    } catch (PDOException $e) {
        switch ($e->errorInfo[1]) {
            case 1062:
                throw new DuplicateKeyException($e->getMessage(), null, $e);
            default:
                throw new QueryException($e->getMessage(), null, $e);
        }
    }
}

$filename = 'a_page';
$contents = '<title>test</title>';

$stmt = $pdo->prepare('INSERT INTO documents (filename,contents) VALUES (:filename,:contents)');
$repeat = true; $counter = 0;
while ($repeat)
    try {
        $stmt->bindValue(
            ':filename',
            $filename . ($counter > 0 ? ("-$counter" : '') . '.html'
        );
        $stmt->bindValue(':contents', $contents);
        executeInsert($stmt);
        $repeat = false;
    } catch (DuplicateKeyException $e) {
        $repeat = true;
        $counter++;
    } catch (QueryException $e) {
        $repeat = false;
        // error handling here
    }
}

I was actually thinking about something along those lines, but what happens if I have a filename that is called "a_page_10"? That means I would need to make 11 database calls and I don't think that would be too efficient. Chances are though I'll probably never get that many duplicates but anything can happen. — Mikey, Feb 17 '13 at 14:06
The alternative would be to make a `LIKE` query for the filename first to check for the smallest available number. If you expect that there are duplicates on almost every insert, you *might* consider that. But otherwise I would prefer a solution that is very fast >99% of the time and reasonable slower in special cases. — Fabian Schmengler, Feb 17 '13 at 15:58

On insert, how can I change the duplicate name to something else in the database?

2 Answers2