SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ARGH!! Show only category name

    Hi all... I have asked this question previously, and have made some progress.
    Quote from previous post:
    There are basically three tables in the db:
    1) Categories (category name and ID fields) ("iad_sections")
    2) Articles (name, id, categoryid) ("iad_entries")
    3) lookup table (categoryid, articleid)

    Now, I want to be able to create an "archives" page, with a list of all the categories (only about five or so) as headings, each with listing of all the articles in that category underneath
    Basically, what I now have is a query with a LEFT JOIN, and the lookup table is not used.
    The code I have is as follows:
    PHP Code:
            $sql "SELECT iad_sections.cat_name AS catName,iad_entries.Title AS entry FROM iad_sections LEFT JOIN iad_entries ON Section=iad_sections.id";
                
    $result    =mysql_query($sql);
                
    $numrows=mysql_num_rows($result);
                
                if(
    $numrows==0)
                    {
                        print (
    "No rows found" .mysql_error());
                    }
                    else
                    {
                    while(
    $row=mysql_fetch_array($result)){
                            print (
    "<h2>" .$row['catName'] ."</h2>");    
                            print (
    "<p>" .$row['entry'].$row['ID'] ."</p>");                            
                    }
                  } 
    Now, this half works, but it returnes each of the entries I have in my in iad_entries, along with the title of the category that that entry is assigend to. I also think that my query statment is a lot more verbose than it needs to be, and could be wrong, as I dont understand them completely yet.
    What I need, as posted in my other post, is for the category name to be displayed ONCE, with all the appropriate entry titles listed under each category name.

    You can see the result here

    Help, as always, much appreciated.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I already provided the code to do this in your other thread a few days ago:

    http://www.sitepoint.com/forums/showthread.php?t=387554

    That prints the category once then all the articles in that category, even though the category is retrieved with every row.

  3. #3
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I know you did, thanks Dan, but I didnt completely understand the code, thus asked the following:

    After FROM, you have articles t1 INNER JOIN categories t2 . . . . now, as "articles" and "categories" are the names of these tables, then they themselves cannot contain tables, so what is t1 and t2 in this case ??

  4. #4
    SitePoint Guru Dashman's Avatar
    Join Date
    Jan 2006
    Location
    Manchester, UK
    Posts
    627
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dan, were you just saying that "articles" was table 1 (t1) and "categories" was table2 (t2), sorry, I probably sound like a nag. . . .

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, the names after the table names in the FROM list are called "aliases", and just make referring to them in the rest of the query easier (faster to type t1.column_name than categories.column_name).

    Regardless, it's the logic within the loop that handles only displaying each category name once

    Even if you use a different query, you should be able to figure out and rewrite/reuse that part.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •