SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Addict Smolf3d's Avatar
    Join Date
    Aug 2004
    Location
    russia
    Posts
    224
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile Help with simple code (categories and subcategories) need correct

    PHP Code:
    $cats mysql_query("SELECT * FROM categories"); 
    while(
    $showCats mysql_fetch_array($cats)) { 
    $category $showCats['catid']; 
    $subq "SELECT * FROM categories WHERE catid = '$category' "
    $sub mysql_query($subq); 
    if(!
    mysql_num_rows($sub)) die("Error. Your query was $subq <br> and the error was ".mysql_error()); 
    echo 
    "<tr><td class=\"tablea\" style=\"border-style: none; border-width: medium\" height=\"10\">&nbsp;<b>".$showCats['catname']."</b></td><td class=\"tablea\" style=\"border-style: none; border-width: medium\" height=\"10\"></td></tr>";
    while(
    $showSub mysql_fetch_array($sub)) {  
    echo 
    "<tr><td class=\"tablea\" style=\"border-style: none; border-width: medium\" height=\"10\">&nbsp;<a style=\"font-size: 11px; font-family: tahoma, sans-serif; text-decoration: none; background: none\" href=\"index.php?cat=".$showSub['name']."\"><font size=\"2\">".$showSub['name']."</font></a></td><td class=\"tablea\" style=\"border-style: none; border-width: medium\" height=\"10\"><b>".$cat['count_movies']."</b></td></tr>";
    }} 

    Its was echo double information (if 2 subs, print double categories) like

    Code:
    Category1
    - sub1
    - sub2
    Category1
    - sub1
    - sub2
    Category2
    - sub1
    - sub2
    - sub3
    Category2
    - sub1
    - sub2
    - sub3
    Category2
    - sub1
    - sub2
    - sub3

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    What does the categories table contain (structure and data) ?

  3. #3
    SitePoint Addict Smolf3d's Avatar
    Join Date
    Aug 2004
    Location
    russia
    Posts
    224
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    CREATE TABLE `categories` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `catid` INT(10) NOT NULL,
      `catname` VARCHAR(50) NOT NULL,
      `name` VARCHAR(40) NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=30 ;
     
    INSERT INTO `categories` VALUES (1, 1, 'sub1', 'somthing here1');
    INSERT INTO `categories` VALUES (2, 1, 'sub1', 'somthing here2');
    INSERT INTO `categories` VALUES (3, 2, 'sub2', somthing here1');
    INSERT INTO `categories` VALUES (4, 2, 'sub2', 'somthing here2');
    INSERT INTO `categories` VALUES (5, 3, 'sub3', 'somthing here1');
    INSERT INTO `categories` VALUES (6, 3, 'sub3', 'somthing here2');
    INSERT INTO `categories` VALUES (7, 3, 'sub3', 'somthing here3');
    INSERT INTO `categories` VALUES (8, 4, 'sub4', 'somthing here1');
    INSERT INTO `categories` VALUES (9, 5, 'sub5', 'somthing here1');
    INSERT INTO `categories` VALUES (10, 6, 'sub5', 'somthing here2');

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Ack.

    Okay, #1: You need an ORDER BY on that category query. (Probably a double-order by... IE: ORDER BY catid,name ). That way we're sure we're getting the categories in the right order.
    #1b: You probably dont need to SELECT *, either... only pull the fields you're interested in using.

    #2... you have no parent-child relationship defined for this data (or more to the point, you've defined everything as a child), so how do you know which ones are the 'main' categories, and which ones are the sub-categories?
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The problem is because you have a loop inside a loop. You have two queries that are selecting * from categories, one of which has no condition. I'm not sure why you'd need both

  6. #6
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I just created your test data and this appears to give me the result you're looking for:
    Code MySQL:
    SELECT concat("Category", catid) AS category
         , concat("- ", catname) AS subcategory
      FROM categories;

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    On a related note, I highly recommend the Nested Set model for storing hierarchical data for categories and the like: http://mikehillyer.com/articles/mana...data-in-mysql/


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
  •