get_results query with accent

I’m trying to do an SQL query and I don’t understand something. I get a value with $ _POST, this value is equal to ‘définition’. I made this request:
$sql = "SELECT DISTINCT * FROM". $ wpdb-> prefix. "posts WHERE post_title LIKE '%". $ _POST ['value']. "% '";.

A var_dump($sql) gives "SELECT DISTINCT * FROM datatablename.posts WHERE post_title LIKE '% definition%'";.

If I do $res = $wpdb->get_results($sql);, I get an empty array

But, if in my code I put directly $sql = "SELECT DISTINCT * FROM datatablename.posts WHERE post_title LIKE '% definition%'"; (I immediately replace $_POST with my value), $res is an array with a post.

The problem stems from the accent, because if $_POST['value'] = 'finition' it’s okay

My data table is in utf8mb4_unicode_ci.

What can be done to solve this problem?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Your SQL command is highly insecure and open to security issues like SQL injection, so even if this may not answer the question, I strongly suggest you to use $wpdb->prepare() and $wpdb->esc_like() — the latter is used to escape the % character in SQL.

Additionally, you can simply use $wpdb->posts to output the table name for WordPress posts such as wp_posts.

And I noticed that in your SQL command:

• The table name is incorrect because the FROM and $wpdb->prefix is concatenated as one word like FROMwp_posts.
• There’s a whitespace after the second % in the LIKE clause: %". $_POST['value']. "% ' — so that whitespace is probably not needed? Or that it could be the reason why the query did not return any results.
• The var_dump() actually contains no accent — you used definition and not définition. Same goes with the direct one.

Now here’s how your query or SQL command should be generated:

$value = $_POST['value'] ?? '';

// wrapped for brevity
$sql = $wpdb->prepare( "
SELECT DISTINCT *
FROM {$wpdb->posts}
WHERE post_title LIKE %s
", '%' . $wpdb->esc_like( $value ) . '%' );

$res = $wpdb->get_results( $sql );

And I actually tested the above code with a post with the title containing the word définition, and the query returned one result (which is a test post).

If my code doesn’t work for you, you can try sanitize_text_field(), but that will strip HTML tags, among other things.

Method 2

Here is a function to query posts with title “like” – returning either IDs or specified columns – escaping both the passed title and any requested column values:

/**
* Get post with title %like% search term
*
* @param       $title          Post title to search for
* @param       $method         wpdb method to use to retrieve results
* @param       $columns        Array of column rows to retrieve
*
* @since       0.3
* @return      Mixed           Array || False
*/
function posts_with_title_like( $title = null, $method = 'get_col', $columns = array ( 'ID' ) ){

    // sanity check ##
    if ( ! $title ) { return false; }

    // global $wpdb ##
    global $wpdb;

    // First escape the $columns, since we don't use it with $wpdb->prepare() ##
    $columns = esc_sql( $columns );

    // now implode the values, if it's an array ##
    if( is_array( $columns ) ){
        $columns = implode( ', ', $columns ); // e.g. "ID, post_title" ##
    }

    // run query ##
    $results = $wpdb->$method (
            $wpdb->prepare (
            "
                SELECT $columns
                FROM $wpdb->posts
                WHERE {$wpdb->posts}.post_title LIKE %s
            "
            ,   esc_sql( '%'.$wpdb->esc_like( trim( $title )  ).'%' )
            )
        );

    #var_dump( $results );

    // return results or false ##
    return $results ? $results : false ;

}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0