0

Thank you for the advice on using the "DOMDocument" approach, creating the elements and then append child appears to work well for what I am after.

Here is what I have come up with, and it appears to work for returning one record, however if there is more than one record (wp_hud_display.id) in the database it breaks the XML, would "foreach" work in the while loop? and how would I add it to my code?

`

<?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);

$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);

    $result = mysql_query($sql);
    if (!$result) {
        die('Invalid query: ' . mysql_error());
    }

    if(mysql_num_rows($result)>0)
    {
       while($result_array = mysql_fetch_assoc($result))
       {

    /* 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");


    //output the XML data

echo $xml;
?>`
Dan
  • 9
  • 3
  • `str_pad("\t", $level)` of course you have to track the level, and use ` ' instead for html, ++$level; --$level; Recursive function work good for this. – ArtisticPhoenix Aug 07 '15 at 00:39
  • It is probably a possible solution that you tidy your XML content before serving. This way you let the indentantion (formatting) logic isolated from your code and you dont have to worry about that. http://stackoverflow.com/questions/8615422/php-xml-how-to-output-nice-format – alariva Aug 07 '15 at 02:04
  • 2
    Use a XML library to build XML, don't smash strings together. That way lies pain. – Quentin Aug 07 '15 at 16:01
  • The first mistake I can see is, that you create the XML by concatenating strings (you do it so to say "by hand"). Use an XML library instead. For example if you take [**XMLWriter**](http://www.php.net/xmlwriter) you get the indentation for free. / edit: Reference question [How to generate XML file dynamically using PHP?](http://stackoverflow.com/q/486757/367456) – hakre Aug 07 '15 at 18:12
  • I'll give it a try, I'm aiming for the sub lists after the different instrument settings in the sql query, to be formatted correctly, as it'll be used by an android app as a settings file. – Dan Aug 09 '15 at 23:21

1 Answers1

0

Why not simply add space to the concatenated $xml variable? Remember all you are doing is writing the text of xml content and outputting to screen or file.

XML is not being browser interpreted yet to say remove whitespaces. Once you save your $xml string to file, indentation will appear.

  $xml.="<id>".$result_array['id']."</id>";
  $xml.="<name>".$result_array['name']."</name>";
  $xml.="<resolution>".$result_array['resolution']."</resolution>";
  $xml.="<user_id>".$result_array['user_id']."</user_id>";
  $xml.="<instrument_id_1>".$result_array['instrument_id_1']."</instrument_id_1>";
  $xml.="      <inst_id>".$result_array['inst1_id']."</inst_id>";
  $xml.="      <name>".$result_array['inst1_name']."</name>";
  $xml.="      <image_file>".$result_array['inst1_image']."</image_file>";
  $xml.="      <user_id>".$result_array['inst1_user_id']."</user_id>";
  $xml.="      <data_source>".$result_array['inst1_data_source']."</data_source>";
          ........................
  $xml.="<x_loc_1>".$result_array['x_loc_1']."</x_loc_1>";
  $xml.="<y_loc_1>".$result_array['y_loc_1']."</y_loc_1>";
  $xml.="<layer_1>".$result_array['layer_1']."</layer_1>";
  $xml.="<instrument_id_2>".$result_array['instrument_id_2']."</instrument_id_2>";
  $xml.="      <inst_id>".$result_array['inst2_id']."</inst_id>";
  $xml.="      <name>".$result_array['inst2_name']."</name>";
  $xml.="      <image_file>".$result_array['inst2_image']."</image_file>";
  $xml.="      <user_id>".$result_array['inst2_user_id']."</user_id>";
  $xml.="      <data_source>".$result_array['inst2_data_source']."</data_source>";
          .........................
  $xml.="      <font_size>".$result_array['inst2_fs']."</font_size>";
  $xml.="<x_loc_2>".$result_array['x_loc_2']."</x_loc_2>";
  $xml.="<y_loc_2>".$result_array['y_loc_2']."</y_loc_2>";
  $xml.="<layer_2>".$result_array['layer_2']."</layer_2>";

  //Screen output of XML data
  echo $xml;

  // Save output to file
  file_put_contents("File.xml", $xml);
Parfait
  • 104,375
  • 17
  • 94
  • 125