SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I designed this table layout, I didn't really think this would be a problem, but my brain has drawn a blank, and I cant get what I want out of it. If somebody could give me the query to select all the cats.name's and their relivent sections then I'd be most grateful. Id' like to look the tables so I get something like.

    GBA Reviews | Parent: Reviews
    N64 Reviews | Parent: Reviews
    Games Suck | Parent: Features

    the tables look like this.

    Cats (the sub-sections)
    ID | name | description | Parent id
    ------------------ example
    2 | N64 Reviews | lovely reviews for you, sir | 5


    Sections (the parents)
    ID | Name | Description
    --------------- example
    6 | Reviews | the reviews section, lots of reviews for you, sir

    I hope I havn't over complicated things. I've tried to do it with joins, have I given sufficient info to do this with one query and then a loop of the results? I mean if you look at it, it appears to be an easy thing to do, but I must be tired or something, if anyone can help, I'd appreciate it.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Try this:

    $sql = "
    SELECT Sections.Name, Cats.name
    FROM Sections,Cats
    WHERE Sections.id=Cats.ParentId";

    $result=mysql_query($sql, $connection);
    $row=mysql_fetch_array($result);

    echo $row['name'] . " | " . $row['Name'];

    If the fields in the two tables actually have the same name, like "Name" then use aliasing:

    $sql = "
    SELECT Sections.Name AS parentName, Cats.Name AS subName
    FROM Sections,Cats
    WHERE Sections.id=Cats.ParentId";
    ....
    ....
    echo $row[subName] . " | " . $row[parentName];
    Last edited by 7stud; May 9, 2001 at 20:09.

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could also add a ORDER BY clause to that sql to put the results into an alphabetic order

    $sql = "
    SELECT Sections.Name AS parentName, Cats.Name AS subName
    FROM Sections,Cats
    WHERE Sections.id=Cats.ParentId
    ORDER BY parentName, subName";

  4. #4
    SitePoint Zealot DarkMonkey's Avatar
    Join Date
    Apr 2001
    Location
    uk
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot people. I dont know why I couldn't do that myself, but my head was going weird. Things like "If I use WHERE on all the selected rows it'll only grab the parent of the very first row and wont loop" ... I dont know why, but sometimes the theory can confuse me.

    Thanks again for your help, always appreciated


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
  •