I have a PHP page that creates XML output from a MySQL query, when it is returning one set of records, it displays it nicely with XML formatting.
However when it returns more than one set of records, the XML formatting is dropped. But both requested record sets are returned.
I edit the database records for triggering one or more sets of records to be returned "WHERE wp_hud_display.user_id = 1", each of the records when run on their own, displays clearly in XML, so it cant be some weird character in the database causing the XML formatting to be dropped.
PHP Page
<?php
// create a dom document with encoding utf8
$domtree = new DOMDocument('1.0', 'UTF-8');
//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "root";
$config['mysql_pass'] = "";
$config['db_name'] = "tasmanhud";
//connect to host
mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
//select database
@mysql_select_db($config['db_name']) or die( "Unable to select database");
//select all items in table
$sql = "SELECT
wp_hud_display.id,
wp_hud_display.name,
wp_hud_display.resolution,
wp_hud_display.user_id,
wp_hud_display.instrument_id_1,
wp_hud_display.x_loc_1,
wp_hud_display.y_loc_1,
wp_hud_display.layer_1,
wp_hud_display.instrument_id_2,
wp_hud_display.x_loc_2,
wp_hud_display.y_loc_2,
wp_hud_display.layer_2,
wp_hud_display.instrument_id_3,
wp_hud_display.x_loc_3,
wp_hud_display.y_loc_3,
wp_hud_display.layer_3,
wp_hud_display.instrument_id_4,
wp_hud_display.x_loc_4,
wp_hud_display.y_loc_4,
wp_hud_display.layer_4,
wp_hud_instrument.inst_id AS inst1_id,
wp_hud_instrument.name AS inst1_name,
wp_hud_instrument.image_file AS inst1_image,
wp_hud_instrument.font_type AS inst1_ft,
wp_hud_instrument.font_size AS inst1_fs,
wp_hud_instrument_1.inst_id AS inst2_id,
wp_hud_instrument_1.name AS inst2_name,
wp_hud_instrument_1.image_file AS inst2_image,
wp_hud_instrument_1.font_type AS inst2_ft,
wp_hud_instrument_1.font_size AS inst2_fs,
wp_hud_instrument_2.inst_id AS inst3_id,
wp_hud_instrument_2.name AS inst3_name,
wp_hud_instrument_2.image_file AS inst3_image,
wp_hud_instrument_2.font_type AS inst3_ft,
wp_hud_instrument_2.font_size AS inst3_fs,
wp_hud_instrument_3.inst_id AS inst4_id,
wp_hud_instrument_3.name AS inst4_name,
wp_hud_instrument_3.image_file AS inst4_image,
wp_hud_instrument_3.font_type AS inst4_ft,
wp_hud_instrument_3.font_size AS inst4_fs
FROM wp_hud_display
LEFT JOIN wp_hud_instrument
ON wp_hud_display.instrument_id_1 = wp_hud_instrument.inst_id
LEFT JOIN wp_hud_instrument wp_hud_instrument_1
ON wp_hud_display.instrument_id_2 = wp_hud_instrument_1.inst_id
LEFT JOIN wp_hud_instrument wp_hud_instrument_2
ON wp_hud_display.instrument_id_3 = wp_hud_instrument_2.inst_id
LEFT JOIN wp_hud_instrument wp_hud_instrument_3
ON wp_hud_display.instrument_id_4 = wp_hud_instrument_3.inst_id
WHERE wp_hud_display.user_id = 1
GROUP BY wp_hud_display.id";
/* create the root element of the xml tree */
$xmlRoot = $domtree->createElement("wp_hud_displays");
/* append it to the document created */
$xmlRoot = $domtree->appendChild($xmlRoot);
$hud_display_group = $domtree->createElement("hud_display_group");
$hud_display_group = $xmlRoot->appendChild($hud_display_group);
$result = mysql_query($sql);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
if(mysql_num_rows($result)>0)
{
while($result_array = mysql_fetch_assoc($result))
{
$hud_display = $domtree->createElement("hud_display");
$hud_display = $hud_display_group->appendChild($hud_display);
$instrument_id_1 = $domtree->createElement("instrument_id_1");
$instrument_id_1 = $hud_display->appendChild($instrument_id_1);
$instrument_id_2 = $domtree->createElement("instrument_id_2");
$instrument_id_2 = $hud_display->appendChild($instrument_id_2);
$instrument_id_3 = $domtree->createElement("instrument_id_3");
$instrument_id_3 = $hud_display->appendChild($instrument_id_3);
$instrument_id_4 = $domtree->createElement("instrument_id_4");
$instrument_id_4 = $hud_display->appendChild($instrument_id_4);
/* you should enclose the following two lines in a cicle */
$hud_display->appendChild($domtree->createElement('id',$result_array['id']));
$hud_display->appendChild($domtree->createElement('name',$result_array['name']));
$hud_display->appendChild($domtree->createElement('resolution',$result_array['resolution']));
$hud_display->appendChild($domtree->createElement('user_id',$result_array['user_id']));
// Instrument 1
$hud_display->appendChild($domtree->createElement('instrument_id_1',$result_array['instrument_id_1']));
$instrument_id_1->appendChild($domtree->createElement('inst_id',$result_array['inst1_id']));
$instrument_id_1->appendChild($domtree->createElement('name',$result_array['inst1_name']));
$instrument_id_1->appendChild($domtree->createElement('image_name',$result_array['inst1_image']));
$instrument_id_1->appendChild($domtree->createElement('font_type',$result_array['inst1_ft']));
$instrument_id_1->appendChild($domtree->createElement('font_size',$result_array['inst1_fs']));
$hud_display->appendChild($domtree->createElement('x_loc_1',$result_array['x_loc_1']));
$hud_display->appendChild($domtree->createElement('y_loc_1',$result_array['y_loc_1']));
$hud_display->appendChild($domtree->createElement('layer_1',$result_array['layer_1']));
// Instrument 2
$hud_display->appendChild($domtree->createElement('instrument_id_2',$result_array['instrument_id_2']));
$instrument_id_2->appendChild($domtree->createElement('inst_id',$result_array['inst2_id']));
$instrument_id_2->appendChild($domtree->createElement('name',$result_array['inst2_name']));
$instrument_id_2->appendChild($domtree->createElement('image_name',$result_array['inst2_image']));
$instrument_id_2->appendChild($domtree->createElement('font_type',$result_array['inst2_ft']));
$instrument_id_2->appendChild($domtree->createElement('font_size',$result_array['inst2_fs']));
$hud_display->appendChild($domtree->createElement('x_loc_2',$result_array['x_loc_2']));
$hud_display->appendChild($domtree->createElement('y_loc_2',$result_array['y_loc_2']));
$hud_display->appendChild($domtree->createElement('layer_2',$result_array['layer_2']));
// Instrument 3
$hud_display->appendChild($domtree->createElement('instrument_id_3',$result_array['instrument_id_3']));
$instrument_id_3->appendChild($domtree->createElement('inst_id',$result_array['inst3_id'])); $instrument_id_3->appendChild($domtree->createElement('name',$result_array['inst3_name']));
$instrument_id_3->appendChild($domtree->createElement('image_name',$result_array['inst3_image']));
$instrument_id_3->appendChild($domtree->createElement('font_type',$result_array['inst3_ft'])); $instrument_id_3->appendChild($domtree->createElement('font_size',$result_array['inst3_fs']));
$hud_display->appendChild($domtree->createElement('x_loc_3',$result_array['x_loc_3']));
$hud_display->appendChild($domtree->createElement('y_loc_3',$result_array['y_loc_3']));
$hud_display->appendChild($domtree->createElement('layer_3',$result_array['layer_3']));
// Instrument 4
$hud_display->appendChild($domtree->createElement('instrument_id_4',$result_array['instrument_id_4']));
$instrument_id_4->appendChild($domtree->createElement('inst_id',$result_array['inst4_id']));
$instrument_id_4->appendChild($domtree->createElement('name',$result_array['inst4_name']));
$instrument_id_4->appendChild($domtree->createElement('image_name',$result_array['inst4_image']));
$instrument_id_4->appendChild($domtree->createElement('font_type',$result_array['inst4_ft']));
$instrument_id_4->appendChild($domtree->createElement('font_size',$result_array['inst4_fs'])); $hud_display->appendChild($domtree->createElement('x_loc_4',$result_array['x_loc_4']));
$hud_display->appendChild($domtree->createElement('y_loc_4',$result_array['y_loc_4']));
$hud_display->appendChild($domtree->createElement('layer_4',$result_array['layer_4']));
}
}
// get the xml printed
echo $domtree->saveXML();
//send the xml header to the browser
header ("Content-Type:text/xml");
?>
One Record XML Output
<wp_hud_displays>
<hud_display_group>
<hud_display>
<instrument_id_1></instrument_id_1>
<instrument_id_2></instrument_id_2>
<instrument_id_3></instrument_id_3>
<instrument_id_4></instrument_id_4>
<id>1</id>
<name>test display</name>
<resolution>1080p</resolution>
<user_id>1</user_id>
<instrument_id_1>1</instrument_id_1>
<x_loc_1>89</x_loc_1>
<y_loc_1>79</y_loc_1>
<layer_1>1</layer_1>
<instrument_id_2>2</instrument_id_2>
<x_loc_2>988</x_loc_2>
<y_loc_2>98</y_loc_2>
<layer_2>1</layer_2>
<instrument_id_3>3</instrument_id_3>
<x_loc_3>89</x_loc_3>
<y_loc_3>90</y_loc_3>
<layer_3>1</layer_3>
<instrument_id_4>4</instrument_id_4>
<x_loc_4>67</x_loc_4>
<y_loc_4>76</y_loc_4>
<layer_4>1</layer_4>
</hud_display>
</hud_display_group>
</wp_hud_displays>
More than one record output
1test1_gauge001-2_zpsdbe86ca1f.jpg11PID_DISTANCE30100002500101223dfd34412sdf34534056Arail122test1_needle11PID_ENGINE_LOAD211000025200160112025000000002test1_needle11PID_ENGINE_LOAD211000025200160112025000000003new gaugedfsgsdfgfd435430PID_COOLANT_TEMP2434534541023324000000003new gaugedfsgsdfgfd435430PID_COOLANT_TEMP2434534541023324000000004Bla100.jpg2702334395910299000000004Bla100.jpg2702334395910299000000002test1_needle11PID_ENGINE_LOAD211000025200160112025000000001test display1080p118979129889813899014677612dsfdsfsd720p12321342323213224232123143423444342
Could it be something to do with the WHILE loop?, unable to create another set of "wp_hud_display.id" records
Very strange results in stripping the XML tags and leaving only the values on records after the first. Possibly, it may be due to the deprecated mysql() functions that Wordpress may no longer support.
Consider using MySQL PDO with a try/catch on the connection, iterating through the STH object. I also add a document output for you to see if contents differ between screen and file
Additionally, you could take advantage of a for loop across elements since tag names change only by the 1-4 instrument indicators:
<?php
// create a dom document with encoding utf8
$domtree = new DOMDocument('1.0', 'UTF-8');
//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "root";
$config['mysql_pass'] = "";
$config['db_name'] = "tasmanhud";
//connect to host
try {
$dbh = new PDO("mysql:host=$config['mysql_host'];dbname=$config['db_name']",$config['mysql_user'],$config['mysql_pass']);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//select all items in table
$sql = "SELECT
wp_hud_display.id,
wp_hud_display.name,
wp_hud_display.resolution,
wp_hud_display.user_id,
wp_hud_display.instrument_id_1,
wp_hud_display.x_loc_1,
wp_hud_display.y_loc_1,
wp_hud_display.layer_1,
wp_hud_display.instrument_id_2,
wp_hud_display.x_loc_2,
wp_hud_display.y_loc_2,
wp_hud_display.layer_2,
wp_hud_display.instrument_id_3,
wp_hud_display.x_loc_3,
wp_hud_display.y_loc_3,
wp_hud_display.layer_3,
wp_hud_display.instrument_id_4,
wp_hud_display.x_loc_4,
wp_hud_display.y_loc_4,
wp_hud_display.layer_4,
wp_hud_instrument.inst_id AS inst1_id,
wp_hud_instrument.name AS inst1_name,
wp_hud_instrument.image_file AS inst1_image,
wp_hud_instrument.font_type AS inst1_ft,
wp_hud_instrument.font_size AS inst1_fs,
wp_hud_instrument_1.inst_id AS inst2_id,
wp_hud_instrument_1.name AS inst2_name,
wp_hud_instrument_1.image_file AS inst2_image,
wp_hud_instrument_1.font_type AS inst2_ft,
wp_hud_instrument_1.font_size AS inst2_fs,
wp_hud_instrument_2.inst_id AS inst3_id,
wp_hud_instrument_2.name AS inst3_name,
wp_hud_instrument_2.image_file AS inst3_image,
wp_hud_instrument_2.font_type AS inst3_ft,
wp_hud_instrument_2.font_size AS inst3_fs,
wp_hud_instrument_3.inst_id AS inst4_id,
wp_hud_instrument_3.name AS inst4_name,
wp_hud_instrument_3.image_file AS inst4_image,
wp_hud_instrument_3.font_type AS inst4_ft,
wp_hud_instrument_3.font_size AS inst4_fs
FROM wp_hud_display
LEFT JOIN wp_hud_instrument
ON wp_hud_display.instrument_id_1 = wp_hud_instrument.inst_id
LEFT JOIN wp_hud_instrument wp_hud_instrument_1
ON wp_hud_display.instrument_id_2 = wp_hud_instrument_1.inst_id
LEFT JOIN wp_hud_instrument wp_hud_instrument_2
ON wp_hud_display.instrument_id_3 = wp_hud_instrument_2.inst_id
LEFT JOIN wp_hud_instrument wp_hud_instrument_3
ON wp_hud_display.instrument_id_4 = wp_hud_instrument_3.inst_id
WHERE wp_hud_display.user_id = 1
GROUP BY wp_hud_display.id";
$STH = $dbh->query($sql);
$STH->setFetchMode(PDO::FETCH_ASSOC);
}
catch(PDOException $e) {
echo $e->getMessage();
}
/* create the root element of the xml tree */
$xmlRoot = $domtree->createElement("wp_hud_displays");
/* append it to the document created */
$xmlRoot = $domtree->appendChild($xmlRoot);
$hud_display_group = $domtree->createElement("hud_display_group");
$hud_display_group = $xmlRoot->appendChild($hud_display_group);
while($row = $STH->fetch()) {
$hud_display = $domtree->createElement("hud_display");
$hud_display = $hud_display_group->appendChild($hud_display);
$instrument_id_1 = $domtree->createElement("instrument_id_1");
$instrument_id_1 = $hud_display->appendChild($instrument_id_1);
$instrument_id_2 = $domtree->createElement("instrument_id_2");
$instrument_id_2 = $hud_display->appendChild($instrument_id_2);
$instrument_id_3 = $domtree->createElement("instrument_id_3");
$instrument_id_3 = $hud_display->appendChild($instrument_id_3);
$instrument_id_4 = $domtree->createElement("instrument_id_4");
$instrument_id_4 = $hud_display->appendChild($instrument_id_4);
/* you should enclose the following two lines in a cicle */
$id = $domtree->createElement('id',$row['id']);
$hud_display->appendChild($id);
$name = $domtree->createElement('name',$row['name']);
$hud_display->appendChild($name);
$resolution = $domtree->createElement('resolution',$row['resolution']);
$hud_display->appendChild($resolution);
$user_id = $domtree->createElement('user_id',$row['user_id']);
$hud_display->appendChild($user_id);
// Instruments 1 - 4
for($i = 1; $i <= 4; $i++) {
${'instrument_id_'.$i} = $domtree->createElement('instrument_id_'.$i,$row['instrument_id_'.$i]);
$hud_display->appendChild(${'instrument_id_'.$i});
${'inst'.$i.'_id'} = $domtree->createElement('inst_id',$row['inst'.$i.'_id']);
${'instrument_id_'.$i}->appendChild(${'inst'.$i.'_id');
${'inst'.$i.'_name'} = $domtree->createElement('name',$row['inst'.$i.'_name']);
${'instrument_id_'.$i}->appendChild(${'inst'.$i.'_name'});
${'inst'.$i.'_image'} = $domtree->createElement('image_name',$row['inst'.$i.'_image']);
${'instrument_id_'.$i}->appendChild(${'inst'.$i.'_image'});
${'inst'.$i.'_ft'} = $domtree->createElement('font_type',$row['inst'.$i.'_ft']);
${'instrument_id_'.$i}->appendChild(${'inst'.$i.'_ft'});
${'inst'.$i.'_fs'} = $domtree->createElement('font_size',$row['inst'.$i.'_fs']);
${'instrument_id_'.$i}->appendChild(${'inst'.$i.'_fs'});
${'x_loc_'.$i} = $domtree->createElement('x_loc_'.$i,$row['x_loc_'.$i]);
$hud_display->appendChild(${'x_loc_'.$i});
${'y_loc_'.$i} = $domtree->createElement('y_loc_'.$i,$row['y_loc_'.$i]);
$hud_display->appendChild(${'y_loc_'.$i});
${'layer_'.$i} = $domtree->createElement('layer_'.$i,$row['layer_'.$i]);
$hud_display->appendChild(${'layer_'.$i});
}
}
// get the xml printed
echo $domtree->saveXML();
// saving xml to document
file_put_contents("XML_WP_Output.xml", $domtree->saveXML());
//send the xml header to the browser
header ("Content-Type:text/xml");
// closing db connection
$dbh = null;
?>
I've found out why Firefox has issues with the XML formatting when returning my query.
Turns out Firefox has a 4K XML node limit Firefox 4K XML Node Limit
When testing my query in IE, it works fine, and loading the output .xml file post query in Firefox is also fine.
That will teach me for not testing with different browsers!
Thanks for you help guys with this problem, I understand a bit more about XML now.