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

Add comment

Your rating:

Submit

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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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

Please publish modules in offcanvas position.