DB_COLLATE is not used in database queries

[bertilow]

Testing WP-2.5RC1 I found that the collation set in "wp-config.php" (DB_COLLATE) is not being used in searches. I think it should.

This can easily be fixed. In "wp-includes/query.php" the following part can be changed as indicated here:

// If a search pattern is specified, load the posts that match
if ( !empty($q['s']) ) {

  // [...]

  foreach((array)$q['search_terms'] as $term) {
    $term = addslashes_gpc($term);
    // The following like has been added
    if ($wpdb->collate) $collatesql = ' COLLATE ' . $wpdb->collate;
    // The following line has been changed
    // $collatesql was inserted in two places
    $search .= "{$searchand}(($wpdb->posts.post_title LIKE '{$n}{$term}{$n}'$collatesql) OR ($wpdb->posts.post_content LIKE '{$n}{$term}{$n}'$collatesql))";
    $searchand = ' AND ';
  }

  // [...]
} 

There is probably a more general way to fix this, e.g. by including a "COLLATE" statement (using DB_COLLATE) along with "SET NAME" $this->query("SET NAMES '$this->charset'") in "wp-db.php", but the above solution should also work.

[lloydbudd]

bertilow, can you attach a patch?

[bertilow]

Added a patch implementing my solution

[westi]

Relates to #3517 and [4860].

At present the DB_COLLATE define is only used when creating the tables at install time.

[westi]

(In [7579]) Specify collation as well when connecting to the database if it is provided. See #6410 for trunk.

[westi]

That does it for trunk.

Ryan can you give this the once over before we possibly merge into 2.5.1

[Denis-de-Bernardy]

when SAVEQUERIES is turned on, the first query always is:

[0]=>
  array(3) {
    [0]=>
    string(16) "SET NAMES 'utf8'"
    [1]=>
    float(0.00020217895507812)
    [2]=>
    string(12) "require_once"

doesn't this mean db_collate is actually used?

D.

[DD32]

doesn't this mean db_collate is actually used?

"SET NAMES 'utf8'" sets the charset to utf8
"SET NAMES 'utf8' COLLATE 'abcde'" sets the charset to utf8 AND the collate to abcde(I'm not sure of a valid collate, so i'm just using that as an example)

[westi]

Replying to DD32:

doesn't this mean db_collate is actually used?

"SET NAMES 'utf8'" sets the charset to utf8
"SET NAMES 'utf8' COLLATE 'abcde'" sets the charset to utf8 AND the collate to abcde(I'm not sure of a valid collate, so i'm just using that as an example)

This is correct. Collation is rarely needed unless the db charset has many possible collations.