0

I have an issue using Class wpdb a (more or less) complex query in Wordpress 4.7.5.

Character set and collation is default (utf8mb4, utf8mb4_unicode_ci) configured in wp-config.php and mysql down to database tables.

wp-config:

define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

MySql:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
**| character_set_database   | utf8mb4                    |**
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0,00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
**| collation_database   | utf8mb4_unicode_ci |**
| collation_server     | latin1_swedish_ci  |
+----------------------+--------------------+
3 rows in set (0,00 sec)

My code is the following and returns results as shown in the comments:

    $qry =
"SELECT
    p.post_author as usr,
    COUNT(*) AS cnt
FROM wp_comments c
LEFT OUTER JOIN wp_usermeta um ON um.user_id = c.user_ID
LEFT OUTER JOIN wp_posts p ON p.ID = c.comment_post_ID
LEFT OUTER JOIN wp_commentmeta cm ON cm.comment_id = c.comment_ID
LEFT OUTER JOIN wp_term_relationships tr ON tr.object_id = p.ID
LEFT OUTER JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
LEFT OUTER JOIN wp_terms t ON t.term_id = tt.term_id
WHERE p.post_status = 'publish'
AND c.comment_approved = '1'
AND cm.meta_key = 'valuexy'
AND str_to_date(um.meta_value, '%Y-%m-%d %H:%i') <= str_to_date(c.comment_date, '%Y-%m-%d %H:%i')
AND tt.taxonomy = 'category'
AND t.slug IN ( 'value01','value02','value03' )
GROUP BY p.post_author";

// the wrong result -why?
            global $wpdb;
            $trash = $wpdb->get_results( $qry, OBJECT );

            foreach( $trash as $v )
                echo "<p>usr: {$v->usr} -> cnt: {$v->cnt}</p>";

/*          WRONG RESULT:   
            usr: 28 -> cnt: 26
            usr: 31 -> cnt: 26
            usr: 35 -> cnt: 50
*/ 


// the correct result
            $conn = new mysqli('localhost', DB_USER, DB_PASSWORD, DB_NAME);
            $result = $conn->query($qry);

            if ($result->num_rows > 0) {
                // output data of each row
                while($row = $result->fetch_assoc()) {
                    echo "usr:" . $row["usr"]. "  cnt:" . $row["cnt"] . "<br>";
                }
/*          CORRECT RESULT: 
            usr:28 cnt:3
            usr:31 cnt:3
            usr:35 cnt:4
*/ 
            } else {
                echo "0 results";
            }
            $conn->close();


// this simple query returns a correct result!
$qry = "SELECT COUNT(*) FROM {$wpdb->posts};";
$res = $wpdb->get_var($qry);
echo "<br>WORKS WELL = POSTS = {$res}";

By running the query in variants selecting a single user with ($wpdb->get_var(), $wpdb->get_row()) the same wrong results are received.

But using mysqli directly I receive correct results from count(*) of that query. The same correct results are received by using different clients (eclipse, mysql-terminal-client, mysql-workbench).

On the other hand running a very simple query with $wpdb that counts all posts of wp_posts (see coding above) delivers a correct result.

My question is: What is the reason for this behaviour of wpdb-class and how can I fix that?

  • Have you tried using Query Monitor or a similar plugin? They may provide debug information and may also show you where the wpdb query is actually slightly different from the way your mysqli query is running. – WebElaine May 19 '17 at 14:43
  • Thank for that hint! I've tried it. Nice tool, but doesn't dive deep enough to help to resolve the reason for this strange problem. – JnBrgr May 19 '17 at 16:04
  • There are a handful of similar plugins - if you search for something like "wp query debug plugin" you may find one that gives you the details you need. – WebElaine May 19 '17 at 16:08
  • I've changed the query and removed a mistake. Now the results are the same everywhere. – JnBrgr May 19 '17 at 16:18
  • Well, that's my solution, but the question stays answerless up to now: There shouldn't be differences in the results, no matter if a query is executed by wbdb or any other client. – JnBrgr May 19 '17 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.