creating unique page title slugs php

Question

I have a function for creating unique slug for a page title. It checks if the slug is available in the pages table then creates a unique slug by adding a '-int' accordingly. The function works fine for the first three entries eg for 'test slug' entered three time will create 'test-slug-1', 'test-slug-2' and 'test-slug-3'. Then after that I get an error "Fatal error: Maximum execution time of 30 seconds exceeded" for the fourth entry. There should be some problem with the logic, can anyone help me find it please.Below is the code:

function createSlug($title, $table_name, $field_name) {

global $db_connect;

$slug = preg_replace("/-$/","",preg_replace('/[^a-z0-9]+/i', "-", strtolower($title)));

$counter = 1;

do{

  $query = "SELECT * FROM $table_name WHERE  $field_name  = '".$slug."'";
  $result = mysqli_query($db_connect, $query) or die(mysqli_error($db_connect));


  if(mysqli_num_rows($result) > 0){
      $count = strrchr($slug , "-"); 
      $count = str_replace("-", "", $count);
      if($count > 0){

          $length = count($count) + 1;
          $newSlug = str_replace(strrchr($slug , "-"), '',$slug);
          $slug = $newSlug.'-'.$length;

          $count++;

      }else{
          $slug = $slug.'-'.$counter;
      }  

  }

  $counter++; 
  $row = mysqli_fetch_assoc($result);

}while(mysqli_num_rows($result) > 0);

return $slug;

}

Answer 1

Just hit the database once, grab everything at once, chances are that's the biggest bottleneck.

$query = "SELECT * FROM $table_name WHERE  $field_name  LIKE '".$slug."%'";

Then put your results in an array (let's say $slugs)

//we only bother doing this if there is a conflicting slug already
if(mysqli_num_rows($result) !== 0 && in_array($slug, $slugs)){
    $max = 0;

    //keep incrementing $max until a space is found
    while(in_array( ($slug . '-' . ++$max ), $slugs) );

    //update $slug with the appendage
    $slug .= '-' . $max;
}

We use the in_array() checks as if the slug was my-slug the LIKE would also return rows such as

my-slug-is-awesome
my-slug-is-awesome-1
my-slug-rules

etc which would cause issues, the in_array() checks ensure that we are only checking against the exact slug that was entered.

Why don't we just count the results and +1?

This is because if you had multiple results, and deleted a few, your next slug could well conflict.

E.g.

my-slug
my-slug-2
my-slug-3
my-slug-4
my-slug-5

Delete -3 and -5 leaves us with

my-slug
my-slug-2
my-slug-4

So, that gives us 3 results, the next insert would be my-slug-4 which already exists.

Why don't we just use ORDER BY and LIMIT 1?

We can't just do an order by in the query because ^{the lack of} natural sorting would make my-slug-10 rank lower than my-slug-4 as it compares character by character and 4 is higher than 1

E.g.

m = m
y = y
- = -
s = s
l = l
u = u
g = g
- = -
4 > 1 !!!
  < 0 (But the previous number was higher, so from here onwards is not compared)

Answer 2

Just use a single query to do all the heavy lifting for you...

$slug = preg_replace("/-$/","",preg_replace('/[^a-z0-9]+/i', "-", strtolower($title)));

$query = "SELECT COUNT(*) AS NumHits FROM $table_name WHERE  $field_name  LIKE '$slug%'";
$result = mysqli_query($db_connect, $query) or die(mysqli_error($db_connect));
$row = $result->fetch_assoc();
$numHits = $row['NumHits'];

return ($numHits > 0) ? ($slug . '-' . $numHits) : $slug;

Answer 3

You can just select slug with the biggest number and increase it with 1:

$query = "SELECT $field_name FROM $table_name WHERE  $field_name LIKE '".$slug."-[0-9]*' ORDER BY $field_name DESC LIMIT 1";

[0-9]* in query means any count of numbers.

This query will select row with $slug at start and the bigest number.

After that you can parse result get number and increase it.

In this case you will have only one query and lot of unused performance.

UPDATE

This will not work, because slug-8 will be "bigger" than slug-11. But no idea how to fix it. maybe ORDER BYidDESC ?

UPDATE 2

Query can be ordered by length too and it will work right. Thanks to Jack:

$query = "SELECT $field_name FROM $table_name WHERE  $field_name LIKE '".$slug."-[0-9]*' ORDER BY LENGTH($field_name), $field_name DESC LIMIT 1";

UPDATE 3

Also added check for original slug. Thanks to Hailwood.

$query = "SELECT $field_name FROM $table_name WHERE $field_name = '".$slug."' OR $field_name LIKE '".$slug."-[0-9]*' ORDER BY LENGTH($field_name), $field_name DESC LIMIT 1";

Answer 4

Why don't you just create a slug and leave the rest of the job that involves indexing to MySQL. Here is a slugify function ( it is a slightly modified version used by Symfony framework ).

function slugify( $text ) {
    $text = preg_replace('~[^\\pL\d]+~u', '-', $text);  
    $text = trim($text, '-');
    $text = iconv('utf-8', 'ASCII//IGNORE//TRANSLIT', $text);   
    $text = strtolower(trim($text));
    $text = preg_replace('~[^-\w]+~', '', $text);

    return empty($text) ? substr( md5( time() ), 0, 8 ) : $text;
}

And the MySQL part can be solved with trigger ( change the table and column names ).

BEGIN
     declare original_slug varchar(255);
     declare slug_counter int;
     set original_slug = new.slug;
     set slug_counter = 1;
     while exists (select true from post where slug = new.slug) do
        set new.slug = concat(original_slug, '-', slug_counter); 
        set slug_counter = slug_counter + 1;
     end while;
END

MySQL Insert row, on duplicate: add suffix and re-insert