Post listing not showing in WordPress admin with IIS and SQL server database

Post listing not showing in WordPress admin with IIS and SQL server database

Installing WordPress with IIS is a bit challenging when we are using SQL server as a database. Generally we use a plugin on azure to run wordpress setup with MSSQL that is “WP Db Abstraction” plugin.

It is to be noted that this plugin last updated 5 years ago, so it is creating issues with current wordpress version in many ways.

We are discussing a situation here where we are facing post listing issues in admin. When we go to wordpress admin after successful installation or upgrade wordpress. Then click on the posts section, in place of showing post listing we find “nothing found” there. Apart from it, same issue for our users as well. We know we have posts and users in our database but not showing in admin.
If we install contact form 7 plugin and created few forms but listing is not showing. These all are due to our wp db abstraction plugin.
We are discussing two solutions to get rid of these bugs here:

First Solution :

The reason for this is breaking the query that return the posts. Culprit is our $limit variable.

To fix this, Just go to “wp-includes/class-wp-query.php” line number: 2824 or search for below code :

 

$this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby $limits";

 

and replace it with below code:

$this->request = "SELECT $found_rows $distinct {$wpdb->posts}.ID FROM {$wpdb->posts} $join WHERE 1=1 $where $groupby $orderby";

 

Or in simple words, we just need to $limit variable from the query which is responsible for the post listing issue. Now just check the post listing
in admin, all post will show there as they should.

Now come to solve the user listing issue:

To fix this, Just go to “wp-includes/class-wp-user-query.php” line number: 597 or search for below code :

$this->request = "SELECT $this->query_fields $this->query_from $this->query_where $this->query_orderby $this->query_limit";

and replace it with below code:
Now check user listing in admin, you will see that user listing in admin works perfectly as it should be.

$this->request = "SELECT $this->query_fields $this->query_from $this->query_where $this->query_orderby";


Note: Please note that it is only a work around which will solve your posts and user listing bugs in admin. But this is a very bad practice to modify core files of wordpress. We should not modify the core files. I will not suggest anyone to proceed with this solution as it is not good way. If we update wordpress software in future, our modified file will automatically loose our fix.

Recommended Solution:

As we have analysed that this bug is generated due to the use of wp db abstraction plugin. Plugin is not updated from last 5 years. In spite of modifying wordpress core files, ee need to do few necessary modifications in the plugin files to resolve these bugs.

The problem is in translations.php file of plugin. Just go to “wp-content/wp-content/mu-plugins/wp-db-abstraction/translations/sqlsrv/translations.php” line number: 737 or search for below code :

// Check for true offset
        if ( count($limit_matches) == 5 && $limit_matches[1] != '0' ) {
            $true_offset = true;
        } elseif ( count($limit_matches) == 5 && $limit_matches[1] == '0' ) {
            $limit_matches[1] = $limit_matches[4];
        }

and replace it with below code:

// Check for true offset

        if ( count($limit_matches) == 5 && $limit_matches[1] != '0' ) {

            $true_offset = true;

        } elseif ( count($limit_matches) >= 5 && $limit_matches[1] == '0' ) {

            $limit_matches[1] = $limit_matches[4];

        }

After performing this edit, just check the listing in admin. You will find that lists for post, users etc are showing items perfectly.
Sometimes people also facing pagination issue, so to correct it we need to fix a regular expression, to do this just go to “wp-content/wp-content/mu-plugins/wp-db-abstraction/translations/sqlsrv/translations.php” line number: 726 or search for below code :

$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is';

and replace it with below code:

$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)$/is';

In this way we are completely free from the bugs we are facing in our admin while listing of posts , users and also with contact forms or may be others. Please proceed with this Recommended solution, it is Recommended because you can upgrade your wordpress with new versions when it comes without any worry for overridden your changes as first solution.

If you are running a wordpress website with IIS server, then personally I suggest to go with MYSQL database in spite of MSSQL.

If you want, you can contact with us! Our Specialist help you with this issue.

Mostakim Hossen

I am Mostakim Hossain, a Web designer and developer, 23 years of age. I have been working as a freelance (upwork) for almost 2 years now and I will be glad in helping you create your next awesome project.

I have created lots of public and private websites(some shown below) and always looking for more work to learn from. I am using PHP as my primary programming language. You can see my skill set beside this text.

I am a person who is willing to learn and adapts quickly. I am keen to design and security. I believe in the core principles of Open Source and support it. I have a minimalist approach to design and usability. Simplicity is beauty they say.
Masters of Accountancy : Asian University of Bangladesh
I’m here to answer any questions you might have about our products and services. Feel free to contact me using any of the following methods:

Email : support@smarterdevs.com

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe
SUBSCRIBE NOW

Coupon : happynewyear

60% OFF

Stay updated with all latest updates,upcoming plugins & much more.
WordPress Themes
Search