Hiya!
I am trying to pull out a record which is joined from two tables in my database using an inner join query.
I am having trouble pulling out data from one field named "ArticleEntry" for two records. This is confusing me. I am not sure if this is a php, xml or sql issue and I am not sure how to go about debugging it.
This is the code to convert the sql to xml:
<?php
include ('common_db.php');
$link_id = db_connect();
//-------------------------------------------------------------
//Do inner join query to pull out all combined rows
//--------------------------------------------------------------
$tablename1 = "Article";
$tablename2 = "Image";
$query = "SELECT Article.ArticleTitle, Article.ArticleDate, Article.ArticleEntry, Image.ImageLoc, Image.ImageDescription FROM $tablename1, $tablename2 WHERE Article.ArticleImage = Image.ImageID";
$result = mysql_query($query, $link_id);
//------------------------------------------------------------------
//Create Dom object to transfer data to xml document
//-----------------------------------------------------------------
$doc = new DomDocument('1.0');
//---------------------
//create root node
//----------------------
$root = $doc->createElement('News');
$root = $doc->appendChild($root);
//----------------------
//Start adding data
//----------------------
while($row = mysql_fetch_assoc($result)) {
//-----------------------------
//add node for each row
//-----------------------------
$occ = $doc->createElement($tablename1);
$occ = $root->appendChild($occ);
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;
?>
This is the output:
<?xml version="1.0"?>
<News>
<Article>
<ArticleTitle>Affordable Housing Strategy for Dingley Dell</ArticleTitle>
<ArticleDate>3rd April 2006</ArticleDate>
<ArticleEntry></ArticleEntry>
<ImageLoc>/images/house.jpg</ImageLoc>
<ImageDescription>Housing site for affordable housing provisions scheme</ImageDescription>
</Article>
<Article>
<ArticleTitle>Schools take the easter egg challenge</ArticleTitle>
<ArticleDate>9th April 2006</ArticleDate>
<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>
<ImageLoc>'/images/easteregg.jpg'</ImageLoc>
<ImageDescription>Cadbury's Easter Egg</ImageDescription>
</Article>
<Article>
<ArticleTitle>New sports facility in Dingley Dell</ArticleTitle>
<ArticleDate>9th April 2006</ArticleDate>
<ArticleEntry></ArticleEntry>
<ImageLoc>'/images/sportscentre.jpg'</ImageLoc>
<ImageDescription>Armitage sports centre, in Dingley Dell</ImageDescription>
</Article>
</News>
As you notice, in the second of the "ArticleEntry" nodes, there is content contained inside of it.
But the other two nodes don't seem to have the relevant 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, or any ideas on how I can debug this?
Cheers!
