PHP: SQL to XML

027th Aug 2010Development, PHP, ,

MySQLI posted this on the CyphersTECH blog a little over a year ago, and thought I’d go ahead and post it here, as well. It doesn’t really do anything exceptionally wonderful, just converts the results of an SQL query to usable XML. It was originally written for a FLEX application, so that explains the references to FLEX within the code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
 
<?php
// Database configuration.
define("db_host", "localhost");
define("db_user", "some_user");
define("db_pass", "mypass");
define("db_name", "mydb");
 
// Authenticate calling application
define("auth_user", "someUserName");
define("auth_pass", "someUniquePhrase");
 
//Set our header…
header("Content-type: text/xml"); 
// and send our first line…
?>
<?xml version=1.0?>
<?php
 
// Kick things off…
user_authenticate();
 
function db_connect ()
{
mysql_connect(db_host,db_user,db_pass);
mysql_select_db(db_name);
}
 
function print_error($error)
{
// We print the error as XML so that the FLEX app can handle these problems.
echo("<flexsqlresult>");
echo("<error>" . $error . "</error>");
echo("</flexsqlresult>");
}
 
function user_authenticate ()
{
// Make sure they send user and pass data that matches our globals defined above.
if(isset($_REQUEST['authuser']) && isset($_REQUEST['authpass']))
{
if(($_REQUEST['authuser'] == auth_user) && ($_REQUEST['authpass'] == auth_pass))
{
if(isset($_REQUEST['qry']))
{
// Everything checks out, let’s run the query.
run_Query(trim(urldecode($_REQUEST['qry'])));
} else {
// The query was empty, error.
print_error("Bad query");
}
} else {
// Their user/pass info didn’t match, error.
print_error("Authentication invalid");
}
} else {
// No user/pass info, error.
print_error("No authentication specified.");
}
}
 
function run_Query ($query)
{
// Connect to our database…
db_connect();
// Run our query…
$resultID = mysql_query($query) or print_error("SQL Error: " . mysql_errno($resultID) . " – " . mysql_error($resultID));
// Start XML output…
echo("<flexsqlresult>");
$rowcount = 0;
while ($row = mysql_fetch_array($resultID))
{
echo("<datarow" . $rowcount . ">");
foreach($row as $key=>$value)
{
// Make sure the column name isn’t numeric, otherwise the XML will be invalid.
if(is_integer($key) != true)
{
/// Spit out our Name/Value row.
echo("<" . $key . ">" . $value . "</" . $key . ">");
}
}
echo("</datarow" . $rowcount . ">");
$rowcount++;
}
echo("</flexsqlresult>");
}
?>

I’m sure there are better ways to do parts of this, and I’d love to hear your thoughts.

No Comments Comments Feed

Add a Comment

Before you post, please prove you are sentient.

what is 5 in addition to 8?