Harrison Hernandez Harrison Hernandez - 5 months ago 15
PHP Question

How to retrieve mysql table and output to xml but using variable in URL

This script can pull data from mysql table then export it to xml

How can i retrieve msgid by passing variable to the URL and output to XML

This should be easy but i cant seem to get it right.

It should be like that:

http://localhost/test.php?msgid=d503dba6-44b8-4ba0-ae9a-8d6743a914ee

<data>
<acceptreport>
<id>4692</id>
<username>test1</username>
<msgid>d503dba6-44b8-4ba0-ae9a-8d6743a914ee</msgid>
<sender>TOPS</sender>
<receiver>523452345</receiver>
<acceptedfordeliverytime>2016-06-21 09:04:16</acceptedfordeliverytime>
<deliveredtohandsettime>2016-06-21 09:04:00</deliveredtohandsettime>
<operator>AIRLINK</operator>
<status>deliveredtohandset</status>
</acceptreport>
</data>


This is the script

<?php
//database configuration
$config['mysql_host'] = "192.168.1.1";
$config['mysql_user'] = "test";
$config['mysql_pass'] = "pass#word";
$config['db_name'] = "testdb";
$config['table_name'] = "box";

//connect to host
mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);
//select database
@mysql_select_db($config['db_name']) or die( "Unable to select database");

/**
* @param mysql_resource - $queryResult - mysql query result
* @param string - $rootElementName - root element name
* @param string - $childElementName - child element name
*/
function sqlToXml($queryResult, $rootElementName, $childElementName)
{
$xmlData = "<?xml version=\"1.0\" encoding=\"ISO-8859-1\" ?>\n";
$xmlData .= "<" . $rootElementName . ">";

while($record = mysql_fetch_object($queryResult))
{
/* Create the first child element */
$xmlData .= "<" . $childElementName . ">";

for ($i = 0; $i < mysql_num_fields($queryResult); $i++)
{
$fieldName = mysql_field_name($queryResult, $i);

/* The child will take the name of the table column */
$xmlData .= "<" . $fieldName . ">";

/* We set empty columns with NULL, or you could set
it to '0' or a blank. */
if(!empty($record->$fieldName))
$xmlData .= $record->$fieldName;
else
$xmlData .= "null";

$xmlData .= "</" . $fieldName . ">";
}
$xmlData .= "</" . $childElementName . ">";
}
$xmlData .= "</" . $rootElementName . ">";

return $xmlData;
}

/* Sql query */
$result = mysql_query("SELECT id, username, msgid, sender, receiver, acceptedfordeliverytime, deliveredtohandsettime, operator, status FROM outbox WHERE username = 'top2' ORDER BY id DESC LIMIT 1");

/* If you want to process the returned xml rather than send it
to the browser, remove the below line.
*/
header("Content-Type: application/xml");
echo sqlToXml($result, "data", "acceptreport");
?>

Answer

You're passing in the wrong variable to get the data in the where clause.

$result = mysql_query("SELECT id, username, msgid, sender, receiver, acceptedfordeliverytime, deliveredtohandsettime, operator, status FROM outbox  WHERE username = 'burs2' ORDER BY id DESC LIMIT 1");

You need to pass in the username in the url to get your dynamic data

$result = mysql_query("SELECT id, username, msgid, sender, receiver, acceptedfordeliverytime, deliveredtohandsettime, operator, status FROM outbox  WHERE username = '".$_REQUEST['username']."' ORDER BY id DESC LIMIT 1");

You're not pulling data by the msgid. The url you should be calling should be the following

http://localhost/test.php?username=test1