creating unique page title slugs php

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)

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;

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";