...

View Full Version : Returning XML from mySQL to Populate a Tree Structure



flexillu
11-30-2010, 05:54 PM
I have a image table in my db.

I want my script to check that that a user is registered, if so return the image categories they are allowed access to in XML, can this be done?

e.g XML like this to populate a data Tree


<folder label="Sports">
<folder label="Football">
<jpg label="Kits"/>
</folder>
<folder label="Cricket">
<jpg label="Bats" />
</folder>
<folder label="Nature">
<folder label="Animals">
<jpg label="Birds"/>
</folder>
</folder>
</folder>

Fou-Lu
11-30-2010, 10:40 PM
Short answer, yes.

flexillu
12-01-2010, 10:42 AM
Can anyone explain how I return the XML for a generic number of menu items then?

Lamped
12-01-2010, 01:08 PM
To be able to give you vaguely understandable code, we need to know roughly how your tables are laid out, but basically you'll probably be using recursive calls:


function getXML($parent) {
$rows = get_database_rows_with_parent($parent);
$result = '<folder>';
foreach($rows as $row) {
if (row_is_folder) {
$result .= getXML(row_parent);
} else {
$result .= '<jpg label="'.file_label.'" />';
}
}
$result .= '</folder>';
return $result;
}

Or something like that.

flexillu
12-01-2010, 01:20 PM
So in my db how would i store the structure?

store the file_label, and whether it is a folder or a file?

Lamped
12-01-2010, 01:30 PM
So in my db how would i store the structure?

store the file_label, and whether it is a folder or a file?

Assuming label is different to file name:

id int
name varchar
label varchar
parentid int
type enum(folder, file)

parentid specifies which folder the current file or folder resides in. Setting parentid to a record with type = file would be invalid.

flexillu
12-01-2010, 01:53 PM
hmmm i nearly get it, so close!

If i call the function with the parent "root" it gets stuck in a loop, because root is a folder but has no parent.

What parameter would i pass to the function to start it off.

By the way thanks for oyur help, your awesome. Been stuck on this for so long its untrue.

Lamped
12-01-2010, 01:55 PM
hmmm i nearly get it, so close!

If i call the function with the parent "root" it gets stuck in a loop, because root is a folder but has no parent.

What parameter would i pass to the function to start it off.

By the way thanks for oyur help, your awesome. Been stuck on this for so long its untrue.

Not a problem :)

Typically I use -1 as the parentid for root elements in a structure such as this, so you'd call it with -1.

flexillu
12-01-2010, 03:12 PM
Still can't get my head round this sorry.

If i call the function with -1 or any parent id i get the following error

Warning: Invalid argument supplied for foreach() in C:\wamp\www\xmlphp.php on line 22

My Code

<?php
$hostname_conn = "****";
$username_conn = "****";
$password_conn = "****";

$conn = mysql_connect($hostname_conn, $username_conn, $password_conn);

//connect to the database


mysql_select_db("*****");

getXML(-1);

function getXML($parent) {
//$rows = get_database_rows_with_parent($parent);
$rows = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
$result = '<folder>';
echo $parent;
foreach($rows as $row) {
if (file_type == 'folder') {
$result .= getXML(row_parent);
} else {
$result .= '<jpg label="'.file_label.'" />';
}
}
$result .= '</folder>';
return $result;
}

?>


At the moment in my db i have

id, file_label, parent_id, file_type

3, root, -1, folder
5,Sports,3,folder
6,Football,5,file

Where am i going wrong?

Lamped
12-01-2010, 05:00 PM
$res = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
$result = '<folder>';
echo $parent;
while($row = mysql_fetch_assoc($res)) {

Pseudo code is pseudo code and not to be taken too literally ;)

flexillu
12-02-2010, 02:50 PM
Still no clue.

This is my first php script so i really need you to spell out what i need to do if that's ok. Here is what i've got so far, not getting any output.


<?php
$hostname_conn = "888";
$username_conn = "888";
$password_conn = "888";

$conn = mysql_connect($hostname_conn, $username_conn, $password_conn);

//connect to the database


mysql_select_db("testimages2");

getXML(-1);



function getXML($parent) {
$res = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
//$rows= mysql_fetch_assoc($rows);
$result = '<folder>';


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

if ("folder_type" == 'folder') {
$result .= getXML(row_parent);

} else {
$result .= '<jpg label="'."file_label".'" />';

}
}
$result .= '</folder>';

print $result;
}


?>

Lamped
12-02-2010, 02:55 PM
<?php
$hostname_conn = "888";
$username_conn = "888";
$password_conn = "888";

$conn = mysql_connect($hostname_conn, $username_conn, $password_conn);

//connect to the database


mysql_select_db("testimages2");

print getXML(-1); // print out the end result of getXML, not in the middle of it



function getXML($parent) {
$res = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
//$rows= mysql_fetch_assoc($rows);
$result = '<folder>';


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

if ($row["folder_type"] == 'folder') { // fixed to include $row[]
$result .= getXML($row['parent_id']); // you need the parent_id from the row you just got...

} else {
$result .= '<jpg label="'.$row['file_label'].'" />'; // Fixed to include file_label from row

}
}
$result .= '</folder>';

