Member Avatar for Facte

Hi there everyone. I have a problem with some PHP.

I have to make a xml feed that works in IE in UTF-8 format and I'm having major problems with £ signs and '. the poud signs are in the mySQL database in their £ format and cannot be changed.

I've tried adding preg_replaces and str_replaces, no joy.

Here is the basic code which pulls all the right info, just with ? instead of £ and apostrophes.

Please help.

<?php
$username = ""; //Database Username
$password = ""; //Database Password
$hostname = ""; //Database Host
$dbname = ""; //Database (or Catalog in MySQL parlance) name
$dbh = mysql_connect($hostname, $username, $password) or die("Could not connect to database server");
$selected = mysql_select_db($dbname, $dbh) or die("Database not found or problem connecting");
$result = mysql_query("SELECT employerid, expiry, postdate, position, hour, contract, salary, subcounty, description, jobref, jobid FROM jobs");

// if the file exists already, delete it first to flush data
$xmlfeedfile = "trovit.xml";
$filehandle = fopen($xmlfeedfile, 'w');
$itemLink  = $fullurl.'/info_jobid_'. $b[jobid].'.html';

$xmlString = '<'.'?'.'xml version="1.0" encoding="utf-8" '.'?' .'>
<trovit>';
fwrite($filehandle, $xmlString);
while ($row = mysql_fetch_assoc($result)) {    
if(strtotime($row['expiry']) < time()) {        
continue;    
}

$pos = ($row['position']);
$date = ($row['postdate']);
$ref = ($row['jobref']);
$desc = ($row['description']);
$jid = ($row['jobid']);
$sal = ($row['salary']);
$loc = ($row['subcounty']);

$xmlString = "<ad>\n\t<id><![CDATA[{$ref}]]></id>\n\t<title><![CDATA[{$pos}]]></title>\n\t
<url><![CDATA[></url>\n\t<content><![CDATA[{$desc}]]></content>\n\t<city><![CDATA[{$loc}]]></city>\n\t<salary><![CDATA[{$sal}]]></salary>\n\t<date><![CDATA[{$date}]]></date>\n</ad>";
fwrite($filehandle, $xmlString);
}
mysql_close($dbh);
fwrite($filehandle, "</trovit>");
fclose($filehandle);
?>
  • 4 Contributors
  • 4 Replies
  • 198 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by joles

Dani AI

Generated

Short summary: this is almost certainly an encoding mismatch between what the MySQL rows actually contain and what you declare/send as UTF-8. ’s test (switching the page to ISO-8859-1) making the characters appear correctly is a strong clue the stored bytes are Latin1 / Windows-1252, not UTF-8. ’s placeholder idea can work but is fragile; better to fix the bytes/connection so the XML really is valid UTF-8.

Quick diagnostics you can run (fast and low-risk)

  • Inspect the server/column settings: run SHOW VARIABLES LIKE 'character_set%'; and SHOW FULL COLUMNS FROM jobs; in MySQL to see declared charsets/collations.

  • Check the raw bytes and whether PHP thinks the string is UTF-8:

    echo bin2hex($row['description']);
var_dump(mb_check_encoding($row['description'], 'UTF-8'));

What to do (practical fixes)

  • If the table is declared as Latin1/Windows-1252 (or your diagnostics show the bytes are not UTF-8), convert on output rather than changing the DB. Convert each field before writing the XML:

    $desc = mb_convert_encoding($row['description'], 'UTF-8', 'Windows-1252');
// or
$desc = iconv('CP1252', 'UTF-8//TRANSLIT', $row['description']);

  • If the database actually uses UTF-8 but the client connection isn’t set, tell MySQL to use UTF-8 for the connection (so MySQL will transcode if needed):

    mysql_set_charset('utf8', $dbh);
// or
mysql_query("SET NAMES 'utf8'");

Final checklist before generating the feed

  • Send matching headers and XML declaration (both UTF-8).
  • Open/write the file in binary mode (no BOM), or let DOMDocument build the XML with 'UTF-8' specified to avoid manual encoding mistakes.
  • Avoid utf8_encode/utf8_decode unless you know the source is exactly ISO-8859-1 — prefer mb_convert_encoding or iconv.

If these steps still leave question marks, paste one sample hex dump here and someone can point to the exact source encoding and the proper conversion.

Recommended Answers

All 4 Replies

Member Avatar for paranjyoti

Hi,

Write this at the beginning of the code and see if it works.

header('Content-type: text/html; charset=iso-8859-1');
Member Avatar for Facte

Hi,

Write this at the beginning of the code and see if it works.

header('Content-type: text/html; charset=iso-8859-1');

Sadly if I change the character set it does work, but the company I am dealing with cannot process it. (I know it's very irritating):@

Member Avatar for network18

try replacing those characters with something else and at the other end , get thme back with the exactly opposite logic.

Member Avatar for joles

i really have no idea about this. i am new in this field so i am getting information here. thanks for this.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.