A small MariaDB database with a simple table with just a couple of columns (id, name, `description` ) drives a PHP website. The table lacks a `slug` column.
You could still get away with spaces in an URL although it’s against the specs.
`”example.com/user/John Doe”` will work if you parse “John Doe” to a variable and do a search in your database.
select * from `people` where `name` like 'John Doe' limit 1
But you can’t copy the url, it will lose Doe.
Spitting out a better url with a slug/url PHP function should output something like `”example.com/user/john-doe”`
But then there is a problem in the database:
select * from `people` where `name` like 'john-doe' limit 1
doesn’t return anything.
So we need to add a `slug` column to the database, and why not try out a VIRTUAL column. A virtual column doesn’t exist in memory of disk, but is generated on the fly.
Can that work?
To sluggify a string in MariaDB, is a bit problematic, probably you should write you own custom function but I took the easy road. The REPLACE function which can only REPLACE one character a time, it doesn’t have the more powerful TRANSLATE function, but you can nest the REPLACE function so that should do the job.
Adding a virtual slug column to a Maria/MySQL database
Not really clean and concise but let’s try:
ALTER TABLE `users` add `slug` VARCHAR(50) AS (LOWER(REPLACE(REPLACE(REPLACE(`name`," ","-"),"(",""),")",""))) VIRTUAL;Filtering out spaces, and brackets.
Doesn’t work. Doesn’t even execute.
Got this error in PHPMyAdmin:
Error
Static analysis:
3 errors were found during analysis.
A new statement was found, but no delimiter between it and the previous one. (near "replace" at position 52)
Unrecognized alter operation. (near "," at position 68)
Unrecognized alter operation. (near "" at position 0)
Took me some time to find out was wrong.
Nothing!
It’s a bug in PHPMyAdmin!
Executing the same command in a MySQL console worked perfectly fine.
A search on the internet gave me this issue, guess my PHPMyAdmin is too old on my local server.
Anyhow for the moment I’m testing a virtual `slug` column instead of a real one. Should save some MB on expensive disk-space.