Re: outline .... need help

Hi Larry. Let's start with the database table. You need one that has an optional one-to-one relationship to ITSELF. I would imagine that you'd need a table that has at least these attributes: ------------------ | Outline | ------------------ | outline_id | +o| | outline_groupid| | | outline_name | | has | outline_order | | | outline_parent | +-| ------------------ outline_id would be the primary key. outline_groupid would be what separates one outline from another. It would most likely be a foreign key to the table I talk about a bit further down. I imagine that you would like for this table to hold more than one outline. outline_name is just whatever you want to store (in your example, this would be Heading, Subheading, detail, second detail, etc). You could add more attributes like it (I can think of an outline that references page numbers and http links. So you could also have outline_pagenum and outline_href also. Point is, add however many you'd like.) outline_order is what keeps everything straight. You have two options with this. You could number by level, or you could number the entire group. I would elect to go with the second because you would be ordering the levels too. In your example, the top levels would be numbered 1, 6 and 7. As long as your PHP script could know how to handle it, you'd be set. (Supppose you don't want to show all the level of detail all the time. You could make the script only show x number of levels. The way this data model is constructed would make this task hopefully pretty easy. Lastly, there is outline_parent. This is where the magic happens. It must be an optional relationship (allow nulls). All it does is reference the primary key of another record. So in your example, 1. detail's parent would reference the outline_id of A. Subheading. A. Subheading references I. Heading's outline_id. You also may want to make another table called Outline_Group. This would hold stuff like title, description, author, first element. You could then make outline_groupid (in the Outline) table have a one-to-one relationship to the primary key of Outline_Group. This structure would allow for adds, deletes and updates when necessary. Think about it, if you delete a child, is anything else disturbed? No. In fact you could probably regenerate the outline without any further trouble. If your PHP script knows the count of records in a certain group, then it would be able to skip numbers in the sequence that are missing. Say you originally have 1,2,3,4,5,6,7,8 and you remove 5, which happens to be a child of 3, your order would then be 1,2,3,4,6,7,8. If your PHP script would know how to handle it, it would mean MUCH less work (because you'd have to reorder everything in the DB everytime you deleted). If you insert something in the middle, you will have to reorder. I don't know of a way to get around it. The SQL command I'd use to get everything out would be: $strSQLQuery = "SELECT * FROM Outline WHERE outline_groupid=$desiredoutline ORDER BY outline_order"; Good luck. -Brian -----Original Message----- From: Larry "RedCobra" Linthicum [mailto:redcobra@thevision.net] Sent: Thursday, September 27, 2001 5:13 PM To: php-db@lists.php.net Subject: outline .... need help I want to store, retrieve, and print out an "outline" dynamically I. Heading A. Subheading 1. detail 2. second detail B. Second Subheading II Second Heading III Third Heading A.sub B Sub etc etc I do not know, in advance, how many entries there will b, in any given place. And, it must be possible to ADD new entries, AND have it print in the correct order I have a fair amount of experience with PHP / MySql for simpler applications, but every way I can think of to do this seems much more complicated than seems right. I'm hoping some of you have ideas on how to set up the tables, and how to retrieve and print the data. Links to examples or tutorials will be greatly appreciated .... I've searched the sites I know of, but found little that addresses my problems. My poor head is a buzz with questions, <G> For instance, what kind of sort field can I use that will allow a "heading" to be inserted between "I" and "II " and then displayed in the correct order? Or, is there a way to retrieve ALL the "detail" level entries with one query, then sort them and print them un the right heading or will I have to do a long series of queries ... I'm nearly certain I'm making it more difficult than it is.... thanks for your help and suggestions