Jump to content

Archived

This topic is now archived and is closed to further replies.

lyndsey.pearce@ntlworld.com

Using PHP to create XML files from SQL data

Recommended Posts

Hiya!
I am trying to pull out a record from one table in my database.
I am having trouble pulling out data from one field named "Article Entry" for two records.

This is the code to convert the sql to xml:

[code]
<?php

include ('common_db.php');


         $link_id = db_connect();


$table_id = 'Article';
$query = "SELECT * FROM $table_id";
$dbresult = mysql_query($query, $link_id);


//-----------------------------
//create a new XML document
//-----------------------------

$doc = new DomDocument('1.0');


//------------------------
//create root node
//------------------------

$root = $doc->createElement('News');
$root = $doc->appendChild($root);


//----------------------------------
//process one row at a time
//-----------------------------------

while($row = mysql_fetch_assoc($dbresult)) {


  //----------------------------
  //add node for each row
  //----------------------------

  $occ = $doc->createElement($table_id);
  $occ = $root->appendChild($occ);


  //--------------------------------
  //add a child node for each field
  //---------------------------------

  foreach ($row as $fieldname => $fieldvalue) {


    $child = $doc->createElement($fieldname);
    $child = $occ->appendChild($child);


    $value = $doc->createTextNode($fieldvalue);
    $value = $child->appendChild($value);


  } // foreach
} // while


//-------------------------------
//get completed xml document
//-------------------------------

$xml_string = $doc->saveXML();


echo $xml_string;
?>
[/code]

This is the output:

[code]
<?xml version="1.0"?>
<News>
<Article>
<ArticleID>1</ArticleID>
<ArticleSubmitter>1</ArticleSubmitter>
<ArticleSubmitDate>4th April 2006</ArticleSubmitDate>
<ArticleLastUpdated>2006-04-24 15:43:19</ArticleLastUpdated>
<ArticleTitle>Affordable Housing Strategy for Dingley Dell</ArticleTitle>
<ArticleDate>3rd April 2006</ArticleDate>
[b]<ArticleEntry></ArticleEntry>[/b]
<ArticleImage>2</ArticleImage>
</Article>

<Article>
<ArticleID>2</ArticleID>
<ArticleSubmitter>1</ArticleSubmitter>
<ArticleSubmitDate>10th April 2006</ArticleSubmitDate>
<ArticleLastUpdated>2006-04-24 15:53:04</ArticleLastUpdated>
<ArticleTitle>Schools take the easter egg challenge</ArticleTitle>
<ArticleDate>9th April 2006</ArticleDate>
[b]<ArticleEntry>Do you love chocolate? Can't wait for your Easter egg? The young people at Dingley Dell's schools are starting Easter early this year by helping trading standards officers test a range of Easter eggs.
The pupils from various schools across the borough will be testing the eggs for weight, packaging, price, value for money and the all important taste.
The results of this testing will be presented in an eye-catching poster to be judged by a panel of trading standards experts.
Elle (11) from All Saints Catholic Primary School -last year's winning team - said: "We tested the eggs and evaluated the information we found using colourful graphs and charts, my favourite part of the project was testing for value for money."
The Dingley Dell winners will take part in the Greater Manchester final.
</ArticleEntry>[/b]
<ArticleImage>4</ArticleImage>
</Article>

<Article>
<ArticleID>3</ArticleID>
<ArticleSubmitter>2</ArticleSubmitter>
<ArticleSubmitDate>10th April 2006</ArticleSubmitDate>
<ArticleLastUpdated>2006-04-24 16:14:33</ArticleLastUpdated>
<ArticleTitle>New sports facility in Dingley Dell</ArticleTitle>
<ArticleDate>9th April 2006</ArticleDate>
[b]<ArticleEntry></ArticleEntry>[/b]
<ArticleImage>5</ArticleImage>
</Article>
</News>
[/code]

As you notice, in the second record the "Article Entry" has content contained inside of it, but the other two records don't seem to pull out the content from the database.

I have double checked these records. In PHPMyAdmin, all three article records have an entry.

Does anyone have any ideas as to what I am doing wrong?


Cheers!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.