PHP XML data to table

tempe · #1 (**permalink**) 01-06-12, 02:15

Hello

First of all, Sorry I'm PHP's noob.
I'm completely new in PHP.

I want to know how to retrieve XML and display the result in table in PHP.

Supposed my XML data are stored in "X" table, "Y" row.
And the XML data structure in "Y" row as follow.
<XML id="">
<A></A>

<C></C>
</XML>

I want to list out data from so that the result will be in table as follow.

A B C
data1 data1 data1
data2 data2 data2

How can I do that in php.
Thanks

Peter.Vanroose · #2 (**permalink**) 01-07-12, 05:01

Where do data1 and data2 come from?
What does this have to do with DB2?

tempe · #3 (**permalink**) 01-07-12, 08:27

I'm sorry for the lack information.

I'm using DB2 Express-C on debian server and developing PHP application.

The X table has 2 rows. Z and Y rows. The Z row stores id while the Y row stores XML data in native (using DB2 purexml function).

I want to retrieve all the XML data from the Y row and represent it in table.

The XML data structure in "Y" row as follow.
For example.
<XML id="1">
<A>data1</A>
data2
<C>data3</C>
</XML>

<XML id="2">
<A>data4</A>
data5
<C>data6</C>
</XML>

.
.
.

<XML id="n">
<A>datax</A>
datay
<C>dataz</C>
</XML>

I want to list out data so that the result will be in table as follow.

A B C
data1 data2 data3
data4 data5 data6
.
.
.
datax datay dataz

How can I do that in php.
Thanks

Peter.Vanroose · #4 (**permalink**) 01-07-12, 17:13

Not 100% sure if I understood your database design correctly, but it could be something in the following style:

Code:

<html><body><table><th><td>A</td><td>B</td><td>C</td></th>
<?php
 $database = 'SAMPLE'; $user = 'db2inst1'; $password = 'ibmdb2';
 $conn = db2_connect($database, $user, $password);
 $sql =
 'SELECT  XMLQUERY(\'$Y/A\' PASSING Y) AS a,
          XMLQUERY(\'$Y/B\' PASSING Y) AS b,
          XMLQUERY(\'$Y/C\' PASSING Y) AS c
  FROM x';
$stmt = db2_prepare($conn, $sql);
$result = db2_execute($stmt);
while ($row = db2_fetch_array($stmt)) {
    print "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
} ?>
</table></body></html>

For the DB2 bindings in PHP, and how to install, see PHP: IBM DB2 Functions - Manual

tempe · #5 (**permalink**) 01-09-12, 19:27

Thanks for the reply.

However I got this errors.
Warning: db2_execute(): Statement Execute Failed in XXX.php on line 17 Warning: db2_fetch_array(): Column information cannot be retrieved in XXX.php on line 18

The errors occurred on
(line 17) $result = db2_execute($stmt);
(line 18) while ($row = db2_fetch_array($stmt)) {

I guess there is something wrong on sql statement.

Peter.Vanroose · #6 (**permalink**) 01-10-12, 06:33

Quote:

Originally Posted by tempe

I guess there is something wrong on sql statement.

In that case, first try the SQL statement (literally, as it would be passed by PHP) in a "normal" DB2 interface, and modify it until it works. There could, e.g., be a problem with the table or column name(s). In my example the table is called "X" and the column is called "Y"; you'll possibly have to replace those.

Internet Services

Web Development

Digital Marketing

Consumer Tech