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

Answer 5

For the one part I would create an object that is dealing with the part creating the slug and handling the number:

// generate new slug:
$slug = new NumberedSlug('Creating Unique Page Title Slugs in PHP');
echo $slug, "\n", $slug->increase(), "\n";

// read existing slug:
$slug = new NumberedSlug('creating-unique-page-title-slugs-in-php-44');
echo $slug->getNumber(), "\n";

Output:

creating-unique-page-title-slugs-in-php
creating-unique-page-title-slugs-in-php-1
44

For the other part, the database, this already greatly simplifies your code (please double check, I've done this quick). Also see how you can benefit from the Mysqli object you actually have (but not use as is):

function createSlug($title, $table_name, $field_name, Mysqli $mysqli = NULL)
{
    $mysqli || $mysqli = $GLOBALS['db_connect'];

    $slug = new NumberedSlug($title);

    do
    {
        $query = "SELECT 1 FROM $table_name WHERE  $field_name  = '" . $slug . "'";

        if (!$result = $mysqli->query($query)) {
            throw new RuntimeException(var_export($mysqli->error_list, true));
        }

        if ($result->num_rows) {
            $slug->increase();
        }

    } while ($result->num_rows);

    return $slug;
}

But as others have already written you should first get all slugs that are numbered at once from the database and then pick a unique one if necessary. This will reduce the number of database calls. Also the code is much more compact:

function createSlug2($title, $table_name, $field_name, Mysqli $mysqli = NULL)
{
    $mysqli || $mysqli = $GLOBALS['db_connect'];

    $slug = new NumberedSlug($title);

    $query = "SELECT $field_name FROM $table_name WHERE $field_name LIKE '$slug-_%'";

    if (!$result = $mysqli->query($query)) {
        throw new RuntimeException(var_export($mysqli->error_list, true));
    }

    $existing = array_flip(call_user_func_array('array_merge', $result->fetch_all()));

    $slug->increase();

    while (isset($existing[$slug])) 
    {
        $slug->increase();
    }

    return $slug;
}

See it in action.

Answer 6

I wasn't fully satisfied with the answers, so I came up with a slightly different approach.

(SELECT CONCAT({$slug}, '-', counter) FROM (
  SELECT (@row_number:=@row_number + 1) AS counter, ev.*
  FROM (
    SELECT REPLACE(slug, {$slug}-, '') AS remainder
    FROM products, (SELECT @row_number:=0) AS t
    WHERE slug LIKE '{$slug}%'
  ) ev
  ORDER BY LENGTH(remainder), remainder
) sr
WHERE counter <> remainder)
LIMIT 1

What this basically does, it checks all the existing values in the DB that are similar to the new slug, and matches it against the row number to check for gaps, and if none is found, it uses the maximum number that's generated by the first identical slug that gets pushed to the end (note: we replace slug- and not slug)

Answer 7

$query = "SELECT * FROM $table_name WHERE  $field_name  LIKE '".$slug."%'";
$result = mysqli_query($db_connect, $query) or die(mysqli_error($db_connect));
//EDITED BASED ON COMMENT SUGGESTIONS
//create array of all matching slug names currently in database
$slugs = array();
while($row = $result->fetch_row()) {
    $slugs[] = $row['field_name'];
}
//test if slug is in database, append - '1,2,..n' until available slug is found
if(in_array($slug, $slugs)){
    $count = 1;
    do{
       $testSlug = $slug . '-' . $count;
       $count++;
    } while(in_array($testSlug, $slugs));
    $slug = $testSlug;
}
//insert slug 

You should be able to do this in a single database call with the LIKE keyword that will reduce your execution time.

Answer 8

You could use the Fbeen/UniqueSlugBundle. This Bundle is lightweight and does what it needs to do.