Increment varchar values on duplicate entry

Question

I know that varchar type of columns are not incrementable, but I want to do something like this:

When a duplicated value is inserted, the new one will be tagged with a number at the end.

For example, we have post-name on our column in the database. Another value post-name is entered, so the next is going to be post-name-2, followed by post-name-3.

I have programmed something in php but its not very convenient.

        $post_url_ = $post_url." %";
        $stmt_check1 = $this->conn->prepare("SELECT * FROM post WHERE post_url LIKE :post_url ");
        $stmt_check2 = $this->conn->prepare("SELECT * FROM post WHERE post_url = :post_url ");

        $stmt_check1->bindparam(":post_url",$post_url_);
        $stmt_check2->bindparam(":post_url",$post_url);

        $stmt_check1->execute();
        $stmt_check2->execute();

        $rows1 = $stmt_check1->rowCount();
        $rows2 = $stmt_check2->rowCount();

        if($rows1<=0 && $rows2==1) {
        $repeat_no = $rows1+1; 
        $post_url = $post_url."-$repeat_no";
        }
        if($rows1>0){
        $repeat_no = $rows1+1; 
        $post_url = $post_url."-$repeat_no";


        }   

Answer 1

Instead of trying to create a complicate process to keep the correct name, just add separated field version

Then for UI proporse just concatenate both

 SELECT CONCAT(post , '-', version)

Answer 2

You can use do...while loop to check next numbers. It isn't performance problem, becuase you will use that only on save new record.

1. Before loop, create counter (initial by 0).
2. In loop - if counter > 0, add number to end of url
3. Increase counter
4. Check if url exists in database (while results from db > 0)