return $result; // return was essential
}


?>

That should get you a lot closer to your goal :)

flexillu
12-02-2010, 04:38 PM
Thank you again!

Im getting a fatal error on line 18

Allowed memory size of 134217728 bytes exhausted (tried to allocate 568 bytes).

Seems calling it with parent_id -1 doesn't work, it just gets stuck in a loop because the parent of the root is itsself.

This is what i have in my table:

id, file_label, parent_id, file_type

3 root -1 folder
5 Sports 3 folder
6 Football 5 file
7 Cricket 5 file

Lamped
12-02-2010, 04:48 PM
Just so we're absolutely clear, yeah, I did something monumentally stupid. Go me!


<?php
$hostname_conn = "888";
$username_conn = "888";
$password_conn = "888";

$conn = mysql_connect($hostname_conn, $username_conn, $password_conn);

//connect to the database


mysql_select_db("testimages2");

print getXML(-1); // print out the end result of getXML, not in the middle of it



function getXML($parent) {
$res = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
//$rows= mysql_fetch_assoc($rows);
$result = '<folder>';


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

if ($row["folder_type"] == 'folder') { // fixed to include $row[]
$result .= getXML($row['id']); // **** Ooopsie here ****
} else {
$result .= '<jpg label="'.$row['file_label'].'" />'; // Fixed to include file_label from row

}
}
$result .= '</folder>';

return $result; // return was essential
}


?>

flexillu
12-02-2010, 04:57 PM
not printing anything out at all now? :(

even if i try to print out this


$result = '<folder>';
print $result;

Lamped
12-02-2010, 05:07 PM
Can you give me an SQL dump of your database?

flexillu
12-03-2010, 01:23 AM
Yeah sure, if i type some text after <folder>

like <folder>blah

that gets printed to the screen..maybe it's an error with print formatting?

Here's my db, pretty basic at the moment, can't see where there would be a problem


-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 03, 2010 at 12:19 AM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `testimages2`
--

-- --------------------------------------------------------

--
-- Table structure for table `menuitems`
--

CREATE TABLE IF NOT EXISTS `menuitems` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`file_label` varchar(20) NOT NULL,
`parent_id` varchar(20) NOT NULL,
`file_type` enum('folder','file') NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `menuitems`
--

INSERT INTO `menuitems` (`id`, `file_label`, `parent_id`, `file_type`) VALUES
(3, 'root', '-1', 'folder'),
(5, 'Sports', '3', 'folder'),
(6, 'Football', '5', 'file'),
(7, 'Cricket', '5', 'file');

Lamped
12-03-2010, 08:42 AM
Are you displaying this in the browser? Have you tried viewing the page source?

flexillu
12-03-2010, 10:15 AM
ah ok i see.

Nearly Working :)

It doesn't seem to add in the label of the parent folder "Sports", just only the bottom files, i need the folders to have labels for it to work as a menu

Thanks for all your help mate.

Lamped
12-03-2010, 03:16 PM
<?php
$hostname_conn = "888";
$username_conn = "888";
$password_conn = "888";

$conn = mysql_connect($hostname_conn, $username_conn, $password_conn);

//connect to the database


mysql_select_db("testimages2");

print getXML(-1); // print out the end result of getXML, not in the middle of it



function getXML($parent) {
$res = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");

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

if ($row["folder_type"] == 'folder') { // fixed to include $row[]
$result .= '<folder label="'.$row['file_label'].'">'.getXML($row['id']).'</folder>';
} else {
$result .= '<jpg label="'.$row['file_label'].'" />'; // Fixed to include file_label from row
}
}

return $result; // return was essential
}


?>

flexillu
12-03-2010, 04:02 PM
Only problem i have no is that it says result is not defined

so i've defined it as result="";

is this bad practice?

Lamped
12-03-2010, 09:08 PM
Only problem i have no is that it says result is not defined

so i've defined it as result="";

is this bad practice?

Depends on your definition of bad practice, but yes, I should have put in $result = '';


<?php
$hostname_conn = "888";
$username_conn = "888";
$password_conn = "888";

$conn = mysql_connect($hostname_conn, $username_conn, $password_conn);

//connect to the database


mysql_select_db("testimages2");

print getXML(-1); // print out the end result of getXML, not in the middle of it



function getXML($parent) {
$res = mysql_query("SELECT * FROM menuitems WHERE parent_id = $parent");
$result = '';

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

if ($row["folder_type"] == 'folder') { // fixed to include $row[]
$result .= '<folder label="'.$row['file_label'].'">'.getXML($row['id']).'</folder>';
} else {
$result .= '<jpg label="'.$row['file_label'].'" />'; // Fixed to include file_label from row
}
}

return $result; // return was essential
}


?>

flexillu
12-07-2010, 10:33 PM
Hi Lamped,

Sorry to be annoying, once this is sorted i won't bother you again i promise!

It seems that if a folder has no files in it is treated as a file.

So i get a file called Trees, when it should be a folder.

I'm pretty sure none of my folders will ever be empty, but in case they are is there any simple way to prevent this happening?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum