WordPress is the world’s most popular blogging platform and is used by millions of websites. It has been converted and customized to almost any kind of website imaginable. This article is if you want to retrieve content from a WP database without having to go through WP code or functions. Just straight access to the WP database using SQL.
We take two example cases of extracting posts from WP:
- Just browsing posts as per the posted data
- Retrieving posts using tags to search for matching posts
Both the above cases fetch the same data. What they retrieve are:
- The url to the actual post
- The featured image if any
- The post title
- The post content
As a side-feature, any images in the post content are removed. This line can be commented out if required. Its just that the method of retrieval is different.
WordPress Database Schema
Give below is the database schema diagram, generated by Socrates
Browse Posts
The code below shows how to extract posts as per posted data. You can set the limit of posts to fetch.
<?php
$no_of_news_items = 10;
$g_connServer="localhost";
$wp_uid = "userid";
$wp_pwd = "password";
$wp_db = "database";
$wconnData = mysql_connect($g_connServer, $wp_uid, $wp_pwd);
if ($wconnData) {
$wdb =mysql_select_db($wp_db, $wconnData);
if ($wdb) {
$sql = "select * from wp_posts where post_status ='publish' and post_date <= now() order by ID desc limit 0," .$no_of_news_items;
$rst = mysql_query($sql, $wconnData);
if ($rst) {
// get WP base url
$sql = "select option_value from wp_options where option_name='siteurl'";
$rst2 = mysql_query($sql, $wconnData);
while ($row2 = mysql_fetch_assoc($rst2)) {
$wp_base = $row2["option_value"] . "/wp-content/uploads";
}
mysql_free_result($rst2);
// get matching posts
$arrData = array();
$i = 0;
while ($row = mysql_fetch_assoc($rst)) {
$arrData[$i]["link"] = $row["guid"];
$arrData[$i]["id"] = $row["ID"];
$arrData[$i]["title"] = $row["post_title"];
$t = trim($row["post_content"]);
$t = preg_replace("/<img[^>]+\>/i", "", $t); // remove any images in text
if (strlen($t) > 200) // truncate text to 200 chars
$t = truncateText($t, 200) ;
$t .= " <a href='" . $row["guid"] ."' target=_blank>[Read More..]</a>";
$arrData[$i]["content"] = $t;
// get featured image
$sql = "select meta_value from wp_postmeta where post_id = " . $row["ID"] . " and meta_key = '_thumbnail_id' limit 0,1";
$rst2 = mysql_query($sql, $wconnData);
if ($rst2) {
while ($row2 = mysql_fetch_assoc($rst2)) {
$image_id = $row2["meta_value"];
}
mysql_free_result($rst2);
$sql = "select * from wp_postmeta where post_id=" . $image_id . " and meta_key = '_wp_attached_file'";
}
$rst3 = mysql_query($sql, $wconnData);
if ($rst3) {
while ($row3 = mysql_fetch_assoc($rst3)) {
$image_path= $row3["meta_value"];
}
mysql_free_result($rst3);
}
$arrData[$i]["featured_image"] = $wp_base . "/" . $image_path;
$i++;
} // while ($row = mysql_fetch_assoc($rst)) {
mysql_free_result($rst);
mysql_close($wconnData);
} // if($rst)
}
else
exit("Error opening database");
}
else
exit( "Error opening connection");
function truncateText($text, $maxlen) {
$retVal = "";
$delimiters = array(".", " ", ",", "!", "\n", "?");
if (strlen($text) <= $maxlen)
$retVal = $text;
else {
$i = $maxlen;
while(!in_array(substr($text,$i, 1), $delimiters))
{
$i = $i+1;
}
$retVal = substr($text,0,$i);
}
return $retVal;
}
?>
<html>
<head>
<title></title>
<style>
.wp_news_block_heading {
width: 600px;
height: auto;
padding:5px;
background-color:#e5e5e5;
color:#343434;
padding-left:10px;
font-family;arial;
font-size:15px;
font-weight:bold;
text-transform:capitalize;
}
.wp_news_block {
width: 600px;
height: 120px;
margin-top: 20px;
border-bottom:1px solid #e5e5e5;
}
.wp_news_block .image {
float:left;
width:150px;
padding:5px;
height:120px;
}
.wp_news_block .data {
float:right;
width:420px;
padding-left:5px;
margin:0;
font-size:14px;
height:120px;
}
.wp_news_block .data a{
}
.wp_news_block .data .heading {
font-size:18px;
font-weight:bold;
margin-bottom:10px;
}
.wp_news_block .data .text {
font-size:12px;
color: #846d78;
}
</style>
</head>
<body>
<div class="wp_news_block_heading">SOME HEADING COMES HERE</div>
<?php
for($i= 0; $i <count($arrData); $i++) {
$single = $arrData[$i];
?>
<div class="wp_news_block">
<div class="image">
<a href='<?php echo($single["link"]);?>' target=_blank><img src="<?php echo($single["featured_image"]);?>" border=0 align=left width=150 height=112></a>
</div>
<div class="data">
<div class="heading">
<?php echo($single["title"]);?>
</div>
<div class="text">
<?php echo($single["content"]);?>
</div>
</div>
<div class="spacer"></div>
</div>
<div class="spacer_large"></div>
<?php } ?>
</body>
</html>
Search Posts With Tags
In this case we retrieve posts based on matching tags. Eg.we create a tag string with the value “music,pop,rock” and this code will go and retrieve the posts which have either of these tags.
<?php
$no_of_news_items = 10;
$g_connServer="localhost";
$wp_uid = "userid";
$wp_pwd = "password";
$wp_db = "database";
$wp_tags = "tag1,tag2"; // tags separated by commas
$wconnData = mysql_connect($g_connServer, $wp_uid, $wp_pwd);
if ($wconnData) {
$wdb =mysql_select_db($wp_db, $wconnData);
if ($wdb) {
if (strpos($wp_tags, ",") !== false) {
$arrtags = explode(",", $wp_tags);
}
else
$arrtags[] = $wp_tags;
$sql = "select term_id from wp_terms where (";
foreach($arrtags as $t) {
if ($t != '')
$sql .= " name='" . $t . "' or ";
}
if (strpos($sql, "or") !== false)
$sql = substr($sql, 0, strlen($sql)-3);
$sql .= ")";
$rst = mysql_query($sql, $wconnData);
if ($rst) {
$term_ids = '';
while ($row = mysql_fetch_assoc($rst)) {
$term_ids .= $row['term_id'] . ",";
}
mysql_free_result($rst);
if (strpos($term_ids, ",") !== false)
$term_ids = substr($term_ids, 0, strlen($term_ids)-1);
$sql = "select object_id from wp_term_relationships where term_taxonomy_id in (" . $term_ids . ")";
if ($term_ids != '')
$rst = mysql_query($sql, $wconnData);
if ($rst) {
$object_ids = '';
while ($row = mysql_fetch_assoc($rst)) {
$object_ids .= $row['object_id'] . ",";
}
mysql_free_result($rst);
if (strpos($object_ids, ",") !== false)
$object_ids = substr($object_ids, 0, strlen($object_ids)-1);
$sql = "select * from wp_posts where post_status ='publish' and ID in (" . $object_ids . ") order by ID desc limit 0," .$no_of_news_items;
$rst = mysql_query($sql, $wconnData);
if ($rst) {
// get WP base url
$sql = "select option_value from wp_options where option_name='siteurl'";
$rst2 = mysql_query($sql, $wconnData);
while ($row2 = mysql_fetch_assoc($rst2)) {
$wp_base = $row2["option_value"] . "/wp-content/uploads";
}
mysql_free_result($rst2);
// get matching posts
$arrData = array();
$i = 0;
while ($row = mysql_fetch_assoc($rst)) {
$arrData[$i]["link"] = $row["guid"];
$arrData[$i]["id"] = $row["ID"];
$arrData[$i]["title"] = $row["post_title"];
$t = trim($row["post_content"]);
$t = preg_replace("/<img[^>]+\>/i", "", $t); // remove images in text
if (strlen($t) > 200) // truncate text at 200 chars
$t = truncateText($t, 200) ;
$t .= " <a href='" . $row["guid"] ."' target=_blank>[Read More..]</a>";
$arrData[$i]["content"] = $t;
// get featured image
$sql = "select meta_value from wp_postmeta where post_id = " . $row["ID"] . " and meta_key = '_thumbnail_id' limit 0,1";
$rst2 = mysql_query($sql, $wconnData);
if ($rst2) {
while ($row2 = mysql_fetch_assoc($rst2)) {
$image_id = $row2["meta_value"];
}
mysql_free_result($rst2);
$sql = "select * from wp_postmeta where post_id=" . $image_id . " and meta_key = '_wp_attached_file'";
}
$rst3 = mysql_query($sql, $wconnData);
if ($rst3) {
while ($row3 = mysql_fetch_assoc($rst3)) {
$image_path= $row3["meta_value"];
}
mysql_free_result($rst3);
}
$arrData[$i]["featured_image"] = $wp_base . "/" . $image_path;
$i++;
} // while ($row = mysql_fetch_assoc($rst)) {
mysql_free_result($rst);
mysql_close($wconnData);
} // if ($rst) {
else {
//exit("Error: could not fetch posts");
}
} // if ($rst) {
else {
exit("Error: could not fetch object_id");
}
} // if ($rst) {
else {
exit("Error: could not fetch term_id");
}
}
else
exit("Error opening database");
}
else
exit( "Error opening connection");
function truncateText($text, $maxlen) {
$retVal = "";
$delimiters = array(".", " ", ",", "!", "\n", "?");
if (strlen($text) <= $maxlen)
$retVal = $text;
else {
$i = $maxlen;
while(!in_array(substr($text,$i, 1), $delimiters))
{
$i = $i+1;
}
$retVal = substr($text,0,$i);
}
return $retVal;
}
?>
<html>
<head>
<title></title>
<style>
.wp_news_block_heading {
width: 600px;
height: auto;
padding:5px;
background-color:#e5e5e5;
color:#343434;
padding-left:10px;
font-family;arial;
font-size:15px;
font-weight:bold;
text-transform:capitalize;
}
.wp_news_block {
width: 600px;
height: 120px;
margin-top: 20px;
border-bottom:1px solid #e5e5e5;
}
.wp_news_block .image {
float:left;
width:150px;
padding:5px;
height:120px;
}
.wp_news_block .data {
float:right;
width:420px;
padding-left:5px;
margin:0;
font-size:14px;
height:120px;
}
.wp_news_block .data a{
}
.wp_news_block .data .heading {
font-size:18px;
font-weight:bold;
margin-bottom:10px;
}
.wp_news_block .data .text {
font-size:12px;
color: #846d78;
}
</style>
</head>
<body>
<div class="wp_news_block_heading">SOME HEADING COMES HERE</div>
<?php
for($i= 0; $i <count($arrData); $i++) {
$single = $arrData[$i];
?>
<div class="wp_news_block">
<div class="image">
<a href='<?php echo($single["link"]);?>' target=_blank><img src="<?php echo($single["featured_image"]);?>" border=0 align=left width=150 height=112></a>
</div>
<div class="data">
<div class="heading">
<?php echo($single["title"]);?>
</div>
<div class="text">
<?php echo($single["content"]);?>
</div>
</div>
<div class="spacer"></div>
</div>
<div class="spacer_large"></div>
<?php } ?>
</body>
</html>
The truncate() function is a small function which truncates a string only at a word boundary instead of cutting it in the middle of a word.
Leave a Reply