[Plugin: WP Db Abstraction] PROBLEM IN translate_limit

I have follow your instruction and modify the Line 750 of translation.php

Yes, I may see the media library. But I cannot edit or delete anything now.

Therefore, I am still looking for solution…

Here are more detail discussion:

https://www.ads-software.com/support/topic/plugin-wp-db-abstraction-not-showing-posts?replies=7

You have to change two sections. Both in function translate_limit().

Section 1:

From:’/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is’
To: ‘/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)$/is’;

Section 2:

From: count($limit_matches) == 5
To: count($limit_matches) >= 5

Wish it helps!

Personally, I found that if I just changed the == to >=, then it broke the user listing… as it was translating this SQL:
SELECT SQL_CALC_FOUND_ROWS wp_users.ID FROM wp_users WHERE 1=1 ORDER BY user_login ASC LIMIT 20

and interpreting it as a “true_offset”, which in turn was using an array slice to get rows 20 to 20…. not good.

Also I couldn’t publish any posts (probably for a similar reason).

So I rewrote it to:

// Check for true offset - ie "limit 40,20"... if first is 0, or there is no comma, then we can use SQL top - else use array_slice later
        if ( count($limit_matches) >= 5 && $limit_matches[1] == '0' ) {
            $limit_matches[1] = $limit_matches[4];
        } elseif ( count($limit_matches) >= 5 && strlen($limit_matches[3]) > 0 ) {
            $true_offset = true;
        }

So far this (and this alone), seems to have fixed it.

Oh, also I don’t know if it makes a difference, but I figure the interpretation of SQL_CALC_FOUND_ROWS was also wrong… as in mysql it returns the selected rows (limits in place) as well as counting all that match… the replacing the column list with a * is potentially wrong – if ever column numbers rather than names are used… so I modified that to:

if (stripos($query, 'SQL_CALC_FOUND_ROWS') > 0 ) {
			$query = str_ireplace('SQL_CALC_FOUND_ROWS ', '' , $query);
            //$sql_calc_pos = stripos($query, 'SQL_CALC_FOUND_ROWS');
            //$from_pos = stripos($query, 'FROM');
            //$query = substr_replace($query,'* ', $sql_calc_pos, ($from_pos - $sql_calc_pos));
        }

And in the new wordpress, I wasn’t getting the user counts properly as it wasn’t handling the translate_specific function wasn’t matching the query (as it has %”administrator”% instead of %administrator%, etc)… so that was changed to:

if ($this->preg_original == "SELECT COUNT(NULLIF(meta_value` LIKE ‘%administrator%’, FALSE)), “
. “COUNT(NULLIF(meta_value LIKE ‘%editor%’, FALSE)), “
. “COUNT(NULLIF(meta_value LIKE ‘%author%’, FALSE)), “
. “COUNT(NULLIF(meta_value LIKE ‘%contributor%’, FALSE)), “
. “COUNT(NULLIF(meta_value LIKE ‘%subscriber%’, FALSE)), “
. “COUNT(*) FROM ” . $this->prefix . “usermeta WHERE meta_key = ‘” . $this->prefix . “capabilities'”
|| $this->preg_original ==
“SELECT COUNT(NULLIF(meta_value LIKE ‘%\”administrator\”%’, false))”
. “, COUNT(NULLIF(meta_value LIKE ‘%\”editor\”%’, false))”
. “, COUNT(NULLIF(meta_value LIKE ‘%\”author\”%’, false))”
. “, COUNT(NULLIF(meta_value LIKE ‘%\”contributor\”%’, false))”
. “, COUNT(NULLIF(meta_value LIKE ‘%\”subscriber\”%’, false))”
. “, COUNT(*) FROM ” . $this->prefix . “usermeta WHERE meta_key = ‘” . $this->prefix . “capabilities'”
) {
$query = “SELECT SUM(case when meta_value LIKE ‘%administrator%’ then 1 else 0 end) as ca
, SUM(case when meta_value LIKE ‘%editor%’ then 1 else 0 end) as cb
, SUM(case when meta_value LIKE ‘%author%’ then 1 else 0 end) as cc
, SUM(case when meta_value LIKE ‘%contributor%’ then 1 else 0 end) as cd
, SUM(case when meta_value LIKE ‘%subscriber%’ then 1 else 0 end) as ce
, COUNT(*) as c FROM ” . $this->prefix . “usermeta WHERE meta_key = ‘” . $this->prefix . “capabilities'”;
$this->preg_data = array();
}`

Which I think is a more efficient query anyway.

Sorry for posting too much here…. think of it as a brain-dump.