douzhu1188 2015-08-25 20:26
浏览 40

返回超过1条记录时，xml格式被删除

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

  • 写回答

2条回答 默认 最新

  • duanliang2017 2015-08-26 00:10
    关注

    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;

?>
    评论

报告相同问题？

悬赏问题

  • ¥20 数学建模，尽量用matlab回答，论文格式
  • ¥15 昨天挂载了一下u盘，然后拔了
  • ¥30 win from 窗口最大最小化，控件放大缩小，闪烁问题
  • ¥20 易康econgnition精度验证
  • ¥15 msix packaging tool打包问题
  • ¥28 微信小程序开发页面布局没问题，真机调试的时候页面布局就乱了
  • ¥15 python的qt5界面
  • ¥15 无线电能传输系统MATLAB仿真问题
  • ¥50 如何用脚本实现输入法的热键设置
  • ¥20 我想使用一些网络协议或者部分协议也行，主要想实现类似于traceroute的一定步长内的路由拓扑功能