Skip to main content
M
matthisco
Inspiring
February 5, 2009
Answered

Loop Problem

  • February 5, 2009
  • 5 replies
  • 678 views
Hi,

I have two tables, a sites table and a pages table (below). I'd like to list the sites and when a user clicks a site link, I'd like to display the pages for that site underneath the site link as a list. Its a very basic CMS of pages within sites.

If I could use a javascript hide and show to display the submenus, that would be great.

Could anyone tell me how I I do this with one query and display this on my page, could I use the group by attribute?

Can anyone tell me how I can do this with one query?

sites table
siteid -PK
sitename

pages table
pageid -PK
siteid - FK
pagebodytext
pagesummarytext
    This topic has been closed for replies.
    Correct answer matthisco
    Thanks very much for the replies

    5 replies

    F
    fober1
    Inspiring
    February 5, 2009
    Hi,

    Here's an example for the open/close javascript function:

    cheers,
    fober

    =================================================

    <div>
    <a href="javascript:void(0);" onclick="toggle(1)"><strong>SITE 1</strong></a><br>
    <ul id="tax_1" >
    <li><a href="">PAGE 1-1</a></li>
    <li><a href="">PAGE 1-2</a></li>
    </ul>

    <a href="javascript:void(0);" onclick="toggle(2)"><strong>SITE 2</strong></a><br>
    <ul id="tax_2" style="display:none;">
    <li><a href="">PAGE 2-1</a></li>
    <li><a href="">PAGE 2-2</a></li>
    </ul>

    <a href="javascript:void(0);" onclick="toggle(3)"><strong>SITE 3</strong></a><br>
    <ul id="tax_3" style="display:none;">
    <li><a href="">PAGE 3-1</a></li>
    <li><a href="">PAGE 3-2</a></li>
    </ul>

    </div>


    <script language="JavaScript">
    function toggle(id){
    obj=document.getElementById('tax_'+id);

    if (obj == null)
    return;

    if (obj.style.display != 'none')
    obj.style.display= 'none';
    else
    obj.style.display= 'block';
    }
    </script>

    M
    matthiscoAuthorCorrect answer
    Inspiring
    February 5, 2009
    Thanks very much for the replies
    N
    Newsgroup_User
    Inspiring
    February 5, 2009
    use CFOUTPUT with GROUP attribute to output your data.
    if u r not familiar with the syntax, check cfml reference.

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    M
    Mattastic
    Inspiring
    February 5, 2009
    I have this sql query so far:

    SELECT *
    FROM sites
    LEFT JOIN pages
    ON sites.siteid=pages.siteid

    <ul><cfoutput query="sitecms">
    <li><a href="?siteid=#siteid#">#sitename#</a></li>
    <ul><li>#pagelink#</li></ul>
    </cfoutput>
    </ul>

    It repeats the site name for every row, do you know how I can list all the pages per site?

    Thanks again
    D
    Dan_Bracuk
    Inspiring
    February 5, 2009
    quote:
    Originally posted by: Mattastic
    I have this sql query so far:

    SELECT *
    FROM sites
    LEFT JOIN pages
    ON sites.siteid=pages.siteid

    <ul><cfoutput query="sitecms">
    <li><a href="?siteid=#siteid#">#sitename#</a></li>
    <ul><li>#pagelink#</li></ul>
    </cfoutput>
    </ul>

    It repeats the site name for every row, do you know how I can list all the pages per site?

    Thanks again

    Since you have a foreign key, you don't need a left join, an inner join will suffice. Next, add an order by clause to sort your results by whatever you intend to group your results by, probably sitename. Azadi has already told you the rest.
    M
    Mattastic
    Inspiring
    February 5, 2009
    I've got that and read it, its how I can display the main menu and submenu when the links are clicked that I'm stuck with, rather that use nested queries.

    Thanks again
    D
    Dan_Bracuk
    Inspiring
    February 5, 2009
    If you don't know how to join tables, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices