Unique Profile Slug with PHP and PDO

Question

I am using a class to generate a string name profile to slug and next use an SQL command to tell me whats the unique value to use in insert command, the problem is the command isn't working properly, sometimes it is possible to return a value which already exist...

Thats the class I am using to generate the slug: (composer require channaveer/slug)

And this the example code:

use Channaveer\Slug\Slug;

$string = "john doe";
$slug = Slug::create($string);

$profile_count_stmt = $pdo->prepare("
                    SELECT
                        COUNT(`id`) slug_count
                    FROM
                        `advogados_e_escritorios`
                    WHERE
                        `slug_perfil` LIKE :slug
                ");

$profile_count_stmt->execute([
    ":slug" => "%".$slug."%"
]);

$profile_count = $profile_count_stmt->fetchObject();

if ($profile_count && $profile_count->slug_count > 0) {
    $profile_increment = $profile_count->slug_count + 1;
    $slug = $slug . '-' . $profile_increment;
}

echo 'Your unique slug: '. $slug;
// Your unique slug: john-doe-5

This is the content of the table when the script run:

Do you know how can I improve the select command to prevent it to return existing slugs from DB?

this is a horrible approach, which is never used in reality. you should never rely on slugs to query the table. Use id — Your Common Sense, Jan 29 '22 at 15:54
there is no good way to handle concurency, the causes are multiple. you can try a transactional approach where you lock the table, but ion hig traffic this will get you many deadlocks — nbk, Jan 29 '22 at 16:54
Hello, Your Commom Sense, thanks for your comment, well how do you advise me to do? what is the best way to create unique slugs for the users? — Sophie, Jan 29 '22 at 17:36
Hey, nbk, thanks for your comment, hm... but how can I do to create unique slugs for the users? using their names as string — Sophie, Jan 29 '22 at 17:37
What have you tried to resolve the problem? "sometimes it is possible to return a value which already exist" - what does that mean? The screenshot shows only unique values. Also, why don't you check for the exact slug in the loop instead of just counting up? — Nico Haase, Jan 31 '22 at 05:49
**what's wrong** with a "unique slug" used for the users **right here** on Stack Overflow? What's wrong with `https://stackoverflow.com/users/16684555/sophie` which is either unique and always refer to the same user even if username changed? What's wrong with using both the user id (16684555) and a username (sophie)? — Your Common Sense, Jan 31 '22 at 06:03

score 0 · Answer 1 · answered Jan 30 '22 at 15:17

Ok finally found a solution... Heres the code for who wants to generate unique profile slugs using PHP - PDO and MySQL

    $string = "John Doe";
    $string = mb_strtolower(preg_replace('/\s+/', '-', $string));
    $slug = iconv('UTF-8', 'ASCII//TRANSLIT', $string);


    $pdo = Conectar();

    $sql = "
    SELECT slug_perfil 
    FROM advogados_e_escritorios 
    WHERE slug_perfil 
    LIKE '$slug%'
    ";

    $statement = $pdo->prepare($sql);
    if($statement->execute())
    {
        $total_row = $statement->rowCount();

        if($total_row > 0)
        {
            $result = $statement->fetchAll();
            foreach($result as $row)
            {
                $data[] = $row['slug_perfil'];
            }

            if(in_array($slug, $data))
            {
                $count = 0;
                while( in_array( ($slug . '-' . ++$count ), $data) );
                $slug = $slug . '-' . $count;
            }
        }
    }

    echo $slug;
    //john-doe-1

Look up at the URL of this page. There is the solution – Your Common Sense Jan 30 '22 at 15:22 — Your Common Sense, Jan 30 '22 at 15:22

Channaveer Hakari · Answer 2 · 2022-01-31T06:46:17.237

0

You should check if the slug exists or not from your database. If it already exists then you can append some random string like the following

$slug = Slug::create($string);

$slugExists = "DB query to check if the slug exists in your database then you may return the count of rows";

//If the count of rows is more than 0, then add some random string

if($slugExists) {
/** NOTE: you can use primary key - id to append after the slug, but that has to be done after you create the user record. This will help you to achieve the concurrency problem as @YourCommenSense was stating.   */
    $slug = $slug.time(); //time() function will return time in number of seconds
}

//DB query to insert into database

I have followed the same for my blog articles (StackCoder) too. Even LinkedIn follows the same fashion.

Following is screenshot from LinkedIn URL

edited Jan 31 '22 at 06:46

answered Jan 31 '22 at 05:44

Channaveer Hakari

2,769
3
34
45

Linkedin, as well as Stack Overflow, doesn't follow this "some random string" approach, simply because it makes no sense to use a "random" string when a post already has a truly unique identifier. That won't change even if the post title changes. – Your Common Sense Jan 31 '22 at 06:00
@YourCommonSense also if you have seen any blog like dev, freecodecamp, medium. Don't you think that 2 or more articles will have the same title and you cant enforce the writers/authors to look for the unique title because you want to make that title a slug? So they add these kind of unique strings at the end of the slug. – Channaveer Hakari Jan 31 '22 at 06:32
@YourCommonSense As far as StackOverflow they might have resolved this issue by adding the unique id before the slug ie 70906354/unique-profile-slug-with-php-and-pdo Hope you understand what I actually meant to convey my message. – Channaveer Hakari Jan 31 '22 at 06:33
"time() function will return time in number of seconds". That's a "technical lead" of our time. A person who have no idea of concurrency issues – Your Common Sense Jan 31 '22 at 06:38
@YourCommonSense I was just giving an idea over there. If they need they can add the primary key column as a string to append or some kind of UUID. – Channaveer Hakari Jan 31 '22 at 06:43
@YourCommonSense I know the difference between those. I was giving various ways to achieve it, didn't mean to compare them. – Channaveer Hakari Jan 31 '22 at 06:57
Nope you don't. That's the problem with you guys. Even being told you are wrong, you just deny that. "append some **random string** like the following **number of seconds**" is your own words. Means you have no idea that the number of seconds is ANYTHING but random. You have no idea that any random value shouldn't be used in the concurrent environment. You are still suggesting in your answer that random and unique is the same thing. – Your Common Sense Jan 31 '22 at 07:03

Unique Profile Slug with PHP and PDO

2 Answers2

Linked