MYSQL: Friendly URLs sql query

Question

I want to make a query using which I can check that URL which I am making for a new video entry is not present in my db.

My current query is:

SELECT Count(videoid) FROM video WHERE titleurl = 'test';

I am storing count in a variable $n Then checking it using the following PHP code

if ($n > 0){
 return $output . "-$n";
}else{
 return $output;
}

But above query is creating a problem. Suppose

• 1st user submitted a video with name Test so the url will be http://example.com/video/test/
• 2nd user submitted a video with name Test so the url will be http://example.com/video/test-1/ because one entry with titleurl test is already present, so I have added 1 in it which will be test-1
• 3rd user also added a video entry with name test but this time accoridng to my method url will be test-1 which is wrong.

I want to solve this problem, if test is already present then it should be test-1 and if another user creating a entry with name test then url should be test-2 because test and test-1 are already there.

Please give some method with which I can solve this issue.

Answer 1

friendly urls are nice, but you should always add the objects id as well, or store a unique string in the database. Check the urls here on stackoverflow, for example.

EDIT I seriously doubt that google is ok with short numbers but dislikes long numbers. Here is a solution using numbers only where you must: Add another column holding the 'id' value of each test-video and create a unique index on that and titleurl:

ALTER TABLE video ADD COLUMN titleurl_id UNSIGNED TINYINT NOT NULL;
CREATE UNIQUE INDEX uidx_url ON video (titleurl, titleurl_id);

When creating a video, you need to add that value:

$amount = query("SELECT COUNT(*) FROM video WHERE titleurl='test'");
query("INSERT INTO video(titleurl, titleurl_id) VALUES ('test', $amount + 1)");

You will need to pay attention to the result of your insert query, you might run into race conditions if you do it this way (that's why ids are more convenient in the url). Creating a URL:

$urlpart = query("
    SELECT IF(
        titleurl_id = 0,
        titleurl,
        CONCAT_WS('-', titleurl, titleurl_id)
    ) AS url FROM video");

Answer 2

My first idea is to select all urls that starts with test ... WHERE url LIKE 'test%' and check then if test exists - if yes, add -1 and again check if test-1 exists. If yes, increase counter to -2..

Answer 3

Try creating two fields - one for the original url, and one for your 'calculated' url.

$c = SELECT COUNT(*) FROM table WHERE original_url = 'test'
UPDATE table SET url='test-$c' WHERE id=...