URL Alias uniqueness with PHP & MySQL

: Category: Personal Home Page; 06 May 2019; Hits: 7718

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:

When I save a record in JoomlaCMS or WordpressCMS
It creates a unique name to use in URLs
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:

copy raw

/----------|-------------------|----------------------\
| id       | name              | url_alias            |
|----------|-------------------|----------------------|
| 1        | My *First* Test   | my_first_test        |
|          |                   |                      |
|          |                   |                      |
\----------|-------------------|----------------------/

/----------|-------------------|----------------------\
| id | name | url_alias |
|----------|-------------------|----------------------|
| 1 | My *First* Test | my_first_test |
| | | |
| | | |
\----------|-------------------|----------------------/

What we want:

copy raw

/----------|-------------------|----------------------\
| 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      |
\----------|-------------------|----------------------/

/----------|-------------------|----------------------\
| 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:

copy raw

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;
}

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:

copy raw

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 ;

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):

Category: Personal Home Page :: Article: 677

URL Alias uniqueness with PHP & MySQL

Add comment

Credit where Credit is Due:

Latest Articles

Accreditation

Joes Word Cloud