MySQL Stored Function to Create a Slug

MySQL slug function on insert

Are you looking for something like this?

CREATE TABLE people
(`name` varchar(128), `slug` varchar(128));

-- It's not a real function it's just an oversimplified example
-- you need to implement your own logic     
CREATE FUNCTION NAME_SLUG(name VARCHAR(128))
RETURNS VARCHAR(128)
  RETURN LOWER(REPLACE(name, ' ', '-'));

CREATE TRIGGER tg_people_insert
BEFORE INSERT ON people
FOR EACH ROW
  SET NEW.slug = NAME_SLUG(NEW.name);

INSERT INTO people (`name`)
VALUES ('Jhon Doe'),('Ian Martin Louis'), ('Mark Lee');

SELECT * FROM people;

Output:

|             NAME |             SLUG |
---------------------------------------
|         Jhon Doe |         jhon-doe |
| Ian Martin Louis | ian-martin-louis |
|         Mark Lee |         mark-lee |

Here is SQLFiddle demo

Easy way of generating a slug name column from the name column?

You can certainly do a string replace using MySQL. The official documentation lists quite a few string functions you might find useful.

SELECT REPLACE('Foo Bar!~~~~', '~', '');
SELECT LOWER('Foo Bar!');

I also ran across this blog post on using regular expressions in MySQL.

Updated: Details from the blog post I mentioned:

So what I would recommend is creating a function for doing a regular expression replace:

DELIMITER $$
FUNCTION `regex_replace`(pattern varchar(1000),replacement varchar(1000),original varchar(1000))
RETURNS varchar(1000)
DETERMINISTIC
BEGIN
DECLARE temp VARCHAR(1000);
DECLARE ch VARCHAR(1);
DECLARE i INT;
SET i = 1;
SET temp = original;
IF original REGEXP pattern THEN
    SET temp = "";
    loop_label: LOOP
    IF i>CHAR_LENGTH(original) THEN
        LEAVE loop_label;
    END IF;
    SET ch = SUBSTRING(original,i,1);
    IF NOT ch REGEXP pattern THEN
        SET temp = CONCAT(temp,ch);
    ELSE
        SET temp = CONCAT(temp,replacement);
    END IF;
    SET i=i+1;
END LOOP;
END IF;
RETURN temp;
END$$
DELIMITER ;

Then something akin to the following

SELECT regex_replace('[^a-zA-Z0-9]+', '', '%$&?/’|test><+-,][)(' )

If you're not comfortable with that approach, you can always just run some update calls using replace

update db_products set Slug_Name = replace(Name, '~', '');

How to rewrite this php slugify function to mysql?

Fixed my function, removed regex_replace usage and instead used function from here: mySQL Stored Function to create a slug

