Jump to content

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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.