WordPress - How to quickly create rewrite rules for all posts

This post describes a quick way to create .htaccess rewrite rules after permalinks change from /%post_id% to /%year%/%monthnum%/%day%/%postname%/

Using phpMyAdmin execute sql code:

SELECT id AS ID, date_format( post_date, "%Y" ) AS Y, date_format( post_date, "%c" ) AS M, date_format( post_date, "%d" ) AS D, post_name AS name
FROM `wp_posts`
WHERE post_status = "publish"
ORDER BY `wp_posts`.`id` ASC 

Export results to CSV file with columns separated by ; character and without header in the first row.

$ head wp_posts.csv 
12;2010;11;29;how-to-access-cli-on-linksys-srw
42;2010;11;29;how-to-use-imagemagick-effects-with-coppermine
99;2010;11;30;having-problems-with-some-ie-only-web-interface-using-firefox
100;2010;12;01;nginx-proxy-and-real-ip-address
101;2010;12;01;nginx-and-gandi-ssl-certificate
102;2010;12;02;https-everywhere
107;2010;12;03;ruby-and-rmagick-quote-with-polaroid-like-effect
108;2010;12;04;zen-photo-polaroid-like-effect
110;2010;12;03;zen-photo-and-nginx-rewrite-rules
112;2010;12;03;linux-like-environment-for-windows

After file is stored in local file system you can use awk to generate rewrite rules:

$  awk -F\; '{print "Rewriterule ^" $1 "$ http://blog.sleeplessbeastie.eu/" $2 "/" $3 "/" $4 "/" $5 "/ [R=301,L]"}' wp_posts.csv
Rewriterule ^12$ http://blog.sleeplessbeastie.eu/2010/11/29/how-to-access-cli-on-linksys-srw/ [R=301,L]
Rewriterule ^42$ http://blog.sleeplessbeastie.eu/2010/11/29/how-to-use-imagemagick-effects-with-coppermine/ [R=301,L]
Rewriterule ^99$ http://blog.sleeplessbeastie.eu/2010/11/30/having-problems-with-some-ie-only-web-interface-using-firefox/ [R=301,L]
Rewriterule ^100$ http://blog.sleeplessbeastie.eu/2010/12/01/nginx-proxy-and-real-ip-address/ [R=301,L]
Rewriterule ^101$ http://blog.sleeplessbeastie.eu/2010/12/01/nginx-and-gandi-ssl-certificate/ [R=301,L]
Rewriterule ^102$ http://blog.sleeplessbeastie.eu/2010/12/02/https-everywhere/ [R=301,L]
Rewriterule ^107$ http://blog.sleeplessbeastie.eu/2010/12/03/ruby-and-rmagick-quote-with-polaroid-like-effect/ [R=301,L]
Rewriterule ^108$ http://blog.sleeplessbeastie.eu/2010/12/04/zen-photo-polaroid-like-effect/ [R=301,L]
Rewriterule ^110$ http://blog.sleeplessbeastie.eu/2010/12/03/zen-photo-and-nginx-rewrite-rules/ [R=301,L]
Rewriterule ^112$ http://blog.sleeplessbeastie.eu/2010/12/03/linux-like-environment-for-windows/ [R=301,L]
[...]
Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu