- This topic is empty.
-
AuthorPosts
-
November 20, 2012 at 9:50 am #40890foomanParticipant
I have two tables, one for landing pages and one for sub-pages. They have a one-to-many relationship.
**Structure for Landing Pages:**
- id
- title
- content**Structure for Sub-pages:**
- id
- landing_id
- title
- contentThis is all well and good.
How do I select everything in one query so that all pages are in order starting from the first landing page, it’s sub pages, next landing, sub-pages. etc etc.Do I have a really bad structure or is this a strange query to need?
November 20, 2012 at 10:56 am #114921__ParticipantSELECT p.id, p.title, s.id, s.title
FROM pages p
JOIN subpages s
WHERE s.landing_id = p.idHowever, since all of your records represent pages, why not put them in the same table?
CREATE TABLE page(
id INT UNSIGNED NOT NULL AUTO_INCREMENT
,parent INT UNSIGNED NOT NULL DEFAULT 0
,title VARCHAR(255) NOT NULL
,content TEXT NOT NULL
)This uses a self-referential foreign key to indicate which page.id a sub-page belongs to. The default value “0” (which has no corresponding id) would indicate a top-level page.
Then , your query is much simpler:
SELECT id,title
FROM page
ORDER BY parentWhich would give you a result like
id | parent | title
+
+
1 | 0 | landing page A
2 | 0 | landing page B
3 | 1 | page A subpage 1
4 | 2 | page B subpage 1
5 | 2 | page B subpage 2You can sort them into an array [map] from there.
November 20, 2012 at 11:42 am #114929__Participant> PS….. how did you do such an awesome code table haha. Mine was an epic fail :(
just remember to count characters : )
> As for your initial query, this will return all results that have subpages. And not those with only a landing page (no sub nav, for example).
Sorry, I meant to write
SELECT p.id, p.title, s.id, s.title
FROM pages p
LEFT JOIN subpages s
ON s.landing_id = p.id> that’s the kind of list that’d be cool to return directly from a query. Right now the only thing I can think of doing is returning the data, and then dumping things into an array to sort it as needed.
I think you’ll be stuck sorting it out in an array. There may be a way to do this in MySQL, but I don’t know that. It seems you’d need to make a preliminary query and then iterate over each result. You might be able to create a VIEW to accomplish this as well… I’ll let you know if I figure it out.
November 20, 2012 at 11:57 am #114931__Participant**********
[this is a little complex](http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html), and requires stored procedures, but it seems it could be a solution.November 20, 2012 at 4:03 pm #114966__Participant“Round trips” to the database are very slow – connecting, querying, and retrieving a result can take as long as the whole rest of your script.
Say you have five landing pages – that’s at least six queries, where you could have gotten the same info with only one. I’d recommend getting all of the records at once, if at all possible.
IIUC, you might do something like this:
SELECT menu_id,landing_id,title
FROM page
WHERE IS NOT NULL menu_id
OR IS NOT NULL landing_idThen, in PHP, you can assemble your list:
# organize resultset into two arrays: menu pages and submenu pages
while( $r = $PDOresult->fetch( PDO::FETCH_NUM ) ){
list( $menuID,$landingID,$title ) = $r;
if( !empty( $menuID ) ){
$menu[ $menuID ] = $title;
}
if( !empty( $landingID ) ){
$submenu[ $landingID ][] = $title;
}
}
# combine the two arrays into a flat list
foreach( $menu as $key => $landingPage ){
$list[] = $landingPage
if( !empty( $submenu[ $key ] ) ){
foreach( $submenu[ $key ] as $subPage ){
$list[] = $subPage;
}
}
}
print ‘'.implode( "n",$list );
If you have a table like this:
menu_id | landing_id | title
+
+
1 | NULL | landing page one
2 | NULL | landing page two
NULL | 1 | page one subpage A
NULL | 2 | page two subpage A
NULL | 2 | page two subpage B
NULL | NULL | unassigned page (just for fun)
You'll get a result like this:
landing page one
page one subpage A
landing page two
page two subpage A
page two subpage BNovember 21, 2012 at 9:41 pm #115115__Participantno prob
-
AuthorPosts
- The forum ‘Other’ is closed to new topics and replies.