trying to figure this out. mysql/php/xml

[billylh]

im setting up a small database(still pretty new to it all) and need to be able to build an xml document using php from its data.

as an example heres what i need the xml format to look like.


<?xml version="1.0" encoding="iso-8859-1"?>
<myauto>
<make label="Ford">
<model label="Focus" id="3"/>
<model label="Taurus" id="5"/>
<model label="Mustang" id="6"/>
</make>
<make label="Chevrolet">
<species label="Silverado" id="7"/>
<species label="Cobalt" url="8"/>
</make>
<make label="Toyota">
<model label="Tundra" id="9"/>
</make>

</myauto>right now i have 2 tables, one MAKE, one MODEL
_______________
|_____table1 ____ |
|_id_|___make___|
|_1_|___ford_____|

_______________________
|_____table2___|________ |
|_id_|_makeid_|__model _|
|_3_|___1_____|_ focus ___|
|_5_|___1_____|_ taurus __|
|_6_|___1_____|_ mustang |

my problem is that i do not understand how to setup the database and use PHP to be able to set the MAKE as a node with multiple MODEL child nodes. Im not sure if this is something thats doable with just one table? or if i need to create a db table for each MODEL with the MAKE ID in it? basically to set up MAKE as a category for MODEL.

and just so everyone is aware of what this is actually for. I want to be able to have the data appear in a flash tree component, as a menu, when clicked on, i want it to pop up with information about that model car.

any help is appreciated.

thanks,
Billy

[Charleh]

You are on the right track with your table structure

The tables are fine as you have your parent node 'make' and your child nodes 'model'.

You can potentially use one table but it's a pain and it's not really the way databases are designed to work.

From a database standpoint everything is 'set up' already. The relationships between the tables might not be apparent to the database itself but you know what you want and with the right queries you can get this data.

SELECT * FROM table1

will give you the makes

SELECT * FROM table2

will give you the models. You can do this and get the data into two PHP result sets and write a loop to go through each one. It works, but the relationship between the data will be done via code on the PHP server.

A better way of doing it is to use a join as databases are designed to allow relationships between data to be formed easily

SELECT * FROM table1 INNER JOIN table2 on table1.id = table2.makeid

Now you will have a list of makes/models in a single result set. You can then use PHP to once again loop through the results and output the list but this time less processing is necessary.

Your PHP would look something like



// Write the XML header
echo "<your XML headers etc";

$result = mysql_query("SELECT * FROM table1 INNER JOIN table2 on table1.id = table2.makeid");

$currentid = 0;

while($row = mysql_fetch_array($result)) {
// While we have rows - store the id
if($currentid != $row["id"]) {
if($currentid != 0) {
// Write 'make' footer
echo "</make>";
}
$currentid = $row["id"];
// Write 'make' header
echo "<make blah blah=". $row["make"] .">";
}

// write the models
echo "<model ". $row["model"] . "></model>";
}

[billylh]

Thank you Charleh, you got me in the right direction.

heres my actual code now(what i am actually using it for and without the db connection parts), i made some modifications to it to get it to format the xml correctly. the db tables are essentially the same except make is genus and model is species



$dbconnect = mysql_connect($host, $user, $pass) or die("Connection error");
mysql_select_db($database, $dbconnect) or die("Database error");

$query = "SELECT * FROM genus INNER JOIN cpcentral on genus.id = cpcentral.genusid";

$xml_output = "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
$xml_output .= "<cps>\n";

$result = mysql_query($query, $dbconnect) or die("No data");

$currentid = 0;

while($row = mysql_fetch_array($result)) {
// While we have rows - store the id
if($currentid != $row["genus.id"]) {

if($currentid != 0) {
// Write 'genus' footer
$xml_output .= "\t</".$row["genus"].">\n";
}

$currentid = $row["id"];
// Write 'genus' header
$xml_output .= "\t<".$row["genus"].">\n";

}

// write the species
$xml_output .= "\t\t<species>".$row["species"]."</species>\n";
}

$xml_output .= "</cps>";
mysql_close($dbconnect);
echo $xml_output;
this is the xml i get in return.


