xml wont write right (php and psql)

[SeenGee]

i've had some good help on this forum in the last couple
of days but i still havent been able to make this work
yet. i'm trying to use php (4.06) to write to an xml document
usind data and fieldnames from a psql database. the php
script is generating xml, its writing the whole document
in fact but the structure is wrong. It prints a record for
correct number of fields but it repeats the details twice
and then appears to get stuck in a loop after it reaches the
second field. the other problem is that i still cannot get
the fieldnames inside the xml tags.

I really appreciate the help i've been given and any other
help people can offer to help me make this work.

<?php
$conn = pg_connect("");
if (!$conn)
{
    echo "<h1>Connection Error</h1>";
    exit;
}

$sql="SELECT * FROM flock;";
$result_set = pg_exec($conn, $sql);
$rows = pg_numrows($result_set);

for ($j=0; $j<$rows; $j++)
	{

list($flock_number,$flock_location) = pg_fetch_row($result_set, $j);
}

$file= fopen("flock.xml" , "w");

$_xml .="<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>\r\n";
$_xml .="<!-- table flock-->\r\n";
$_xml .="<flock>\r\n";
//$result_set = pg_Exec ($conn, $sql);

for ($j=0; $j<$rows; $j++) { 

$row = pg_fetch_array($result_set, $row); 
//$row = pg_fetch_object($result_set, $row);
   $_xml .="    <record>\r\n";
   foreach ($row as $data) {
      $_xml .="        <$fieldnames[$count]>$data</$fieldnames[$count]>\r\n";
      $count++;
           }
   $_xml .="    </record>\r\n";
}
//}
$_xml .="</flock>";

fwrite($file, $_xml);
fclose($file);
echo $_xml;
pg_close();
?>

when i try to generate the xml this error comes up:

A name was started with an invalid character. Error processing resource
'../newCheck.php'. Line 5, Position 10

    <>1</>

---------^

but when i view the source i see:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!-- table flock-->
<flock>
<record>
<>1</>
<>1</>
<>den</>
<>den</>
</record>
<record>
<>2</>
<>2</>
<>the barn</>
<>the barn</>
</record>
<record>
<>2</>
<>2</>
<>the barn</>
<>the barn</>
</record>

etc. for the number of fields i have.

Thanks again for the help.

[Tim_Brown]

I usually use MySQL, so couldn't test the following:

// get the number of rows
$num = pg_numrows($result_set);

// get the number of columns
$col_num = pg_numfields($result_set);

for ($j=0; $j<$num; $j++) {
// get each row of data and assign values to an array
$line = pg_fetch_row($result_set, $j);

// start each record tag
$_xml .=" <record>\r\n";

// need to output each field from the array
for ($count=0; $count<$col_num; $count++){
$xml .=" <$line[$count]>$data</$line[$count]>\r\n";
}
// close the record tag
$xml .=" </record>\r\n";
}
$_xml .="</flock>";

The above goes below the line: $_xml .="<flock>\r\n";
You can see from the last line shown where it should end.

Also, not sure what the first FOR loop does since you never use
either $flock_number or $flock_location in the script.

[SeenGee]

cheers for the reply, the code is now using the data from the db to name the xml tags where as what i want is for the column names (flock number, flock location) to be the names of the tags and for the data to go inside these tags.

Thanks.

[Tim_Brown]

How about this for the tag names:

$flock_number = pg_field_name ($result_set, 0);
$flock_location = pg_field_name ($result_set, 1);

And then create the tags with:

// start each record tag
$_xml .=" <record>\r\n";

// output each field from the array
$xml .=" <$flock_number>$line[0]</$flock_number>\r\n";
$xml .=" <$flock_location>$line[1]</$flock_location>\r\n";

// close the record tag
$_xml .=" </record>\r\n";

[SeenGee]

cheers for all the help, it really is miuch appreciated. i'm afraid there is still a problem though. firstly the output is not being recognised as xml, it is just being listed across the screen as plain text, secondly every piece of data and tag is printed twice.
i dont know maybe if i have misunderstood some of your help. this is the code i have now:

<?php
$conn = pg_connect("");
if (!$conn)
{
    echo "<h1>Connection Error</h1>";
    exit;
}

$sql="SELECT * FROM flock;";
$result_set = pg_exec($conn, $sql);
$rows = pg_numrows($result_set);

$flock_number = pg_fieldname ($result_set, 0); 
$flock_location = pg_fieldname ($result_set, 1); 

$file= fopen("flock.xml" , "w");

$_xml .="<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>\r\n";
$_xml .="<!-- table flock-->\r\n";
$_xml .="<flock>\r\n";
$num = pg_numrows($result_set); 

// get the number of columns 
$col_num = pg_numfields($result_set); 

for ($j=0; $j<$num; $j++) { 
// get each row of data and assign values to an array 
$line = pg_fetch_row($result_set, $j); 

// start each record tag 
$_xml .=" <record>\r\n"; 

// need to output each field from the array 
for ($count=0; $count<$col_num; $count++){ 
// output each field from the array 
$_xml .=" <$flock_number>$line[0]</$flock_number>\r\n"; 
$_xml .=" <$flock_location>$line[1]</$flock_location>\r\n"; 

} 
// close the record tag 
$_xml .=" </record>\r\n"; 
} 
$_xml .="</flock>"; 

fwrite($file, $_xml);
fclose($file);
echo $_xml;
pg_close();
?>

Thanks again.

[Tim_Brown]

What are you using to view the file? I can't run the code at this
time, but it should be working as long as the output in your file
looks like this:

<?xml version="1.0" encoding="iso-8859-1" ?>
<!-- table flock -->
<flock>
<record>
<number>1</number>
<location>den</location>
</record>
<record>
<number>2</number>
<location>the barn</location>
</record>
</flock>

For the tags printing twice, I would first check the database to
ensure the rows are not repeated there. If the data looks good,
then try adding a line in the for loop like:
$_xml .=" <p>j: $j</p>";

That should show us how many times the loop is iterating.

[SeenGee]

my stupid mistake i'm afraid, i was viewing a cached copy of the page and hadnt noticed, sleep deprivation has strange effects. the data in the db is definitely not repeated. it appears that it is iterating twice through each piece of data.

Other than that it is displaying as it should.

Cheers

[Tim_Brown]

Can't believe I over looked this, but you don't need the last For loop.

Change:
// need to output each field from the array
for ($count=0; $count<$col_num; $count++){
// output each field from the array
$xml .=" <$flock_number>$line[0]</$flock_number>\r\n";
$xml .=" <$flock_location>$line[1]</$flock_location>\r\n";

}

To:
// output each field from the array
$xml .=" <$flock_number>$line[0]</$flock_number>\r\n";
$xml .=" <$flock_location>$line[1]</$flock_location>\r\n";

[SeenGee]

of course, should have seen that myself, much respect for all the help. IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!