Consult if SLUG already exists

Asked

Viewed 339 times

4

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..)

  • show how you tried to select

  • $Slug = $_POST["Slug"]; $Slug = Slug($title, '-'); $query = mysqli_query($connect, "SELECT * FROM content WHERE Slug LIKE '$Slug%'"); $listSlugs = mysqli_fetch_array($query); &#if($Slug == $line && $listSlugs >0){ echo found Slug; // here I want to get $Slug to get +1 if there is an identity in the bank // ex: if there is a test title, it is titrated-test1, titrated-test2 } Else { // continues the code

  • puts that in the question.. edit the question..

  • Pronto Daniel Omine!

  • I started doing it and I was about to post an answer. But then I realized it’s not that simple. If you have patience, another day I will post something more concise. I thought it was simple, but it is not so simple, according to the conditions you presented. I did something with an SQL statement similar to Lucas' answer, instead of LIKE I traded for =, but that’s just what I realized is a mistake because that way you’ll never find an existing increment. That’s just the most complex part.

  • I was able to make a comparison as follows: if($Slug == 'test') { echo "Slug $Slug already exists"; } Else { //continues the code.. } The real problem was when I tried to compare with the existing slugs in the database..

  • Exactly.. Hehe this will give a job and requires time. I do not have that time now, but I liked the challenge. If no one else posts before, I’ll try to post some solution. But don’t expect me to deliver the answer ready because I can’t compromise and promise anything. I thought of something like, check the registration date. The logic would also be to filter by the date of the most recent. If you have no registration date or some other field where we can verify that is the newest record, will complicate.

  • Can’t you only query if the value of the $Slug string already exists in the Slug column in my database? I posted the code on the Pastebin, take a look if you can. http://pastebin.com/vdr3mtzw

  • does not roll.. the thing is complex. You can reduce the complexity of a solution by adding a field that identifies the "age" of the record. The logic is to be able to identify the most recent record. I suggest an auto increment numeric id field or a date field (date_created). Particularly would use date. Hands in the Dough! Try to solve your work.

  • I thought it was the same thing as a registration system, which if the user already exists it does not store.. in my case I just want, if there is, increment a number at the end.

  • Incrementing a number on a first occurrence is simple. The problem is if you need a second increment. Example of Slug "Slug-test". Suppose it already exists, then the new one would look like "Slug-teste1". Now suppose you try to register "Slug-test" again. How will you know if there is no "Slug-teste1" anymore? And the thing goes, "Slug-teste2", "Slug-teste100", "Slug-teste2000". Get it? The business model of a user registration is quite different from the business logic you want. Maybe you need to change the logic of the business or do what I suggested or find some other solution. Anyway.

  • Do you have any different idea of the increment in each Slug? If there’s anything that makes it easier for me, some term at the end, I don’t know, something like that.. am open to suggestions!

  • First of all, I don’t like the idea of increment or the technique of using slung as the identifier of a record. You could use Unique Numeric Id. Thus, you would eliminate this problem with slung. With Numeric ID as identifier, Urls would have to look like site.com/artigo/1/titulo-de-teste. When there is a name exactly like an existing one, it would not conflict the URL due to the ID: site.com/artigo/2/titulo-de-teste

  • another widely used technique is to differentiate the Urls by the registration date. It would be something like this site.com/artigo/20151207/titulo-de-teste. When an equal title appears, but on a different day , it would look like this site.com/artigo/20160201/titulo-de-teste. Get it? The logic of this is that you will hardly be registered for the same title on the same day. But it depends a lot on your business model. Finally, there are N solutions. Here in the comments I provided several tips and I think it’s enough for you to turn around. After all this is your job.

  • Anyway, thanks for helping. The chances of a Lug duplication will be minimal. However, it is always good to be prepared and so I decided to post the doubt, I just did not imagine that it was so complex.

Show 10 more comments

1 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.

  • Sorry ignorance, but do not know if you noticed that I asked for help to convert the field SLUG(TEXT) to UNIQUE, because when I try it always gives error..

  • I’m sorry too, I answered without understanding what I needed. If what you need is the generation of Slug from the number of tuples that have the same substring in the table, then I will edit my answer. Ex: a user registers a post with the title "Ways to Expand Your Online Business" (the Slug would be "ways-to-expand-your-business-online"), and after a while, the same or other user decide to create a post with the same title, which would result in Slug "ways-to-expand-your-business-online-1". Correct?

  • Perfectly correct! However, I no longer know how to turn the Slug(text) field into UNIQUE, much less how to know if Slug already exists in the SLUG column of my database. Have any way to get in touch?

  • If you need help with this problem: [email protected].

Browser other questions tagged

You are not signed in. Login or sign up in order to post.