在PHP中使用MySQL查询创建XML结构

Question

I have 2 columns in a table im trying to pull rows out of. Im building an xml file and can't get the structure right with my requirements.

Here is what i have working

<?php

if (!empty($_SERVER['REMOTE_USER'])) {
    $user = $_SERVER['REMOTE_USER'];
} else {
    $user = $_SERVER['HTTP_CLIENT_IP'];
}

//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "*****";
$config['mysql_pass'] = "*****";
$config['db_name']    = "*****";
$config['table_name'] = "user";

//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");

$xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
$xml .= "<resources>";
$xml .= "
";

//select all items in table
$sql = "SELECT activity_full FROM user where user = '$user'";
$sqlname = "SELECT name FROM user where user = '$user'";

$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

$resultname = mysql_query($sqlname);
if (!$resultname) {
    die('Invalid query: ' . mysql_error());
}

if(mysql_num_rows($result)>0){

    while($result_array = mysql_fetch_assoc($result)){

        foreach($result_array as $key => $value){

            $xml .= "<item component=\"ComponentInfo{";

            $xml .= "$value";

            $xml .= "}\" drawable=\"";

            $result_array_name = mysql_fetch_assoc($resultname);
                foreach($result_array_name as $key => $valuename){

                    $xml .= "$valuename";

            }

            $xml .= "\" />";
            $xml .= "
";
            $xml .= "
";
        }
    }
}

$xml .= "</resources>";

//send the xml header to the browser
header ("Content-Type:text/xml");

//output the XML data
echo $xml;



file_put_contents("export/".$user."_appfilter.xml", $xml);

?>

That produces this

<item component="ComponentInfo{com.apps.aaa.roadside/com.apps.aaa.roadside.Splash}" drawable="aaa_roadside1" />

<item component="ComponentInfo{com.aaa.android.triptik/com.aaa.android.triptik.Bootstrap}" drawable="aaa_triptik" />

<item component="ComponentInfo{au.com.phil.abduction2.demo/au.com.phil.abduction2.demo.menus.PsymIntro}" drawable="abduction" />

<item component="ComponentInfo{au.com.phil.abduction2/au.com.phil.abduction2.menus.PsymIntro}" drawable="abduction" />

<item component="ComponentInfo{au.com.phil/au.com.phil.Intro}" drawable="abduction" />

What i need is to apply a header and group items with the same name. Like this

<!-- aaa_roadside1 -->
<item component="ComponentInfo{com.apps.aaa.roadside/com.apps.aaa.roadside.Splash}" drawable="aaa_roadside1" />

<!-- aaa_triptik -->
<item component="ComponentInfo{com.aaa.android.triptik/com.aaa.android.triptik.Bootstrap}" drawable="aaa_triptik" />

<!-- abduction -->
<item component="ComponentInfo{au.com.phil.abduction2.demo/au.com.phil.abduction2.demo.menus.PsymIntro}" drawable="abduction" />
<item component="ComponentInfo{au.com.phil.abduction2/au.com.phil.abduction2.menus.PsymIntro}" drawable="abduction" />
<item component="ComponentInfo{au.com.phil/au.com.phil.Intro}" drawable="abduction" />

Answer 1

Here's the solution without all the MySQL stuff.

I've translated it as best I could into your existing code, resulting in the below. It should work as-is, but since I'm not connecting to your db, I can't say with absolute certainty.

That being said, you really, really need to stop using the mysql_* functions and start using either PDO or mysql. And use bound parameters in your queries instead of doing interpolation.

<?php

if (!empty($_SERVER['REMOTE_USER'])) {
    $user = $_SERVER['REMOTE_USER'];
} else {
    $user = $_SERVER['HTTP_CLIENT_IP'];
}

//database configuration
$config['mysql_host'] = "localhost";
$config['mysql_user'] = "*****";
$config['mysql_pass'] = "*****";
$config['db_name']    = "*****";
$config['table_name'] = "user";

//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");

$xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";
$xml .= "<resources>";
$xml .= "
";

//select all items in table
// get both columns in just one query
$sql = "SELECT activity_full, name FROM user where user = '$user'";

$result = mysql_query($sql);
if (!$result) {
    die('Invalid query: ' . mysql_error());
}

if(mysql_num_rows($result)>0){

    while($row = mysql_fetch_assoc($result)){

        if(!isset($previousRow) || !isset($previousRow["name"]) || $previousRow["name"] != $row["name"])
        {
            $xml .= "<!-- " . $row['name'] . " -->
";
        }
        $xml .= "<item component=\"ComponentInfo{";

        $xml .= $row["activity_full"];

        $xml .= "}\" drawable=\"";

        $xml .= $row["name"];

        $xml .= "\" />";
        $xml .= "
";
        $xml .= "
";

        $previousRow = $row;
    }
}

$xml .= "</resources>";

//send the xml header to the browser
header ("Content-Type:text/xml");

//output the XML data
echo $xml;

file_put_contents("export/".$user."_appfilter.xml", $xml);

?>