SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 29
  1. #1
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Categories and Subcategories Help

    I have this mysql table:

    Code:
    CREATE TABLE `categories` (
      `categoryID` int(10) unsigned NOT NULL auto_increment,
      `category_name` text NOT NULL,
      `parentID` int(10) NOT NULL default '0',
      `yes_no` char(1) NOT NULL default '',
      PRIMARY KEY  (`categoryID`)
    ) TYPE=MyISAM;
    And this sample data:

    Code:
    INSERT INTO `categories` VALUES (1, 'Fruit', 0, 'y');
    INSERT INTO `categories` VALUES (2, 'Vegetables', 0, 'n');
    INSERT INTO `categories` VALUES (3, 'Orange', 1, 'n');
    INSERT INTO `categories` VALUES (6, 'Apple', 1, 'y');
    INSERT INTO `categories` VALUES (8, 'Onion', 2, 'y');
    INSERT INTO `categories` VALUES (9, 'Carrot', 2, 'n');
    And I use this sql query:

    Code:
    SELECT cat.categoryID, cat.category_name, subcat.categoryID AS subcategoryID, subcat.category_name AS subcategory_name
    FROM categories AS cat
    LEFT OUTER JOIN categories AS subcat ON cat.categoryID = subcat.parentID
    WHERE cat.parentID=0
    ORDER BY cat.category_name, subcat.category_name
    To generate this html output:
    http://www.interfaceuniversity.com/categories.php

    With the above sql query, and this piece of php code:
    PHP Code:
    echo "\t\t<td><input type=\"radio\" name=\"yesOrNo",$row['categoryID'],"\" value=\"e\" id=\"",$row['categoryID'],"\" /></td>\n";

    echo 
    "\t\t<td><input type=\"radio\" name=\"yesOrNo",$row['categoryID'],"\" value=\"i\" id=\"",$row['categoryID'],"\" /></td>\n"
    I generate the radio buttons that you see on that page.
    If you look at the html source for the radio button for Apple as an example, you will notice that the name attribute is yesOrNo1. However, I want it to be the REAL categoryID for apple which is 6. So, it SHOULD be this: ...name=yesOrNo6...

    Any ideas how to do this? I'd appreciate the help.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    moving to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In your code you are referring to $row['categoryID'] while you should refer to $row['subcategoryID']:
    PHP Code:
    echo "\t\t<td><input type=\"radio\" name=\"yesOrNo",$row['categoryID'],"\" value=\"e\" id=\"",$row['categoryID'],"\" /></td>\n";

    echo 
    "\t\t<td><input type=\"radio\" name=\"yesOrNo",$row['subcategoryID'],"\" value=\"i\" id=\"",$row['subcategoryID'],"\" /></td>\n"
    As a side note, you should not use:
    Code:
    `yes_no` char(1) NOT NULL default ''
    in your DB table, but
    Code:
    `yes_no` ENUM('yes','no') NOT NULL default 'no'
    There’s more than one way to skin a cat.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for helping out with the php, nacho

    i must disagree with that last advice, however

    sorry

    go ahead and keep the char(1), spursfan, it's just as good and it's portable too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    thanks for helping out with the php, nacho
    We are here to help, aren't we?
    Quote Originally Posted by r937
    i must disagree with that last advice, however

    sorry

    go ahead and keep the char(1), spursfan, it's just as good and it's portable too
    Well, you're the SQL master if I'm well informed, so I won't dare to contradict you. I would love to hear why do you advice him/her to do so though.
    There’s more than one way to skin a cat.

  6. #6
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm. I see what you mean. I believe that will work, but I also wanted to have the main categories, as well as the subcategories be associated with the radio buttons.
    For example, how can I achieve this html output:
    Fruit: ...name="yesOrNo1"...
    Apple: ...name="yesOrNo6"...
    Orange: ...name="yesOrNo3"...
    Vegetables: ...name="yesOrNo2"...
    Onion: ...name="yesOrNo8"...
    Carrot: ...name="yesOrN9"...

    As you can see, afterthe yesOrNo, I'd like to have the categoryID be there for every main and subcategory.
    Hope that makes sense.

  7. #7
    SitePoint Addict
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can think of two ways of achieving that:
    1) you could query the database with the results of the submitted form and ask for the parentID field for each categoryID, or
    2) you could separate the radio buttons into category groups and make individual forms for each group with a hidden input field per group which holds the value of the corresponding parent categoryID.
    There’s more than one way to skin a cat.

  8. #8
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I will think that over some more.
    Is there any way I could modify the sql query I'm currently using, to acheive what I'm trying to do?

  9. #9
    SitePoint Addict
    Join Date
    May 2003
    Location
    The Netherlands
    Posts
    391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query looks good to me.
    There’s more than one way to skin a cat.

  10. #10
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Please disregard everything I said before. I'll ask in a different way.
    I have this table:

    Code:
    CREATE TABLE `categories` (
      `categoryID` int(10) unsigned NOT NULL auto_increment,
      `category_name` text NOT NULL,
      `parentID` int(10) NOT NULL default '0',
      PRIMARY KEY  (`categoryID`)
    ) TYPE=MyISAM;
    With this sample data:
    Code:
    INSERT INTO `categories` VALUES (1, 'Fruit', 0);
    INSERT INTO `categories` VALUES (2, 'Vegetables', 0);
    INSERT INTO `categories` VALUES (3, 'Orange', 1);
    INSERT INTO `categories` VALUES (6, 'Apple', 1);
    INSERT INTO `categories` VALUES (8, 'Onion', 2);
    INSERT INTO `categories` VALUES (9, 'Carrot', 2);
    And I was wondering what SQL statement would achieve this output:
    HTML Code:
    categoryID     category_name                subcategory_name
    1 	               Fruit                        Null
    6	               Fruit 	 	            Apple
    3 	               Fruit 	 	            Orange
    2 	               Vegetables                Null
    9 	               Vegetables 	 	 Carrot
    8 	               Vegetables 	 	 Onion

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by thespursfan
    Ok. Please disregard everything I said before.
    no problem
    Code:
    select cat.categoryID     
         , cat.category_name                
         , null as subcategory_name
      from categories as cat
     where cat.parentID = 0 
    union all
    select subcat.categoryID     
         , cat.category_name                
         , subcat.category_name as subcategory_name
      from categories as cat
    inner
      join categories as subcat
        on cat.categoryID = subcat.parentID
     where cat.parentID = 0 
    order
        by 2,3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The code you posted almost worked for me.
    When I execute that command, I get this:
    HTML Code:
    categoryID     category_name                subcategory_name
    1 	               Fruit                      Null
    6	               Fruit 	 	            
    3 	               Fruit 	 	            
    2 	               Vegetables                Null
    9 	               Vegetables 	 	 
    8 	               Vegetables
    Any ideas why the subcategory_name isn't being displayed?

    EDIT---------------------------------------------------------

    I tried it again on a different table and set of data, and it worked perfectly.
    Hmm. Oh well.
    Thanks for all of your help and your patience.
    I need to get better at narrowing down my specific problem in the future.
    Thanks again!

  13. #13
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One last question about this.
    How can I join a second table on this categories table?
    If I had this second table:
    Code:
    CREATE TABLE `category_notes` (
      `categoryID` int(10) unsigned NOT NULL auto_increment,
      `category_notes` text NOT NULL,
      PRIMARY KEY  (`categoryID`)
    ) TYPE=MyISAM AUTO_INCREMENT=10 ;
    With this sample data:
    Code:
    INSERT INTO `category_notes` VALUES (1, 'good to eat');
    INSERT INTO `category_notes` VALUES (2, 'good for you');
    INSERT INTO `category_notes` VALUES (3, 'watch your eyes when you peel');
    INSERT INTO `category_notes` VALUES (6, 'green red etc.');
    INSERT INTO `category_notes` VALUES (8, 'don''t cry');
    INSERT INTO `category_notes` VALUES (9, 'i like the baby ones');
    To get this output:
    HTML Code:
    categoryID     category_name     subcategory_name         category_notes
    1 	               Fruit              Null             good to eat
    6	               Fruit 	 	  Apple           green red etc.
    3 	               Fruit 	 	  Orange          watch.....
    2 	               Vegetables      Null               good for you
    9 	               Vegetables      Carrot            i like the...
    8 	               Vegetables      Onion             don''t cry

  14. #14
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bump. Any advice on about my previous post would be appreciated. Thanks for your help.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    have you ever done a join? try it on the first half of your UNION

    add the category_notes table to this query --
    Code:
    select cat.categoryID     
         , cat.category_name                
         , null as subcategory_name
      from categories as cat
     where cat.parentID = 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll try that. I have doin LEFT JOINS before, but I wasn't sure where to include the join in the query you supplied earlier. Thanks. I'll play around with this. I appreciate it.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, good luck

    don't forget, you will have to join it to both halves of the union
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what does
    order
    by 2,3
    mean? never seen that before

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it means to order the result set by the 2nd column, and within that, by the 3rd column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh ok, so you have to specify the numeric position because the columns have different names on each side of the union...

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't think you have to

    could just as easily say ORDER BY cat.category_name, subcategory_name

    i think
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GENIUS! Thanks so much! It worked great.
    Ok, no more questions from me for a while ;-)

  23. #23
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I thought I was done with this example, but need another hint.

    If I have this output with the query you helped me with:
    HTML Code:
    categoryID     category_name     subcategory_name         category_notes
    1 	               Fruit              Null             good to eat
    6	               Fruit 	 	  Apple           green red etc.
    3 	               Fruit 	 	  Orange          watch.....
    2 	               Vegetables      Null               good for you
    9 	               Vegetables      Carrot            i like the...
    8 	               Vegetables      Onion             don''t cry
    How can I extract just one row where categoryID = X
    For example, how can I get just the apple row:
    HTML Code:
    categoryID     category_name     subcategory_name         category_notes
    6	               Fruit 	 	  Apple           green red etc.
    I know I need a WHERE clause, I just have no idea where to put it.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by thespursfan
    I know I need a WHERE clause, I just have no idea where to put it.
    please show your current query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Enthusiast thespursfan's Avatar
    Join Date
    Sep 2004
    Location
    Texas
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure thing:

    HTML Code:
    SELECT cat.categoryID, cat.category_name, NULL AS subcategory_name, category_notes.category_notes
    FROM categories AS cat
    INNER JOIN category_notes ON cat.categoryID = category_notes.categoryID
    WHERE cat.parentID =0
    UNION ALL
    SELECT subcat.categoryID, cat.category_name, subcat.category_name AS subcategory_name, category_notes.category_notes
    FROM categories AS cat
    INNER
    JOIN categories AS subcat ON cat.categoryID = subcat.parentID
    INNER JOIN category_notes ON subcat.categoryID = category_notes.categoryID
    WHERE cat.parentID =0
    ORDER
    BY 2 , 3


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
  •