I have a table with this query, i set code=SPEED and code=PSTN.in this query, i set code=SPEED :
SELECT ID,NAME,xs1 as download, xs2 as upload
FROM sc_params
WHERE rfen = 'SERVICE_REQUESTED'
AND code = 'SPEED'
OR code IN
(
SELECT code
FROM sc_params
WHERE rfen = 'SERVICE_REQUESTED'
AND id = (
SELECT parent
FROM sc_params
WHERE rfen = 'SERVICE_REQUESTED'
AND code = 'SPEED' )
);
and the result is:
ID NAME DOWNLOAD UPLOAD
-----------------------------------
1 Internet null null
6 SPEED 1024 256
if i set code=PSTN,this is the result:
ID NAME DOWNLOAD UPLOAD
-----------------------------------
1 VOICE null null
6 PSTN null null
I add this query to PHP, and here's the code:
$serviceName="PSTN, SPEED";
public function get_xml_product($serviceName) {
$product = Array();
$idx = -1;
$explodeResult=explode(", ",$serviceName);
foreach($explodeResult as $value)
{
$sql = "SELECT id,parent,code,name,xs1, xs2
FROM sc_params
WHERE rfen = 'SERVICE_REQUESTED'
AND code = '".$value."'
OR code IN
(
SELECT code
FROM sc_params
WHERE rfen = 'SERVICE_REQUESTED'
AND id = (
SELECT parent
FROM sc_params
WHERE rfen = 'SERVICE_REQUESTED'
AND code = '".$value."' )
)";
$stmt = oci_parse($this->_conn,$sql);
$rs = oci_execute($stmt);
if (!$rs) {
$error = oci_error();
$this->_err = $error;
return false;
}
while ($data = oci_fetch_array($stmt, OCI_BOTH)) {
if($data['PARENT'] == 0) {
$idx++;
$idx2 = 0;
$product[$idx]['id'] = $data['ID'];
$product[$idx]['name'] = $data['NAME'];
}
}
}
//to make xml
$xml = new SimpleXMLElement("<services/>");
foreach($product as $key => $value) {
if (is_array($value)) {
$subnode = $xml->addChild("service");
$this->array_to_xml($value, $subnode);
} else {
$xml->addChild(htmlspecialchars("$key"),htmlspecialchars("$value"));
}
}
$string = $xml->asXML();
$result = htmlentities($string);
oci_free_statement($stmt);
oci_close($this->_conn);
return $result;
}
i make function array to xml also, here's it:
public function array_to_xml($product, &$xml) {
foreach($product as $key => $value) {
if (is_array($value)) {
$subnode = $xml->addChild("service");
array_to_xml($value, $subnode);
} else {
$xml->addChild(htmlspecialchars("$key"),htmlspecialchars("$value"));
}
}
return $xml;
}
from code above, i got the result in xml:
<xml version=1.0>
<services>
<service>
<id> 2 </id>
<name> VOICE </name>
</service>
<service>
<id> 1 </id>
<name> INTERNET </name>
</service>
</services>
but,the result i want to show is like this:
<?xml version="1.0"?>
<services>
<service>
<id>1</id>
<name>INTERNET</name>
<attribute>
<name>DOWNLOAD</name>
<value>1024</value>
</attribute>
<attribute>
<name>UPLOAD</name>
<value>512</value>
</attribute>
</service>
<service>
<id>2</id>
<name>VOICE</name>
</service>
</services>
No comments:
Post a Comment