how to replace apostrophe (’) with single quote(')

Question

It seems there are two types of apostrophes.I want to know the difference between this character(’) and this(')

the first one was copied from Microsoft Word and I'm trying to past it into text area and then insert into database but it doesn't work. it breaks my query so I want to replace it with this one(') please how do I achieve this.

I tried this but it seem not working

function replace_microsolft_apostrophe($string){
        $string = str_replace('’', "'", $string);
        return $string;
    }

my query looks like this:

function create_note($page_id,$title,$note,$category,$author_id){
    global $conn,$notes_table,$pages_table,$notification_table;
    $sql = "INSERT INTO $notes_table(page_id,title,content,note_category) VALUES(?,?,?,?)";
    $query = $conn->prepare($sql);
    $query->bind_param("issi",$page_id,$title,htmlspecialchars($note),$category);
    $query->execute();
}

The above query works fine without this character (’)

also I want to know why it this so? because it is the same key i press but when it goes to Microsoft Word the character seem to change. The reason why am trying to make it work is because a user may copy an already typed work from Microsolf word and past on my application where I expect them to write and note and publish it.

Any help would be much good.

Answer 1

WOW! after surfing the net I found Pascal Martin's answer very useful...it also has a reference to a website with the full answer. How to replace Microsoft-encoded quotes in PHP

I was able to replace right quotation mark with normal quote

  //convert single-byte apostrophes -encoded
function convert_smart_quotes($string) 

{ 
    $search = array(chr(145), 
                    chr(146), 
                    chr(147), 
                    chr(148), 
                    chr(151)); 

    $replace = array("'", 
                     "'", 
                     '"', 
                     '"', 
                     '-'); 

    return str_replace($search, $replace, $string); 
} 

Also this answer is much more useful: Converting Microsoft Word special characters with PHP

Answer 2

This seems to be a charset issue. Have you checked if even your editor's charset is set to ISO-8859-1?

Anyway, a workaround could be convert your string to hex and let MySQL convert it again to string. I haven't tested this code, but it should work.

function create_note($page_id,$title,$note,$category,$author_id){
    global $conn,$notes_table,$pages_table,$notification_table;
    $sql = "INSERT INTO $notes_table(page_id,title,content,note_category) VALUES(?,?,UNHEX(?),?)";
    $query = $conn->prepare($sql);
    $query->bind_param("issi",$page_id,$title,bin2hex(htmlspecialchars($note)),$category);
    $query->execute();
}