URL Alias uniqueness with PHP & MySQL

What?
So this is an article for me on how to copy Joomla's and Wordpress' feature where a Title/Name value is converted to a search-engine friendly URL alias and is unique in the database. Ok that's a long sentence; let me try this:
  1. When I save a record in JoomlaCMS or WordpressCMS
  2. It creates a unique name to use in URLs
  3. I want that

Why?
These are used as inputs to server-side scripts for the sake of search-engine friendliness. There are no silver bullets here but I want to block any character that isn't a letter, a number or an underscore.

How?
Suppose the following exists as a MySQL database table called my_table_name:

What we have:
/----------|-------------------|----------------------\
| id       | name              | url_alias            |
|----------|-------------------|----------------------|
| 1        | My *First* Test   | my_first_test        |
|          |                   |                      |
|          |                   |                      |
\----------|-------------------|----------------------/
What we want:
/----------|-------------------|----------------------\
| id       | name              | url_alias            |
|----------|-------------------|----------------------|
| 1        | My *First* Test   | my_first_test        |
| 2        | My *First* Test   | my_first_test_1      |
| 3        | My _-_First Test  | my_first_test_2      |
\----------|-------------------|----------------------/

the PHP
$p_Name here is the title of your article or product that will be changed into a string of only letters, numbers and underscores. Note that the PHP doesn't need a database connection here:
function getUrlAlias($p_Name){

    // lowercase and trim preceding/trailing spaces
    $v_Output = strtolower(trim($p_Name));

    // replace all non alphanumeric characters (except underscore) to an underscore
    $v_Output = preg_replace("/[^a-z0-9\_]/", "_",$v_Output);

    // replace any consecutive underscores to a single one (eg. "my_____test" yields "my_test")
    $v_Output = preg_replace("#((\_){2})\\2+#", "$2", trim($v_Output, "_"));

    // returns a string
    return $v_Output;
}
and the MySQL
is a trigger which executes before the record is inserted and increments the value of url_alias:
DROP TRIGGER IF EXISTS incrementUrlAlias;
DELIMITER |
CREATE TRIGGER incrementUrlAlias BEFORE INSERT ON my_table_name
FOR EACH ROW BEGIN
    declare original_url varchar(255);
    declare url_counter int;
    set original_url = new.url_alias;
    set url_counter = 1;
    while exists (select true from my_table_name where url_alias = new.url_alias) do
        set new.url_alias = concat(original_url, '_', url_counter);
        set url_counter = url_counter + 1;
    end while;
END;
|
DELIMITER ;
Note that running this in PhpMyAdmin might show as an error, possibly due to the delimiters, but you can run it anyway.

Additional Note(s):
The trigger only runs if inserting a record. If your script is trying to modify/update a record with a value that already exists in the table, you will get an error in which case you could probably add another trigger like above but with BEFORE UPDATE ON my_table_name.

Source(s):

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.