I've done it this way and it works fairly well without using subselects
(which were finicky between MySQL versions of the past). Subselects worked
fine on my production system, but then didn't when I uploaded to the
(several versions old) server, so I went back to doing it simply using two
queries.
1) Create the DB to hold something like (depends upon how deep the majority
of chapters go):
titleTable titleID title
1 Title1
2 Title2
subtitleTable subtitleID parentTitleID
subtitle
1 1
Subtitle1.1
2 1
Subtitle1.2
[so far so good, every 1.1 is going to have a 1 , after that, it's
variable]
contentsTable contentID subtitleID additionalString1
contents
1 1 1
subtitle 1.1.1
2 1 2
subtitle 1.1.2
3 2 1
subtitle 1.2.1
So far, so good. You can then add additional strings as needed to the
contents table.
2) Pull everything up and loop through the contents (while or foreach of
whatever sort of loop you prefer):
$TitleArray = SELECT titleID, title from titleTable;
$ContentsArray = SELECT CONCAT_WS('.','titleID','subtitleID') as node,
subtitle,
FROM contentsTable, subtitleTable
WHERE contentsTable .subtitleID =
subtitleTable.subtitleID and subtitleTable.titleID = $TitleArray[0]];
if( !isset($_GET[titleID])) {
doTitleQuery();
for ($i = 0; $1<count(TitleArray); $i++) {
$titleID = $TitleArray[0];
printTitle($titleID);
doContentsQuery($titleID);
printContents();
}
} else { // get contents for a specific title; }
I found it easier to use two queries than to try to get it perfectly in one.
Once ou get the output you want,t hen you can fine tune everything any MAYBE
get it down to one subselected query.
Something else you'll want to build in is a way to determine the titleID
dynamically.
BTW, I didn't check this code, it's only for the concept.
-----Original Message-----
From: php-objects@xxxxxxxxxxxxxxx [mailto:php-objects@xxxxxxxxxxxxxxx] On
Behalf Of Sourav
Sent: Friday, December 30, 2005 3:51 PM
To: php-objects@xxxxxxxxxxxxxxx
Subject: PHP MySQL code help
I am trying to develop a e-book management system in php MySQL where a
chaper or a sub-chapter may have one or more subchapter, like
1
1.1
1.1.1
1.2
1.3
1.3.1
1.3.2
1.3.3
1.4
2
2.1
2.1.1
How can I construct a query so that first 1 then 1.1 then 1.1.1 then 1.2
.... will be diaplayed
Regards,
Sourav
------------------------ Yahoo! Groups Sponsor --------------------~--> AIDS
in India: A "lurking bomb." Click and help stop AIDS now.
http://us.click.yahoo.com/9QUssC/lzNLAA/TtwFAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator - http://www.meta-language.net/
Yahoo! Groups Links
------------------------ Yahoo! Groups Sponsor --------------------~-->
1.2 million kids a year are victims of human trafficking. Stop slavery.
http://us.click.yahoo.com/.QUssC/izNLAA/TtwFAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator - http://www.meta-language.net/
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php-objects/
<*> To unsubscribe from this group, send an email to:
php-objects-unsubscribe@xxxxxxxxxxxxxxx
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
