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 8 of 8

Thread: Grouping?

  1. #1
    Regular Coder
    Join Date
    May 2003
    Location
    34° 54' N 82° 13' W
    Posts
    996
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping?

    I have 2 tables in my MySQL database.
    - categories
    - sections

    Categories acts like the forums here - Server-side help, client side help, etc.
    Sections acts like the sections in the forums - PHP (with Server Side) , JavaScript (with Client Side), etc.

    Both tables have an id that relates them together. For instance a category name 'General' has the id of 1. Each section in that goes with 'General' also has an id of 1, so that they correlate.

    My question is, how would I display them together? For instance, how would I get all general sections to show up under the general forum like how PHP,Perl/CGI,etc. show up under Server-Side help here?

    I hope I am clear by what I am asking.

    Thanks.
    Stevie Peele
    Neverside IRC Network - irc.veonex.net | tc.tutorialnetwork.org
    #dev - any programming,etc. question
    #design - design discussion and critque
    #central - general chat
    Come join us!

  • #2
    Senior Coder
    Join Date
    Feb 2004
    Posts
    1,206
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
     $result_cats mysql_query("SELECT * FROM tbl_categories"$db);
     while(
    $row_cats mysql_fetch_array($result_cats)) {
      echo 
    $result_cats["name"]."<br>";

      
    $result_secs mysql_query("SELECT * FROM tbl_sections WHERE id=".$result_cats["id"], $db);
      while(
    $row_secs mysql_fetch_array($result_secs)) {
       echo 
    " ".$result_secs["name"]."<br>";
      }
     } 
    So name is the column that holds the name of the category/section (change that accordingly) and id is the id of the category/section (change that accordingly).

    That's a pretty basic example, but that's the idea. You can work with that to put your data into tables or however you want to display it.

    Hope that helps,
    Sadiq.

  • #3
    Regular Coder
    Join Date
    May 2003
    Location
    34° 54' N 82° 13' W
    Posts
    996
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks - I'll have to test that probably sometime this weekend since I wil be unable to do so until then. I'll let ya know how it turns out!

    Thanks
    Stevie Peele
    Neverside IRC Network - irc.veonex.net | tc.tutorialnetwork.org
    #dev - any programming,etc. question
    #design - design discussion and critque
    #central - general chat
    Come join us!

  • #4
    Regular Coder
    Join Date
    May 2003
    Location
    34° 54' N 82° 13' W
    Posts
    996
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, it seems to be working *somewhat*. I turns out like this -
    Code:
    Serverside Programming
    Client Side Programming
    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY `id` ASC' at line 1
    as you can see its turning out my sections, but there is something wrong with my category query. I usually can debug my own sql errors, but this one is puzzling me
    PHP Code:
        function main_display(){
            
            
    $this->connect();
            
             
    $sections_query "SELECT id,name,cat_id FROM sections"/* ie: server side programming, client side, etc. */
             
    $result mysql_query($sections_query) or die(mysql_error());
                 while(
    $row mysql_fetch_assoc($result)){ echo $row['name']."<br>"; }
                    
            
    $category_query "SELECT id,name,cat_id FROM categories WHERE cat_id=".$row['cat_id']." ORDER BY id ASC"/* ie: php, javascript, html, css, dhtml, etc. */
            
    $result mysql_query($category_query) or die(mysql_error());
                      while(
    $row mysql_fetch_assoc($category_query)){   echo $row['name']."<br>";      }    
         } 
    Thanks to all.
    Stevie Peele
    Neverside IRC Network - irc.veonex.net | tc.tutorialnetwork.org
    #dev - any programming,etc. question
    #design - design discussion and critque
    #central - general chat
    Come join us!

  • #5
    Regular Coder trib4lmaniac's Avatar
    Join Date
    Feb 2004
    Location
    Cornwall, UK
    Posts
    535
    Thanks
    0
    Thanked 0 Times in 0 Posts
    while($row = mysql_fetch_assoc($category_query))

    Shouldn't that be

    while($row = mysql_fetch_assoc($result))

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the resourceID is indeed wrong.

    but the reason for the error is that $row['cat_id'] inside $category_query is empty. (as you would have seen if you printed the executed sql.)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    Regular Coder
    Join Date
    May 2003
    Location
    34° 54' N 82° 13' W
    Posts
    996
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Alright, im making progress

    PHP Code:
        function main_display(){
            
            
    $this->connect();
            
             
    $sections_query "SELECT id,name,cat_id FROM sections"/* ie: server side programming, client side, etc. */
             
    $result mysql_query($sections_query) or die(mysql_error());
                 while(
    $row mysql_fetch_assoc($result)){ echo $row['name']."<br>";  $cat_id $row['cat_id']; /*easier in the string just to set it to a variable*/}
                    
            
    $category_query "SELECT id,name,cat_id FROM categories WHERE cat_id=".$cat_id." ORDER BY id ASC"/* ie: php, javascript, html, css, dhtml, etc. */
            
    $result mysql_query($category_query) or die(mysql_error());
                      while(
    $row mysql_fetch_assoc($result)){   echo " » ".$row['name']."<br>";      }    
         } 
    returns
    Serverside Programming
    Client Side Programming
    » (X)HTML
    » CSS
    Yet, there are two 'sub-sections' to Serverside and they are not showing up. Any reasons why?
    Stevie Peele
    Neverside IRC Network - irc.veonex.net | tc.tutorialnetwork.org
    #dev - any programming,etc. question
    #design - design discussion and critque
    #central - general chat
    Come join us!

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Probably because there is an error inside your code or because your db isn't filled in correctly.

    Could it be that you missed a closing curly bracket ?

    Also, you have column id and cat_id inside the categories table --> what's the point of that? Shouldn't it be a section id? And shouldn't the cat_id inside the sections table be dropped?
    In a normalised db, you'd have a design like
    sections table
    section_id (PK)
    section_name

    categories table
    cat_id
    cat_name
    section_id (FK)

    You should also do this with an outer join so save on these querys inside the loop. Assuming that you have the above design then your code is:
    PHP Code:
    function main_display(){

        
    $this->connect();

        
    $query "SELECT section.section_id, sections.section_name, categories.cat_id, categories.cat_name
                           FROM sections LEFT JOIN categories ON sections.section_id=categories.section_id
                           ORDER BY sections.section_name ASC, categories.cat_name ASC"
    ;
        
    $result mysql_query($query) or die(mysql_error());
        if (
    mysql_num_rows($result) >=1){
          
    $previous '';
          while(
    $row mysql_fetch_assoc($result)){
             if (
    $row['section_name'] != $previous){
                 echo 
    $row['section_name'] . '<br />';
                 
    $previous $row['section_name'];
             }
             echo 
    " » ".$row['cat_name']. '<br />';
          }
        }else{
          echo 
    'No sections found';
        }

    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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