<?xml version="1.0" encoding="utf-8"?>
<cps>
<species>N. ampullaria</species>
<species>N. bellii</species>
<species>N. sanguinea</species>
<species>S. rubra</species>
<species>S. leucophylla</species>
</cps> notice the N. and S.? i cant figure out how to get it to show the name of the genus
I need for it to show as such:


<?xml version="1.0" encoding="iso-8859-1"?>
<cps>
<genus label="Nepenthes">
<species label="N. ampullaria" id="3"/>
<species label="N. bellii" id="5"/>
<species label="N. sanguinea" id="6"/>
</genus>
<genus label="Sarracenia">
<species label="S. rubra" id="7"/>
<species label="S. leucophylla" id="8"/>
</genus>
</cps>again, im trying to get it to add into a flash tree component and show each genus as a folder and each species under its genus.

oh and also where can i read about using php to add the xml attributes for nodes? ie., id="3"

thanks for the help

~billy

[Charleh]

You are so close I'm suprised you haven't figured it out seeing as you've put the PHP together so well. There are probably a couple of bugs with my code as I didn't actually try it -

note: you can use PHP tags instead of code tags in your posts to colour the code!

The bit thats supposed to output the genus doesn't look like its working and it's probably because you've tried to access genus.id in the $row[] array. The SQL query doesn't specify an alias for the genus.id column so the actual column name will be just 'id' in the array if the column isn't ambiguous or may be something else if it is ambiguous (maybe no name just a numeric column index!)

I'd try adding the actual column names in the select list and make sure in your code that anything to do with the $currentid uses the genus id (whatever alias you give it)

Also you might want to add an extra 'footer' write code underneath the loop or the last genus footer won't be written

e.g.



// should be
if($currentid != $row["id"]) {
// assuming that the genus.id is the only column called 'id' in the query

[billylh]

ok, so after tinkering with it, i got these results:


<?xml version="1.0" encoding="utf-8"?>
<cps>
<genus name="Nepenthes">
<species name="N. ampullaria" id="3"/>
<species name="N. bellii" id="5"/>
<species name="N. sanguinea" id="6"/>
</genus>
<genus name="Nepenthes">
<species name="" id=""/>
<species name="" id=""/>
<species name="" id=""/>
</genus>
<genus name="Nepenthes">
<species name="" id=""/>
<species name="" id=""/>
<species name="" id=""/>
</genus>
<genus name="Sarracenia">
<species name="" id=""/>
<species name="" id=""/>
<species name="" id=""/>
</genus>
<genus name="Sarracenia">
<species name="" id=""/>
<species name="" id=""/>
<species name="" id=""/>
</genus></cps>

im getting somewhere i feel but im kinda at a loss. any pointers?


<?php

header("Content-type: text/xml");

$host = "";
$user = "";
$pass = "";
$database = "";

$dbconnect = mysql_connect($host, $user, $pass) or die("Connection error");
mysql_select_db($database, $dbconnect) or die("Database error");

$query = "SELECT * FROM genus INNER JOIN cpcentral on genus.id = cpcentral.genusid";
$query2 = "SELECT * FROM cpcentral WHERE genusid ='$currentid+1'";

echo "<?xml version=\"1.0\" encoding=\"utf-8\"?>\n";
echo "<cps>\n";

$result = mysql_query($query, $dbconnect) or die("No data");
$result2 = mysql_query($query2, $dbconnect) or die("No 2 data");

$currentid = 0;

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

// While we have rows - store the id
if($currentid != $row["genusid"]){

if($currentid > 0){
// Write 'genus' footer
echo "\t</genus>\n";
}

$currentid = $row["id"];

// Write 'genus' header
echo "\t<genus name=\"".$row["genus"]."\">\n";

}
for($x = 0 ; $x < mysql_num_rows($result2) ; $x++){
$row = mysql_fetch_assoc($result2);
echo "\t\t<species name=\"".$row["species"]."\" id=\"".$row["id"]."\"/>\n";

// write the species
//$xml_output .= "\t\t<species name=\"".$row["species"]."\" id=\"".$row["id"]."\"/>\n";
}
}


echo "</genus>";
echo "</cps>";
mysql_close($dbconnect);
//echo $xml_output;

?>

any help is appreciated.

~billy