List Posts Using $wpdb and setup_postdata() in WordPress

In wordpress, we can use $wpdb to execute a sql to query posts. In order to display these posts, we can use setup_postdata(). In this tutorial, we will introduce you how to use them to do.

Create a sql to query posts

We can create a custom sql to query wordpress posts. For example:

$current_object = get_queried_object();

$current_object = get_queried_object();
$current_id = $current_object->ID;
$text = addslashes($current_object->post_title);
$sql = "select * from wp_posts where post_status='publish' and ID !=".$current_id." and match(post_title) against('".$text."') limit 0, 6";

Here $sql is our sql statement.

Execute sql using $wpdb

After creating $sql, we can use $wpdb to execute it.

global $wpdb,  $post;
$result = $wpdb->get_results($sql, OBJECT);

Here, we should notice we have created a php global variable $post, which is very important. It will be used in setup_postdata().

Display posts

Finally, we can display posts as follows:

<?php
if($result && count($result) > 0){
    foreach ($result as $post){
        setup_postdata($post);
?>
        <a href="<?php the_permalink(); ?>"><?php the_title(); ?></a>
        <div class="excerpt">
        <?php the_excerpt(); ?>
        </div>
<?php
wp_reset_postdata();     
}
?>

Here we should notice $post must be in foreach statement. Otherwise, posts will no be displayed correctly.