CODEWITHSUNDEEP
phpmysql
Abdul Sadik Yalcin
Abdul Sadik Yalcin

Reputation: 1822

Generate unique url slug with PHP

I'm developing a small CMS system. Currently brain dead while trying to generate a unique url. I generate the url from the title of the page. Got a nice script to make this happen but I can't resolve the issue of duplicates.

Similar question here but getting the exact output.

I manage to make the following;

this-is-the-slug
this-is-the-slug-2

But if I create the same post the third time, it would just duplicate: this-is-the-slug-2

$i = 1;
$baseurl = $url;
//$check database here
if($thereisamatch){
$url = $baseurl . "-" . $i++;  
}

I can't get around it, would be thankful for assistance.

if(isset($_POST['save'])) {
    $title = mysqli_real_escape_string($db, $_POST['title']);
    $url = toAscii($title);
    // check url
    $content = $_POST['content'];
    $author = $_SESSION['thelab_username'];
    $type = $_POST['type'];
    $showtitle = $_POST['showtitle'];
    $saveas = $_POST['status'];

    $insert = $connection->query(" INSERT INTO lab_pages (title, permalink, content, author, status, type, showtitle)
    VALUES ('$title', '$newslug', '$content', '$author', '$saveas', '$type', '$showtitle') ");

    if($insert === TRUE) {
        echo '<div id="success">Page added. <button id="close">Close</button></div>';
    }

    else {
        echo '<div id="error">Failed. Try again. <button id="failed">Close</button></div>';
        printf("Errormessage: %s\n", $db->error);
    }
}

function toAscii($str, $replace=array(), $delimiter='-') {
 if( !empty($replace) ) {
  $str = str_replace((array)$replace, ' ', $str);
 }

 $clean = iconv('UTF-8', 'ASCII//TRANSLIT', $str);
 $clean = preg_replace("/[^a-zA-Z0-9\/_|+ -]/", '', $clean);
 $clean = strtolower(trim($clean, '-'));
 $clean = preg_replace("/[\/_|+ -]+/", $delimiter, $clean);

 return $clean;
}

Upvotes: 3

Views: 7380

Answers (5)

Jordan Harding
Jordan Harding

Reputation: 142

This code is insecure and not production ready! (sql injection)

function slug($str) {
    $str = strtolower(trim($str));
    $str = preg_replace('/[^a-z0-9-]/', '-', $str);
    $str = preg_replace('/-+/', "-", $str);
    return rtrim($str, '-');
}

function get_one_value($con,$query) {
    $result = mysqli_query($con,$query);
    $row = mysqli_fetch_row($result);
    return($row[0]);
}
$slug = slug($input_without_trailing_number);
$exists = get_one_value("select count(id) from table where slug = '$slug'"); 

if ($exists > 0)
{
    $new_number = $exists + 1;
    $newslug = $slug."-".$new_number;
}
echo $newslug;

Threw in my own slug function because I've found it infinitely useful and I hope others will too.

Upvotes: 3

Murtaza JAFARI
Murtaza JAFARI

Reputation: 734

Try this code, this worked for me.

// check if slug is already exist

$post_slug = preg_replace('/[^a-z0-9]+/i', '-', trim(strtolower($_POST['post_title'])));

$query = "SELECT * FROM `posts` WHERE post_slug = '$post_slug'";
$result = mysqli_query($conn, $query) or die(mysql_error());
$ifExist = mysqli_num_rows($result);
if ($ifExist >= 1) {

    $post_slug = $post_slug . '-' . $ifExist++;
} else {
    $post_slug = $post_slug;
}

Upvotes: 0

Victor Rusu
Victor Rusu

Reputation: 107

I had problems with permalinks too. Especially when I wanted to remove russian, chinese and any other characters than English from the url. Try the function bellow. I used PDO but it is not a big difference.

        function FilterPermalink($link){
            $db = new Connect;
            $link = strtolower(trim($link));
            $link = preg_replace('/[^a-z0-9-]/', '-', $link);
            $link = preg_replace('/-+/', "-", $link);
            $link = rtrim($link, '-');
            $link = preg_replace('/\s+/', '-', $link);
            if(strlen($link) > 30)
                $link = substr($link, 0, 30);
            $existing_lnk = $db->prepare("SELECT id FROM posts WHERE permalink = :permalink");
            $existing_lnk->execute(array('permalink' => $link));
            $num = $existing_lnk->fetchAll(PDO::FETCH_COLUMN);
            $first_total = count($num);
            for($i=0;$first_total != 0;$i++){
                if($i == 0){
                    $new_number = $first_total + 1;
                    $newlink = $link."-".$new_number;
                }
                $check_lnk = $db->prepare("SELECT id FROM posts WHERE permalink = :permalink");
                $check_lnk->execute(array('permalink' => $newlink));
                $other = $check_lnk->fetchAll(PDO::FETCH_COLUMN);
                $other_total = count($other);
                if($other_total != 0){
                    $first_total = $first_total + $other_total;
                    $new_number = $first_total;
                    $newlink = $link."-".$new_number;
                }elseif($other_total == 0){
                    $first_total = 0;
                }           
            }
            if($i > 0)
                return $newlink;
            else
                return $link;
        }

It works well for me.

Upvotes: 2

syck
syck

Reputation: 3029

Use the value of database matches (by SELECT COUNT(*)) and increase it to get a new postfix.

PHP scripts do not talk to each other, so $i will always be set to 1 at start and then be increased by 1, hence the 2.

Thanks for the source code. Therefore the beginning of your code should be like:

if(isset($_POST['save'])) {
    $title = mysqli_real_escape_string($db, $_POST['title']);
    $url = toAscii($title);
    // check url
    $content = $_POST['content'];
    $author = $_SESSION['thelab_username'];
    $type = $_POST['type'];
    $showtitle = $_POST['showtitle'];
    $saveas = $_POST['status']; // no modifications until here

    // count entries with permalink like our slug
    $urlmask = $url.'%';
    $sql = 'SELECT COUNT(*) FROM lab_pages WHERE permalink LIKE ?';
    $stst =  $db->stmt_init();
    $stst->bind_param('s', $urlmask);
    $stst->execute();
    $stst->store_result();
    $stst->bind_result($count);
    $stst->fetch();
    $stst->free_result();
    $newslug = sprintf('%s-%d', $url, $count+1);

    $insert = $connection->query(" INSERT INTO lab_pages (title, permalink, content, author, status, type, showtitle)
    VALUES ('$title', '$newslug', '$content', '$author', '$saveas', '$type', '$showtitle') ");
    :

The important thing is fetching the number of present slugs with an SQL statement like SELECT COUNT(*) FROM lab_pages WHERE permalink LIKE "$url%". Then you can increase it and create a new unique slug. The column permalink should be indexed due to performance.

But, I'd rather use uniqid() for stuff like that.

I used prepared statements for sake of demonstration, but you can of course run the query by the normal query-fetch_array-free_result sequence.

Upvotes: 0

Oleg Samorai
Oleg Samorai

Reputation: 390

You can resolve it by two ways.

  1. Move check database into function and use loop.

Like this:

function check_into_database($url){
    //if isset - return true else false
}
$i = 1; $baseurl = $url;
while(check_into_database($url)){
    $url = $baseurl . "-" . $i++;        
}

And second way

Use query with count(id) for check all urls.

Query should be like this:

SELECT COUNT(id) FROM tbl_name WHERE url LIKE "$url%"

This query will return count of urls which like this-is-the-slug%.

Upvotes: 0

Related Questions