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:
copyraw
What we want:
/----------|-------------------|----------------------\ | id | name | url_alias | |----------|-------------------|----------------------| | 1 | My *First* Test | my_first_test | | | | | | | | | \----------|-------------------|----------------------/
- /----------|-------------------|----------------------\
- | id | name | url_alias |
- |----------|-------------------|----------------------|
- | 1 | My *First* Test | my_first_test |
- | | | |
- | | | |
- \----------|-------------------|----------------------/
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 | \----------|-------------------|----------------------/
- /----------|-------------------|----------------------\
- | 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:
copyraw
and the MySQLfunction 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;
- }
is a trigger which executes before the record is inserted and increments the value of url_alias:
copyraw
Note that running this in PhpMyAdmin might show as an error, possibly due to the delimiters, but you can run it anyway.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 ;
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):
- Online regex tester and debugger: PHP, PCRE, Python, Golang and JavaScript
- StackOverflow - MySQL Insert row, on duplicate: add suffix and re-insert
Category: Personal Home Page :: Article: 677
Add comment