Consult if SLUG already exists

Question

I would like to know how I do to query if a SLUG already exists in the database and, if it exists, how to increment +1 at the end of the string, getting:

site.com/artigo/titulo-de-teste1    
site.com/artigo/titulo-de-teste2    
site.com/artigo/titulo-de-teste3

I tried using SELECT:

$slug = $_POST["slug"];
 $slug = slug($titulo, '-');
 $consulta = mysqli_query($conecta, "SELECT * FROM conteudo WHERE slug LIKE '$slug%'"); 

$listaSlugs = mysqli_fetch_array($consulta);
 if($slug == $linha && $listaSlugs >0){ 
echo 'encontrou slug'; 
// aqui eu quero fazer com que $slug fique com +1 caso haja algum identico no banco 
// ex: se encontrar titulo-teste, fique titulo-teste1, titulo-teste2 } 
else { // continua o codigo.. com INSERT} 

I read something about creating the field like Unic and I tried several ways, but I couldn’t, because I don’t know what the field type should be like (VARCHAR, TEXT, etc..)

Answer 1

First, to maintain database consistency, you could add the UNIQUE restructure to the 'table.Slug' column, so it would be impossible to have two equal slugs in this table. Now the other solution is to check if the Slug to be inserted already exists, to prevent an exception from being triggered. At the moment I remember two:

1 - Select the first Slug in the table that matches Slug is trying to insert:

SELECT slug FROM table WHERE slug = $slug LIMIT 1

If the empty result means that you can enter the Slug.

2 - Add a Try-catch block to capture the exception if it occurs. If it occurs, you will return a notice to the user that the title already exists and that it should change it.

A hint: In the example you mentioned in the question you added the $Slug variable directly in the query, it can cause you in an SQL Injection vulnerability, so use Prepared Statements to avoid this problem, at least in the most common cases, level 1 and 2.

@update

First let’s start by converting all the initials of each word of the title to uppercase, by standardization, since the user can type all the minuscule. A function for that would be:

function title($str)
{
    return mb_convert_case($str, MB_CASE_TITLE, 'UTF-8');
}

User informs the title "learn how to promote your company on social networks". The return of the function will be "Learn How to Publicize Your Company On Social Networks".

For Slug, we can use the following function:

Function Slug($title, $separator = '-') { $title = toAscii($title);

    $flip = $separator == '-' ? '_' : '-';

    $title = preg_replace('!['.preg_quote($flip).']+!u', $separator, $title);

    $title = preg_replace('![^'.preg_quote($separator).'\pL\pN\s]+!u', '', mb_strtolower($title));

    $title = preg_replace('!['.preg_quote($separator).'\s]+!u', $separator, $title);

    return trim($title, $separator);

}

Passing the title to the Slug function, we get the following string:

"learn-a-disclose-your-business-on-social-networks"

The complete file of the required functions can be found here and they were removed from here. I just shifted the paradigm from object-oriented to procedural, which is what you’re using, at least that’s what I think.

Now we need to know how many results for this Slug exists in table X of the Y database, referring to column Slug.

SELECT COUNT(*) as num FROM tabela WHERE slug LIKE '%aprenda-a-divulgar-sua-empresa-nas-redes-sociais%'

With this number in hand, we can change the Slug to be inserted in the table:

$slug = $slug . '-' . $num;

"learn-a-disclose-your-business-on-social-networks-1"

Then we enter it into the database.

Another way, much more adopted, is to add a number based on date and time or another numbering that ensures the uniqueness of Slug.

Ex:

$slug = strrev(date('dmYHis')).$slug;

Also make unique in the table the combination of Slug with record creation date, so equal slugs would be allowed. And as for the URL, what do I do to differentiate them?

www.mysite.com/posts/{Slug}/{id}

www.mysite.com/posts/learn-how-to-publicize-your-company-on-social-media/1547 www.mysite.com/posts/learn-how-to-disclose-your-company-on-social-media/1550

olhadigital.uol.com.br/noticia/o-facebook-pretende-livrar-seu-feed-de-noticias-de-virais-que-Voce-não-quer-ver/53511

or

www.mysite.com/posts/{id}-{Slug}

www.mysite.com/posts/1547-learn-your-business-on-social-media www.mysite.com/posts/1550-learn-to-publicize-your-company-on-social-media

In the latter case you would need a function to separate Slug from id, using id to rescue the data in the database.