wpdb query not returning results containing single quotes

Question

I'm using a simple $wpdb query that searches the database for all other posts with the same title as the current one and then displays a link to them on the current post.

So if I go to a post titled RED SHOES it will display at the top of the screen a link to all other posts with the title RED SHOES.

It works fine for all post titles except those with a single quote in them. Other special characters work fine.

A post with the title THE WEATHER: SUNNY will feature a list to all other posts with that title. But a post with the title TODAY'S WEATHER IS NICE will not find any other posts with that title even though they exist, because it contains a single quote.

I've searched for a solution but haven't found one. Can anyone suggest a way around this?

Here's the code I'm using:

global $wpdb;
$itemName = get_the_title( $ID );
$results =$wpdb->get_results("SELECT ID FROM $wpdb->posts where  $wpdb->posts.post_title LIKE '$itemName' AND $wpdb->posts.post_status = 'publish' ORDER BY post_title ASC", OBJECT);

foreach ($results as $result){
$newTitle =$wpdb->get_results("SELECT post_title FROM $wpdb->posts where  $wpdb->posts.ID = $result->ID", OBJECT);
printf ($newTitle->post_title);
}

By the way, I realise that code will only display the title of other pages and not the link. I've removed the 'link' section as it works fine, so it's not necessary here.

EDIT:

This is the updated code based on userabuser's suggestion. It's currently returning an error "Unexpected '=' on line 55", possibly due to my (mis)use of the proper quotation mark syntax.

global $wpdb;
$itemName = get_the_title( $ID );
$results = $wpdb->get_results($wpdb->prepare("SELECT ID FROM $wpdb->posts where $wpdb->posts.post_title LIKE '%%%s%%'", $wpdb->esc_like("$itemName") AND $wpdb->posts.post_status = 'publish' ORDER BY post_title ASC", OBJECT));

Answer 1

Here is an example of performing a LIKE based query while escpaing the value parameter passed to the query:

$results = $wpdb->get_results( 
    $wpdb->prepare( 
        "SELECT post_title, ID from $wpdb->posts WHERE post_title LIKE '%%%s%%'", 
        $wpdb->esc_like("today's weather is nice")
    ) 
);

Update:

Based on your edit, your query is incorrectly formed hence the error, it should be:

global $wpdb;

$itemName = get_the_title($ID);

$results = $wpdb->get_results(
    $wpdb->prepare(
        "
        SELECT ID 
        FROM $wpdb->posts
        WHERE $wpdb->posts.post_title 
        LIKE '%%%s%%' 
        AND $wpdb->posts.post_status = 'publish' 
        ORDER BY post_title ASC
        ", 
        $wpdb->esc_like($itemName)
    )
);