2

Am creating XML sitemap using PHP. The XML values are from database and using below code to generate XML sitemap.

header("Content-Type: application/xml; charset=utf-8");
echo '<?xml version="1.0" encoding="UTF-8"?>'.PHP_EOL;

$query = mysqli_query($connect, "SELECT * FROM `tbl_city_landing_pages` WHERE `city_landing_pages_status` = 1 ORDER BY `city_landing_pages_keyword` ASC");
$total = mysqli_num_rows($query);

echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'.PHP_EOL;

while ($city_rows = mysqli_fetch_array($query)) {
    echo '<url>'.PHP_EOL;
    echo '<loc>'.BX_DOL_URL_ROOT.strtolower(str_replace(array(" ", "/"), array("-", "_"), $city_rows["city_landing_pages_keyword"])).'</loc>'.PHP_EOL;
    echo '<lastmod>'.date('Y-m-d').'</lastmod>'.PHP_EOL;
    echo '<changefreq>daily</changefreq>'.PHP_EOL;
    echo '</url>'.PHP_EOL;
}

echo '</urlset>'.PHP_EOL;

The above code is working fine with some characters.

In my DB some row contains Bogotá, D.C.. And this character is not encoding even charset is utf-8. And the error shown is,

This page contains the following errors:

error on line 19 at column 6: Encoding error

Below is a rendering of the page up to the first error.

How to show all the text from DB.

Is there any solution for this. I need to display all the values from mysql DB.

Jithin Varghese
  • 2,018
  • 3
  • 29
  • 56
  • Is your database connection set to use UTF-8? How about the columns / tables? – Phil Jan 16 '19 at 03:51
  • @Phil database is set to UTF-8 – Jithin Varghese Jan 16 '19 at 03:53
  • That's a very general response. I asked you about three very separate things. If you could, please show where you're setting the connection encoding, ie [`mysqli_set_charset()`](http://php.net/manual/mysqli.set-charset.php) – Phil Jan 16 '19 at 03:55
  • I said about database collation utf8_general_ci – Jithin Varghese Jan 16 '19 at 03:58
  • So are you setting your connection encoding via `mysqli_set_charset()` or not? You should also check out [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Phil Jan 16 '19 at 05:53

1 Answers1

1

Looks like you have character encoding issues.

Try this approach,

  • First, identify columns that can produce results with different character sets.
  • Wrap them with utf8_encode before adding to the XML node.
  • In your example above, let's say this is the column ($city_rows["city_landing_pages_keyword"]), that brings different character sets. Wrap them with utf8_encode before placing in the XML. See below,

echo '<loc>'.BX_DOL_URL_ROOT.strtolower(str_replace(array(" ", "/"), array("-", "_"), utf8_encode($city_rows["city_landing_pages_keyword"]))).'</loc>'.PHP_EOL;

Likewise, do for the rest of the columns that can bring results with different character sets.

Hope this helps. Cheers.

Anjana Silva
  • 8,353
  • 4
  • 51
  • 54