See if SLUG already exists

4

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

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 as unique and tried it in several ways, but I could not, because I do not know what the field type should look like (VARCHAR, TEXT, etc.) >     

asked by anonymous 06.12.2015 / 17:56

1 answer

1

First, to maintain database consistency, you could add the UNIQUE constraint 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 thrown. At the moment I remember two:

1 - Select the first slug in the table that matches the slug you are trying to insert:

SELECT slug FROM table WHERE slug = $slug LIMIT 1

If the result is empty, it means that you can enter the slug.

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

A tip: In the example you mentioned on the page you added the $ slug variable directly in the query, you can cause it to be a SQL Injection vulnerability, so use Prepared Statements to avoid this problem, at least in the most common cases , level 1 and 2.

@update

First of all let's start by converting all the initials of each title word to upper case, by standardization, since the user can type everything mini-circle. One function for this would be:

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

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

For the 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-to-disclose-your-company-in-social-networks"

The full file of required functions can be found here and they have been removed from here . I just turned the paradigm, from object-oriented to procedural, which is what you are using, at least that's what I think.

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

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 into the table:

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

"learn-to-disclose-your-company-in-social-networking-1"

Then we insert it into the database.

Another way, much more adopted, is to add a number based on the date and time or other numbering that guarantees the uniqueness of the slug.

Ex:

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

Also make unique the table the combination of the slug with date of creation of the registry, in this way slugs equal would be allowed. What about the URL, what do I do to make them different?

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

www.mysite.com/posts/aprenda-a-divulgar-your-company-networks/1547 www.mysite.com/posts/aprenda-a-divulgar-your-company-in-social-reports/1550

olhardigital.uol.com.br/noticia/o-facebook-pretende-livrar-seu-feed-de-novas-de-virais-que-voce-no-ver-ver/53511

or

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

www.mysite.com/posts/1547-append-to-disclose-your-company-in-social-reports www.mysite.com/posts/1550-aprenda-a-divulgar-your-company-in-social-reports

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

    
06.12.2015 / 18:39