2

I am pulling data from a database using mysqli and PHP.

This works OK but I get a random character (black question mark) replacing apostrophes in words, so I thought I could use the htmlspecialchars() function to have them display properly, but it doesn't work.

So my code looks like this

while($row = $result->fetch_assoc())
{
    $desc = htmlspecialchars($row['description'], ENT_QUOTES);?>

    <div class="timeline-key">
        <div class="fluid key-content">
            <p><?php echo $desc?></p>
        </div>
    </div><?php
}

Now any record in the database which contains an apostrophe $desc prints out nothing, all other records print out OK?

Now if I try the exact same come on a string that is not being pulled from the database it displays it OK?

Dharman
  • 30,962
  • 25
  • 85
  • 135
BBLJ84
  • 175
  • 1
  • 12

1 Answers1

3

From the website you linked, it is obvious that the page itself uses UTF-8 for character encoding (<meta charset="UTF-8">). It seems that the database from which you fetch your data uses a character encoding that is not equal to the ini_get("default_charset") value. If you get the encoding wrong, htmlspecialchars will return the empty string, which is what you're seeing here.

The solution is to be consistent in (or explicit about) which character encoding you're using. This is a lot simpler when you use one character encoding for everything. Let's use UTF-8 as a good example:

  • Tell the browser you're using UTF-8. You already do that with the meta tag, so that's good.
  • Tell PHP you're using UTF-8. Make sure that php.ini sets default_charset to UTF-8.
  • Tell MySQL you're using UTF-8. Make sure the tables in your database use UTF-8 as their encoding -edit- or tell the MySQL interface to use UTF-8.

Alternatively, you could use a function like mb_convert_encoding to convert the strings between the different character encodings that the browser is expecting, that PHP is assuming, and that MySQL uses to store and serve text.

  • thank you for your detailed reply, i understand what is going on now. as you said i have told the browser to use UTF-8 now i have added the following to see what the default charset for php is: echo ini_get("default_charset"); and that returns UTF-8 so now its just down to the database itself. which I'm having trouble checking. am awaiting my host to get back to me to see what the current charset is – BBLJ84 Oct 04 '16 at 14:57
  • 1
    ok i have just checked my sql charset using var_dump($conn->get_charset()); and that returned object(stdClass)#2 (8) { ["charset"]=> string(6) "latin1" ["collation"]=> string(17) "latin1_swedish_ci" ["dir"]=> string(0) "" ["min_length"]=> int(1) ["max_length"]=> int(1) ["number"]=> int(8) ["state"]=> int(1) ["comment"]=> string(0) "" } which shows that mysql charset is latin1_swedish_ci so that is obviously the issue here. currently trying to set a new charset for mysql – BBLJ84 Oct 04 '16 at 15:08
  • Query the information schema, as explained [in this answer](/a/1049958/824425). –  Oct 04 '16 at 15:09
  • sorted it!!! added the following line mysqli_set_charset($conn,"utf8"); and now all of the 3 are set to UTF-8 thank you very much!!!!!! – BBLJ84 Oct 04 '16 at 15:18