For a very long time now we have been looking for a method to get permalinks from our Worpress database. We searched on Google for everything we could think of, find WordPress permalinks in database, WordPress MySQL permalinks and so on. We searched on this issues for many months to now avail.
We found out the the post title is stored in a ‘slug’ in the database so we tried to access ‘post_slug’, but this is not a field name in the posts table.
So we used php to see all the fields and the data in the posts table. We found that the actual post permalink is not stored in the database anywhere. At least no where we checked and we checked most of the tables in the WordPress database over a long period of time.
Our research into the WordPress database revealed that the WordPress post slug is actually called ‘post_name’ this gives the post title converted to the permalink slug needed to generate a WordPress permalink from the database.
So to get a permalink one must generate a link similar to the one below:
http://domain-name.com/blog/<?= $year . '/' . $month . '/' . mysql_result($result, $x, 'post_name') ?>/
We use the following php and MySQL to get the needed data to generate a WordPress Permalink directly from the database:
include "db_connect.php"; // Connection to the Database
$query = "select * from wp_posts where post_status = 'publish' and post_type = 'post' order by post_date DESC limit 1"; // This pulls the most recently published post from the database
$result = mysql_query($query);
$date_time = mysql_result($result, $x, 'post_date'); // Get the timestamp the post was published on
$date_time = split(' ', $date_time); // Split the timestamp the post was published on into the neded parts
$date = $date_time;
$date = split('-', $date);
$year = $date;
$month = $date;
$day = $date;
$time = $date_time;
$time = split(':', $time);
$hour = $time;
$minute = $time;
$second = $time
<a href="http://domain-name.com/blog/<?= $year . '/' . $month . '/' . mysql_result($result, $x, 'post_name') ?>/">
<?= preg_replace("/[^a-zA-Z0-9s]/", " ", mysql_result($result, $x, 'post_title')) ?>
This is very nice because it allows adding permalinks to pages outside of the blog.
WordPress probably didn’t make an easy process to allow this since WordPress was setup to be the CMS (Content Management System) for an entire website, but we know many blogs that still have separate static or dynamic pages.
Perhaps your site has a php shopping cart and you would like to dynamically add permalinks to the cart, which link to the blog on the same server. This is exactly the kind of situation where the above code becomes vital, but the information to write the code is not easily available.
We are not sure why WordPress would not make this more easily available or maybe it’s just difficult to provide a database map in terms people could understand. We know there must be many people trying to crack the Post Slug code and find out where on earth the WordPress post slug is actually stored in the database.
If you need to find your post slugs inside of your WordPress database look in the posts table (posts or wp_posts by default) under the column name ‘post_name’. Some might be looking for the field name post_slug that doesn’t seem to exist. The field the WordPress post slugs are stored in is: post_name
We’re not sure why the WordPress developers didn’t use something other than post name to store the post slug, since post_name could easily be confused with post_title where the post title is stored.