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:
copyraw
/----------|-------------------|----------------------\
| id       | name              | url_alias            |
|----------|-------------------|----------------------|
| 1        | My *First* Test   | my_first_test        |
|          |                   |                      |
|          |                   |                      |
\----------|-------------------|----------------------/
  1.  /----------|-------------------|----------------------\ 
  2.  | id       | name              | url_alias            | 
  3.  |----------|-------------------|----------------------| 
  4.  | 1        | My *First* Test   | my_first_test        | 
  5.  |          |                   |                      | 
  6.  |          |                   |                      | 
  7.  \----------|-------------------|----------------------/ 
What we want:
copyraw
/----------|-------------------|----------------------\
| 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      |
\----------|-------------------|----------------------/
  1.  /----------|-------------------|----------------------\ 
  2.  | id       | name              | url_alias            | 
  3.  |----------|-------------------|----------------------| 
  4.  | 1        | My *First* Test   | my_first_test        | 
  5.  | 2        | My *First* Test   | my_first_test_1      | 
  6.  | 3        | My _-_First Test  | my_first_test_2      | 
  7.  \----------|-------------------|----------------------/ 

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:
copyraw
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;
}
  1.  function getUrlAlias($p_Name){ 
  2.   
  3.      // lowercase and trim preceding/trailing spaces 
  4.      $v_Output = strtolower(trim($p_Name))
  5.   
  6.      // replace all non alphanumeric characters (except underscore) to an underscore 
  7.      $v_Output = preg_replace("/[^a-z0-9\_]/", "_",$v_Output)
  8.   
  9.      // replace any consecutive underscores to a single one (eg. "my_____test" yields "my_test") 
  10.      $v_Output = preg_replace("#((\_){2})\\2+#", "$2", trim($v_Output, "_"))
  11.   
  12.      // returns a string 
  13.      return $v_Output
  14.  } 
and the MySQL
is a trigger which executes before the record is inserted and increments the value of url_alias:
copyraw
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 ;
  1.  DROP TRIGGER if EXISTS incrementUrlAlias; 
  2.  DELIMITER | 
  3.  CREATE TRIGGER incrementUrlAlias BEFORE INSERT ON my_table_name 
  4.  for EACH ROW BEGIN 
  5.      declare original_url varchar(255)
  6.      declare url_counter int; 
  7.      set original_url = new.url_alias; 
  8.      set url_counter = 1
  9.      while exists (select true from my_table_name where url_alias = new.url_alias) do 
  10.          set new.url_alias = concat(original_url, '_', url_counter)
  11.          set url_counter = url_counter + 1
  12.      end while; 
  13.  END; 
  14.  | 
  15.  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):
Category: Personal Home Page :: Article: 677

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

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.