Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
#1 2006-12-14 15:51:40
- Ace of Dubs
- Member
- Registered: 2006-04-17
- Posts: 446
Find articles with duplicate URL-titles
I have a project with multiple assistent doing data entry. Many times they ignore the error for duplicate URL titles (I’ve had to fire a couple) In the meantime I have this huge mess to clean up.
Any recommendations for finding articles with these dupes?
Right now I am showing URL titles with PHP globals and going over each entry…its driving me crazy
Offline
Re: Find articles with duplicate URL-titles
You can go into phpMyAdmin and browse the Textpattern table, then click the url_title field heading to sort by it.
This will at least let you see what is duplicated, then you can change it from there.
We Love TXP . TXP Themes . TXP Tags . TXP Planet . TXP Make
Offline
#3 2006-12-15 15:35:34
- Mary
- Sock Enthusiast
- Registered: 2004-06-27
- Posts: 6,236
Re: Find articles with duplicate URL-titles
If you run the following query, it will tell you which url_titles are duplicated and how many there are of the duplicate:
select url_title, count(url_title) from textpattern group by url_title having count(url_title) > 1;Then you can run a query like the following to find the article ID#s for each duplicate set:
select id from textpattern where url_title = 'the_url_title';Depending upon the number of dups, it’ll still be several queries to run, but it’s easier than looking through every row of your table.
Offline
#4 2006-12-15 17:59:22
- Ace of Dubs
- Member
- Registered: 2006-04-17
- Posts: 446
Re: Find articles with duplicate URL-titles
Never have two lines of code savedme so much aggravation
Thanks a bunch Mary
:)
Offline
Re: Find articles with duplicate URL-titles
Yep, been lurking on this one… Thanks much
Offline
#6 2006-12-16 18:12:43
- Mary
- Sock Enthusiast
- Registered: 2004-06-27
- Posts: 6,236
Re: Find articles with duplicate URL-titles
If this is something that happens often (or you expect to recur later), you might consider creating yourself a plugin to do it.
I worked out the way to do it with just two queries:
CREATE TEMPORARY TABLE textpattern_tmp
SELECT url_title, COUNT(url_title) AS count FROM textpattern GROUP BY url_title HAVING COUNT(url_title) > 1;
SELECT t1.ID, t2.url_title, t2.count FROM textpattern t1 INNER JOIN textpattern_tmp t2 ON t1.url_title = t2.url_title;Offline
Re: Find articles with duplicate URL-titles
How about just one query ….
.
SELECT DISTINCT a.id, a.url_title
FROM `textpattern` AS a, `textpattern` AS b
WHERE a.url_title = b.url_title AND a.id != b.id
ORDER BY a.url_title ASC , a.id ASCLast edited by dbulli (2006-12-16 20:20:48)
nuff-respec ::: dannyb
Offline
#8 2006-12-16 21:04:22
- Mary
- Sock Enthusiast
- Registered: 2004-06-27
- Posts: 6,236
Re: Find articles with duplicate URL-titles
You don’t need the distinct restriction (because of the where clause used), but yeah that works if you don’t particularly care about the count.
Offline
Re: Find articles with duplicate URL-titles
Mary wrote:
You don’t need the distinct restriction (because of the where clause used), but yeah that works if you don’t particularly care about the count.
Sorry, but actually you do need the DISTINCT as as the same id will repeat as may times as it has a duplicate … and if you don’t have the a.id != b.id it will match itself. I can’t imagine count matters as much, if you have the it sorted by title and id, but you can always modify the sql to give you count too…
.
SELECT a.id, count(b.id) as dups, a.title, a.url_title
FROM `textpattern` AS a, `textpattern` AS b
WHERE a.url_title = b.url_title
GROUP BY a.id
HAVING dups > 1
ORDER BY a.url_title ASC , a.id ASCnuff-respec ::: dannyb
Offline
Re: Find articles with duplicate URL-titles
I love it, dueling queries :)
Offline
Re: Find articles with duplicate URL-titles
mrdale wrote:
I love it, dueling queries :)
=) … I am not terribly comfy with SQL, but it’s an amazing language … such a limited number of commands, I think of it just like TxP (without plugins) … how to use what you got to make it work … and so many ways to skin a cat …
nuff-respec is still plugin free -)
nuff-respec ::: dannyb
Offline
Re: Find articles with duplicate URL-titles
Quite frankly, maybe the better alternative would be to switch to a url-scheme where duplicate url-titles are not an issue – there’s a couple of those.
Offline