0

I recently transferred a huge custom database of about 100,000 articles to wordpress. Everything seemed to work. But recently while checking the site. I found this issue where the post is displayed but when clicked, it doesn't open. Somehow the link doesn't contain the post title. Although when I edit the post and update it, it works. Now the problem is that it is very time consuming to bulk update 100 posts each time. Is there another way?

I have tried to update the permalink settings but no use.

Any help will be highly appreciated.

  • Did the posts have (or get) valid URLs when you imported them? – Jos May 14 at 17:36
  • To be honest (and my sincere apologies), I do not understand the question. The transfer was majorly carried out via database transfer. I am not sure if I saw a field to insert post URL. – Byte IT Antwerpen May 14 at 17:39
  • (That is what I mean, yes.) Can you look in the database to see if these posts have URLs now? – Jos May 14 at 17:41
  • What table and column should I be looking? I just checked wp_posts and did not find any column that would suggests it is for URL – Byte IT Antwerpen May 14 at 17:45
  • That would be the column guid. – Jos May 14 at 17:51
0

As suggested by Jos the reason for this was that the post_name column in the wp_posts table was empty. To correct the said problem, the following steps were taken:

  1. Backup the database.
  2. Check for duplicates in the database. select * from (select count(*) as c, post_title from wp_posts where post_name = '' and post_type = 'post') as counts where c > 1;
  3. Update the table wp_posts set post_name = replace(post_title, ' ', '-');
  4. Be extra careful that your post_name column doesn't contain accented characters or characters that are not allowed in the urls.
  5. I then replaced most of the disallowed character in the post_name by running the following query (Absolute noob in sql):
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Š','S');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'š','s');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ð','Dj');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ž','Z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ž','z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'À','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Á','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Â','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ã','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ä','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Å','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Æ','A');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ç','C');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'È','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'É','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ê','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ë','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ì','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Í','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Î','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ï','I');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ñ','N');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ò','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ó','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ô','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Õ','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ö','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ø','O');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ù','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ú','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Û','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ü','U');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ý','Y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Þ','B');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ß','Ss');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'à','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'á','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'â','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ã','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ä','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'å','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'æ','a');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ç','c');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'è','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'é','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ê','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ë','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ì','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'í','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'î','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ï','i');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ð','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ñ','n');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ò','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ó','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ô','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'õ','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ö','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ø','o');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ù','u');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ú','u');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'û','u');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ý','y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ý','y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'þ','b');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ÿ','y');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ƒ','f');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'.','-');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'-','-');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'--','-');

UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ě','e');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ž','z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'š','s');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'č','c');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ř','r');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ď','d');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ť','t');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ň','n');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'ů','u');

UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ě','E');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ž','Z');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Š','S');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Č','C');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ř','R');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ď','D');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ť','T');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ň','N');
UPDATE `wp_posts` SET post_name = REPLACE(post_name,'Ů','U');
UPDATE `wp_posts` SET
    post_name = lower(name),
    post_name = replace(post_name, '.', '-'),
    post_name = replace(post_name, ',', '-'),
    post_name = replace(post_name, ';', '-'),
    post_name = replace(post_name, ':', '-'),
    post_name = replace(post_name, '?', '-'),
    post_name = replace(post_name, '%', '-'),
    post_name = replace(post_name, '&', '-'),
    post_name = replace(post_name, '#', '-'),
    post_name = replace(post_name, '*', '-'),
    post_name = replace(post_name, '!', '-'),
    post_name = replace(post_name, '_', '-'),
    post_name = replace(post_name, '@', '-'),
    post_name = replace(post_name, '+', '-'),
    post_name = replace(post_name, '(', '-'),
    post_name = replace(post_name, ')', '-'),
    post_name = replace(post_name, '[', '-'),
    post_name = replace(post_name, ']', '-'),
    post_name = replace(post_name, '/', '-'),
    post_name = replace(post_name, '-', '-'),
    post_name = replace(post_name, '\'', ''),
    post_name = trim(post_name),
    post_name = replace(post_name, '-', '-'),
    post_name = replace(post_name, '--', '-');

UPDATE `wp_posts` SET post_name = LOWER(post_name);
  1. Seems everything works.
  2. I also used the plugin 'Rebuild Permalinks' just to be sure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.