Mysql Query for unique slugs

Question

I have made a wordpress plugin to find and replace keywords in database, where admin provides keywords to find and to replace, and then the MYSQL query does the changes in DB.
But all I want to know the way to avoid the duplicate slugs.
For example :
As if there are two posts with following slugs:

firstpost
secondpost

and then admin runs the query and wants to replace "second" with "first". then the result will be:

update wp_posts set `post_name` = replace(post_name, 'second','first') where post_type='post'

firstpost
firstpost

Is there a way to keep the slugs unique ?

By far the simplest solution will be adding a unique constraint to the database column and catching the error thrown if a duplicate is found. — sjdaws
– sjdaws, Commented Jun 9, 2017 at 12:19
As an addition to @sjdaws :When there is a duplicate entry for a unique constraint, you will get a specific error code, if i remember correctly 1062. Put the query inside a try-catch block and inside the catch you can then check if the error code equals 1062. — Tobias F.
– Tobias F., Commented Jun 9, 2017 at 12:24
This is a messy solution in my opinion, and it doesn't really achieve what's been requested - which is to find a way to enable both the keyword swap and preserve uniqueness in the slugs. See my solution below. — Simon Woolf
– Simon Woolf, Commented Jun 9, 2017 at 12:46

Simon Woolf · Accepted Answer · 2017-06-09 12:47:48Z

This update query works with a derived table to check if the change would create a duplicate slug. If it would, a number is appended to the existing slug for the would-be duplicate.

UPDATE wp_posts p, (SELECT REPLACE(post_name,'second', 'first') AS post_name , COUNT(post_name) AS num_dupes FROM wp_posts WHERE post_type = 'post' GROUP BY REPLACE(post_name, 'second', 'first')) d SET p.post_name = CONCAT(REPLACE(p.post_name, 'second', 'first'), CASE WHEN num_dupes > 1 THEN num_dupes - 1 ELSE '' END) WHERE p.post_type='post' AND p.post_name = d.post_name

In my opinion this is a better solution than using a unique constraint on the post_name field because a) you don't need to make custom DML changes to the wordpress database and b) you prevent the issue of a duplicate occurring, and of having to handle the error, before it happens.

Thanks @Simon for you answer. Working exactly what I was looking for.

Collectives™ on Stack Overflow

Mysql Query for unique slugs

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related