Added transliteration tweak, final code is here. transliteration function doesn't change:

    DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `slugify`(`dirty_string` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE x, y , z Int;
    Declare temp_string, allowed_chars, new_string VarChar(255);
    Declare is_allowed Bool;
    Declare c, check_char VarChar(1);

    set allowed_chars = "abcdefghijklmnopqrstuvwxyz0123456789-";
    set temp_string = transliterate(dirty_string);
    set temp_string = lower(temp_string);

    Select temp_string Regexp('&') Into x;
    If x = 1 Then
        Set temp_string = replace(temp_string, '&', ' and ');
    End If;

    Select temp_string Regexp('[^a-z0-9]+') into x;
    If x = 1 then
        set z = 1;
        While z <= Char_length(temp_string) Do
            Set c = Substring(temp_string, z, 1);
            Set is_allowed = False;
            Set y = 1;
            Inner_Check: While y <= Char_length(allowed_chars) Do
                If (strCmp(ascii(Substring(allowed_chars,y,1)), Ascii(c)) = 0) Then
                    Set is_allowed = True;
                    Leave Inner_Check;
                End If;
                Set y = y + 1;
            End While;
            If is_allowed = False Then
                Set temp_string = Replace(temp_string, c, '-');
            End If;

            set z = z + 1;
        End While;
    End If;

    Select temp_string Regexp("^-|-$|'") into x;
    If x = 1 Then
        Set temp_string = Replace(temp_string, "'", '');
        Set z = Char_length(temp_string);
        Set y = Char_length(temp_string);
        Dash_check: While z > 1 Do
            If Strcmp(SubString(temp_string, -1, 1), '-') = 0 Then
                Set temp_string = Substring(temp_string,1, y-1);
                Set y = y - 1;
            Else
                Leave Dash_check;
            End If;
            Set z = z - 1;
        End While;
    End If;

    Repeat
        Select temp_string Regexp("--") into x;
        If x = 1 Then
            Set temp_string = Replace(temp_string, "--", "-");
        End If;
    Until x <> 1 End Repeat;

    If LOCATE('-', temp_string) = 1 Then
        Set temp_string = SUBSTRING(temp_string, 2);
    End If;

    Return temp_string;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `transliterate`(original VARCHAR(512)) RETURNS varchar(512) CHARSET utf8
BEGIN

  DECLARE translit VARCHAR(512) DEFAULT '';
  DECLARE len      INT(3)       DEFAULT 0;
  DECLARE pos      INT(3)       DEFAULT 1;
  DECLARE letter   CHAR(1);
  DECLARE is_lower BIT;

  SET len = CHAR_LENGTH(original);

  WHILE (pos <= len) DO
    SET letter   = SUBSTRING(original, pos, 1);
    SET is_lower = IF(LCASE(letter) COLLATE utf8_bin = letter COLLATE utf8_bin, 1, 0);

    CASE TRUE
      WHEN letter = 'a' THEN SET letter = IF(is_lower, 'a', 'A');
      WHEN letter = 'b' THEN SET letter = IF(is_lower, 'b', 'B');
      WHEN letter = 'c' THEN SET letter = IF(is_lower, 'c', 'C');
      WHEN letter = 'd' THEN SET letter = IF(is_lower, 'd', 'D');
      WHEN letter = 'e' THEN SET letter = IF(is_lower, 'e', 'E');
      WHEN letter = 'f' THEN SET letter = IF(is_lower, 'f', 'F');
      WHEN letter = 'g' THEN SET letter = IF(is_lower, 'g', 'G');
      WHEN letter = 'h' THEN SET letter = IF(is_lower, 'h', 'H');
      WHEN letter = 'i' THEN SET letter = IF(is_lower, 'i', 'I');
      WHEN letter = 'j' THEN SET letter = IF(is_lower, 'j', 'J');
      WHEN letter = 'k' THEN SET letter = IF(is_lower, 'k', 'K');
      WHEN letter = 'l' THEN SET letter = IF(is_lower, 'l', 'L');
      WHEN letter = 'ł' THEN SET letter = IF(is_lower, 'l', 'L');
      WHEN letter = 'm' THEN SET letter = IF(is_lower, 'm', 'M');
      WHEN letter = 'n' THEN SET letter = IF(is_lower, 'n', 'N');
      WHEN letter = 'o' THEN SET letter = IF(is_lower, 'o', 'O');
      WHEN letter = 'p' THEN SET letter = IF(is_lower, 'p', 'P');
      WHEN letter = 'q' THEN SET letter = IF(is_lower, 'q', 'Q');
      WHEN letter = 'r' THEN SET letter = IF(is_lower, 'r', 'R');
      WHEN letter = 's' THEN SET letter = IF(is_lower, 's', 'S');
      WHEN letter = 't' THEN SET letter = IF(is_lower, 't', 'T');
      WHEN letter = 'u' THEN SET letter = IF(is_lower, 'u', 'U');
      WHEN letter = 'v' THEN SET letter = IF(is_lower, 'v', 'V');
      WHEN letter = 'w' THEN SET letter = IF(is_lower, 'w', 'W');
      WHEN letter = 'x' THEN SET letter = IF(is_lower, 'x', 'X');
      WHEN letter = 'y' THEN SET letter = IF(is_lower, 'y', 'Y');
      WHEN letter = 'z' THEN SET letter = IF(is_lower, 'z', 'Z');
      ELSE
        SET letter = letter;
    END CASE;

    -- CONCAT seems to ignore the whitespace character. As a workaround we use
    -- CONCAT_WS with a whitespace separator when the letter is a whitespace.
    SET translit = CONCAT_WS(IF(letter = ' ', ' ', ''), translit, letter);
    SET pos = pos + 1;
  END WHILE;

  RETURN translit;

END$$

DELIMITER ;

P.S. Someone was asking on chat for test scenario. You need at least one table with 2x varchars(255) columns. And sentence from any book or text, with commas, dots, brackets, other identificational signs and etc. As the results only numbers, words, letters and single dashes has to remain. But when I started it was as result lower case word with spaces.

creating unique page title slugs php

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;

Create slug to include id and title when insert record - php MySQL

You should create a trigger, something like that:

CREATE TRIGGER trigger_name BEFORE INSERT ON table
FOR EACH ROW SET slug = CONCAT(NEW.title, "-", NEW.id);

I'm not sure you'll be able to access the ID column before its written on the database (unless, of course, you generate your IDs yourself, not using the autoincrement).

If you're using your DB's autoincrement (and you should be), try creating a trigger AFTER INSERT, and updating your row in your trigger. That way, even though you're updating it AFTER your insert, it'll still run before you can run any other queries (like a SELECT).

HERE is the documentation on triggers.

---

I was wrong. Apparently, you can't update a table you're inserting into (using an AFTER INSERT triger), it'll throw an exception. So, two possible ways to do it using SQL alone:

The ugly way:

DELIMITER $$
CREATE TRIGGER `create_slug` BEFORE INSERT
    ON `events`
    FOR EACH ROW
BEGIN
    SET new.id = (SELECT MAX(id) FROM events) + 1;
    SET new.slug = CONCAT(new.menu_name, "-", new.id);
END$$
DELIMITER ;

It overrides your database's AUTOINCREMENT. Really, don't do that.

Or, you can create a procedure:

DELIMITER $$

CREATE PROCEDURE insert_event(MenuName VARCHAR(30), Content TEXT, PhotoName TEXT)
BEGIN
    INSERT INTO events (menu_name, content, photo_name) VALUES (MenuName, Content, PhotoName);
    SET @id = LAST_INSERT_ID();
    UPDATE events SET slug = CONCAT(menu_name, "-", @id) WHERE id = @id;
END$$

DELIMITER ;

And, instead of calling (as you were calling):

$query = mysql_query("INSERT INTO events (menu_name, content, photo_name) VALUES ('{$menu_name}', '{$content}', '{$photo_name}');", $connection); 

just call

$result = mysql_query("CALL insert_event('{$menu_name}', '{$content}', '{$photo_name}');",$connection );

Again, I'll strongly advice against using mysql_query. It's outdated, discontinued and unsafe. You should check out mysqli or PDO.

when adding a title that stores in database, create slug automatically

Looks like you have the headline slug, which is what you want anyway. right? So let's just wrap that in our function create_url_slug()

create_url_slug(GetSQLValueString($_POST['headlineslug'], "text"))

Should be all you need.

Let's do it using PDO as the gentlemen above suggested!

try{
    $conn = new PDO('mysql:host=host;dbname=yourdatabasename', 'user', 'pass');

    $slug = create_url_slug($_POST['newsarticle']);

    //we'll use a prepared statement, which will sanitize our strings for us!
    $stmt = $conn->prepare('INSERT INTO news (articledate, newsheadline, headlineslug, newsarticle) VALUES (:articledate, :newsheadline, :headlineslug, :newsarticle)');
    $stmt->bindParam(':articledate', $_POST['articledate']);
    $stmt->bindParam(':newsheadline', $_POST['newsheadline']);
    $stmt->bindParam(':headlineslug', $_POST['headlineslug']);
    $stmt->bindParam(':newsarticle', $slug);

    $stmt->execute();

    echo 'Successfully saved article!';

} catch(PDOException $e){
    echo "There was an error: ".$e->getMessage();
    exit();
}

Resources

1. PDO connection
2. PDO Prepared Statements

---

Related Topics