Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help with a JOIN statement

    I am having trouble with a JOIN statement...

    I am trying to list each record from TABLE_HC_CAT & then list each record from TABLE_HC_CON under its category that we pulled from TABLE_HC_CAT. I have this working to pull the data, but it is listing the categories_name for each question.

    It should list like this:

    Shipping
    Shipping question 1
    Shipping question 2
    Shipping question 3
    Shipping question 4

    but currently it is listing like this:

    Shipping
    Shipping question 1
    Shipping
    Shipping question 2
    Shipping
    Shipping question 3
    Shipping
    Shipping question 4

    PHP Code:
        $admin_query_query_raw = "select 
                                  cat.categories_id, 
                                  cat.categories_name, 
                                  cat.parent_id, 
                                  cat.sort_order, 
                                  cat.status, 
                                  cat.date_added, 
                                  cat.last_modified, 
                                  con.content_id, 
                                  con.status, 
                                  con.question, 
                                  con.answer 
                                  from " . TABLE_HC_CATEGORIES . " cat
                                  LEFT JOIN " . TABLE_HC_CONTENT . " con
                                  ON cat.categories_id=con.categories_id
                                  where cat.column_id = '2'
                                  and con.status != '0'
                                  order by 'cat.sort_order' ";
        $admin_query_query = tep_db_query($admin_query_query_raw);
        while ($admin_quer = tep_db_fetch_array($admin_query_query)) {
    ?>
              <tr>
                <td class="HC_HEADING_TITLE" align="<?php echo HEADING_TITLE_ALIGN?>"><?php echo $admin_quer['categories_name']; ?></td>
              </tr>
              <tr>
                <td class="HC_LINKS" align="<?php echo HEADING_TITLE_ALIGN?>"><?php echo '' $admin_quer['question'] . ' - ' $admin_quer['content_id'] . '' ?></td>
              </tr>
    <?php
        
    }

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    see the albums/artists link in my signature.

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    see the albums/artists link in my signature.
    That didn't help any. Look at that first query on your site
    PHP Code:
    select 
    artistname

    albumname 
    from artists 
    left outer join albums 
    on artists
    .id albums.artist_id 
    where artistname
    ='Black Sabbath'
    That displays results as
    Code:
    +---------------+------------------------+
    | artistname    | albumname              |
    +---------------+------------------------+
    | Black Sabbath | Paranoid               |
    | Black Sabbath | Sabbath Bloody Sabbath |
    +---------------+------------------------+
    2 rows in set (0.50 sec)
    My problem is that I only want to display the artistname once & list each their albumname's under their name...like this:

    Black Sabbath
    • Paranoid
    • Sabbath Bloody Sabbath

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    so what you are saying is you only read part of the article and didn't understand the php?

    the php will allow you to print out the artist once, just like you said.

  • #5
    Regular Coder
    Join Date
    Dec 2005
    Posts
    346
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    so what you are saying is you only read part of the article and didn't understand the php?

    the php will allow you to print out the artist once, just like you said.
    Why do you have to be a ****ing smart *** all the time for? If I understood this then I wouldn't be asking for help now would I? I read over that article & don't see the part about php only printing the artist name once.
    Last edited by lansing; 02-11-2007 at 04:15 AM.

  • #6
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    there's only one section in that article that deals with the PHP part of the operation, it's at the bottom and quite clearly says:
    When a new artist comes up, we print it out and then set the value of the $artist variable to the name of that artist. Each subsequent row will ignore the artist until a new artist is found.
    It's telling you exactly what you want...
    My thoughts on some things: http://codemeetsmusic.com
    And my scrapbook of cool things: http://gjones.tumblr.com

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by lansing View Post
    Why do you have to be a ****ing smart *** all the time for? If I understood this then I wouldn't be asking for help now would I? I read over that article & don't see the part about php only printing the artist name once.
    I'm not being smart. I told you that if you read through the article, all of it, not some of it, that it would tell you how to do what you wanted. You pointed out the very first part of the article and said it didn't apply to you.

    There is a php part of the article, if you thought the top part of the article didn't apply to you then my only guess was you didn't read through the entire thing.

    I knew the answer was in that article so I pointed it out to you. If I didn't want to help you I would have said nothing right?

    In fact I will edit the article for clarity sake, so that it will be more clear that is what it is used for. Perhaps you are not the only person to have read it and not understood what it is for. I wrote the article to help not to make things obscure.


  •  

    Posting Permissions

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