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
- content
This 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?
SELECT p.id, p.title, s.id, s.title
FROM pages p
JOIN subpages s
WHERE s.landing_id = p.id
However, 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 parent
Which 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 2
I thought of doing the structure like that, but I thought it'd be easier to keep track of if I could keep the pages in separate tables. It's just how I figured it'd make sense to do things.
I definitely thought of doing things all in one table, but decided it'd be easier to keep track of in the future as things grow a bit.
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). I have a similar query where I return basically the same thing (including permalinks which are stored in another table).
What would be super-awesome is returning something such as...
id | title
--------+--------+--------------------
1 | landing page A
2 | page A subpage 1
3 | landing page B
4 | page B subpage 1
5 | page B subpage 2
I realize I'd need to return more data, but 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.
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.
jesus murphy that seems like a lot of reading for what we're trying to accomplish.
I've actually taken your advice about using one table.
I've found that without doing a thesis on database structure, I use PDO statements to iterate over the results and order things that way.
So I do the following:
1. Grab all pages that have a menu_id (only landing pages have this).
2. Iterate over the results. Store them in a nice lil array.
3. For each, grab the sub-pages that have a landing_id that matches the landing page's id. Store them in the array.
4. List out the array. Everything will be in order as expected.
It's more queries... but I honestly have no idea how to do this more efficiently. And I don't see a noticeable gain in performance being worth the time :(
"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_id
Then, in PHP, you can assemble your list:
<?php
# 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 '<pre>'.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 B
I have two tables, one for landing pages and one for sub-pages. They have a one-to-many relationship.
Structure for Landing Pages:
Structure for Sub-pages:
This 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?
However, since all of your records represent pages, why not put them in the same table?
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:
Which would give you a result like
You can sort them into an array [map] from there.
I thought of doing the structure like that, but I thought it'd be easier to keep track of if I could keep the pages in separate tables. It's just how I figured it'd make sense to do things.
I definitely thought of doing things all in one table, but decided it'd be easier to keep track of in the future as things grow a bit.
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). I have a similar query where I return basically the same thing (including permalinks which are stored in another table).
What would be super-awesome is returning something such as...
I realize I'd need to return more data, but 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.
PS..... how did you do such an awesome code table haha. Mine was an epic fail :(
just remember to count characters : )
Sorry, I meant to write
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.
this is a little complex, and requires stored procedures, but it seems it could be a solution.
jesus murphy that seems like a lot of reading for what we're trying to accomplish. I've actually taken your advice about using one table.
I've found that without doing a thesis on database structure, I use PDO statements to iterate over the results and order things that way.
So I do the following: 1. Grab all pages that have a menu_id (only landing pages have this). 2. Iterate over the results. Store them in a nice lil array. 3. For each, grab the sub-pages that have a landing_id that matches the landing page's id. Store them in the array. 4. List out the array. Everything will be in order as expected.
It's more queries... but I honestly have no idea how to do this more efficiently. And I don't see a noticeable gain in performance being worth the time :(
"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:
Then, in PHP, you can assemble your list:
If you have a table like this:
You'll get a result like this:
you, sir... are a saviour. I didn't even look at how simple this could be by using PHP moreso than MySQL :)
Thanks for the help!
no prob