PDA

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



flexillu
11-30-2010, 04: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, 09:40 PM
Short answer, yes.

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

Lamped
12-01-2010, 12: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, 12: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, 12: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, 12: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, 12: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, 02: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, 04: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, 01: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, 01: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, 03: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, 03: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, 03: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, 04:07 PM
Can you give me an SQL dump of your database?

flexillu
12-03-2010, 12: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, 07:42 AM
Are you displaying this in the browser? Have you tried viewing the page source?

flexillu
12-03-2010, 09: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, 02: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, 03: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, 08: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, 09: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?