Recursive Databse / etc

Anon

I am new to PHP (coming from ASP) and am looking for some help in working on a tool I started to write in ASP but didn't get working quite right.

I have a database that contains an id (primary key), a parent id, and a data entry for that id.

What I need to do build a hierarchical tree showing the tree structure, and display the group totals of each "branch" of the tree.

For instance, I have a parent, two children, and 3 subchildren for each child. say each entry has a value of 10 (they all vary, however).. the parent would have a group value of 90, with each child haivng a group value of 40 each, and the subchild having group values of 10 each.

I need to display the tree structure, and the totals for their group as they're generated.

-parent 90 10
-child 40 10
-child1 10 10
-child2 10 10
-child3 10 10
-child 40 10
-child1 10 10
-child2 10 10
-child3 10 10

I built the tree in asp using recursive database calls, allowing for "unlimited" depth and width of the tree, but it opened a lot of database connections to do it. I'll deal with peformance if I need to do this again, but am not sure how to calculate the group values dynamically.

Any thoughts? I appreciate all the help I can get on this, sample code, etc.

Thanks,

John

sarahk

I would have thought you could do one clever query into an array. Looping through the array if it's ordered properly may be less expensive than the repeated database queries.

I know Oracle lets you do a query starting at one point and looping up or down based on an id/parentid combination. I don't know if MySQL (or whatever you are using) has such a function, I doubt it.

HTH

Anon

That sounds like a job for a programmable function. Does your database support some kind of scriptability? It's pretty fast to recurse records in an internal database function because you aren't transferring lots of data across the wire so to speak.

Anon

I'll look into that, thanks.

Anon

I'm uncertain what database I'll end up with, ideally SQL Server 2000 CE (for pocket pc's) if I can find a way to run PHP on the PDA, otherwise, probably MySQL which I'm just starting to learn.

Using oracle, how would you write the query?

Anon

How complex of a database do you want? If you're gonna have to iterate across a table to get recursive output (likely in mysql) then you might as well consider gdbm datasets, which are just simple hashes. No SQL needed, but good for somethings like this.

Much lighter weight than ANY real RDBMS, GDBM and it's DBM style partners are supported by PHP. They aren't really databases, so they don't need a complex master program. Note that they arn't built for transactions, parallel writes or anything like that really, just storing lots of data in a simple key => value method that's pretty fast if you know the key.

libgdbm is only about 20k. gdbm files only take a fraction more disk space than plain text files. So there's good savings on storage space in prgram execution and storage. Recursing through one is pretty fast for single user access so typical in a hand held, it's just l*t where l = levels deep to recurse and t = time to retrieve one record plus your code.

Try running your code across an array in memory and a simple hash like gdbm and see which runs well. I think a database is getting in the way of delivery here.

If you need to use a database, looke for an imbedded version of something that runs in unix / linux as well as windows if possible, and is stable. You never know which OS you'll be on tomorrow.

MSSQL and Postgresql both need administration, so if you use them, be sure and have a set of scripts to vacuum at night or rebuild indexes or whatever little stuff they'll both need.

MySQL needs maintenance too, as do most databases. GDBM needs almost no maintenance, but is dumb as a brick. And I mean that in the good way. :-)