SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, I have a new problem...
    I have two tables. 'Category' (items are 'ID' and 'title') and 'Subcategory' (items are 'ID', 'Cat_ID' and 'title')
    ... I want to make a 'sitemap' (kind of) of the categories and the subcategories... which looks something like this:
    ----
    Category 1 (title)
    ..Subcat 1.1 (title)
    ..Subcat 1.2 (title)
    ..Subcat 1.3 (title)

    Category 2 (title)
    ..Subcat 2.1 (title)
    ..Subcat 2.2 (title)
    ..Subcat 2.3 (title)
    et cetera....
    ----

    I already have a statement for the retrieval of the subcaterogies:

    ----

    <?
    $sql = "SELECT * FROM subcats order by cat";
    $result = mysql_query($sql);

    while ( $row = mysql_fetch_array($result)) {
    echo '<tr bgcolor="#FFFFCC">' . '
    <td width=60 align="center">' . $row["id"] . '</TD>' .
    '<td width=60 align="center">' . $row["cat_ID"] . '</td>' .
    '<td valign="top">' . $row["title"] . ' ' . '</td>' .
    '</tr>';
    }
    exit;
    ?>
    ---

    This shows a table with a column of subcat ID's, a column of cat_ID's and a column of subcat titles.

    Now the true problem lies in the combining of the two tables. Now I could probably link the cat_ID from the Subcategory table with the Category table. But how do I order the list in the way I showed at the beginning of this message.

    Could someone help me with the code to solve this problem?

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay this might work. I don't know if MySQL supports the UNION opperator or if it will allow you to alias a UNION like this but give it a shot.

    For your SELECT statement try:

    SELECT a.*
    FROM (SELECT null as sub_cat_id, cat_id, title FROM category UNION SELECT sub_cat_id, cat_id, title FROM subcategory) a
    ORDER BY cat_id, sub_cat_id

    This should return results similar to:

    SubCatID CatID Title
    NULL 1 Test1
    1 1 Sub1.1
    2 1 Sub1.2
    3 1 Sub1.3
    NULL 2 Test2
    1 2 Sub2.1
    2 2 Sub2.2
    3 2 Sub2.3
    4 2 Sub2.4

    Where SubCatID is null is a "Category", when it has a value it is a "Sub Category".

    Hope this helps.

    BlackCatt

  3. #3
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, that doesn't work... I get the following error-message:

    Warning: Supplied argument is not a valid MySQL result resource in C:\Inetpub\wwwroot\tiemersma\showsubcats1.php on line 15

    ---
    <?php
    include("db_password_ICLON.inc");
    mysql_pconnect("$hostname","$user","$dbpassword");

    mysql_select_db("$user")
    or die ("unable to select database");

    $sql = "SELECT a.*
    FROM (SELECT null as subcats.ID, cats.ID, cats.title FROM cats, subcats
    UNION SELECT subcats.ID, subcats.cat, subcats.title FROM subcats)a
    ORDER BY cats.ID, subcats.ID ";

    $result = mysql_query($sql);

    while ( $row = mysql_fetch_array($result)) {
    echo '<tr bgcolor="#FFFFCC"><td width=60 align="center">' . $row["subcats.ID"] . '</TD>' . '<td width=60 align="center">' . $row["subcats.cat_ID"] . '</td>' . '<td valign="top">' . $row["subcats.title"] . ' ' . '</td>' .
    '</tr>';
    }
    exit;

    ?>
    ----
    Line 15 is the while-statement

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so I went about it a bit different, I opted to use two queies for this one. Here is my code, it basically uses arrays to store the categories and their subcategories, then it loops though the final array to create the effect you want.

    PHP Code:
    //Grab all the main cats and store them in an array
    $result mysql_query("select * from cats order by title");
    while(
    $row mysql_fetch_array($result)) {
        
    extract($row);
        
    $cats[$id] = array("title" => $title,
                           
    "subcats" => array()
                           );
        }

    //Get all the subcats and put them into the array at the corrsponding main cat    
    $result mysql_query("select * from subcats order by title");
    while(
    $row mysql_fetch_array($result)) {
        
    extract($row);
        
    $cats[$catID]["subcats"][$id] = $title;
        }    
        
        
    foreach(
    $cats as $key => $val) {
        print 
    "Category $key ".$cats[$key]["title"]."<br>";
        
    $i 1;
        foreach(
    $cats[$key]["subcats"] as $jey => $jal) {
            print 
    "&nbsp;&nbsp;&nbsp;Subcat $key.$i $jal<br>";
            
    $i++;
            }
        unset(
    $i);    
        print 
    "<br><br>";
        } 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    jazztie,

    I see something wrong with your SELECT statement. I don't know if that is the problem but you may want to try to fix it and try again.

    Your code reads:
    $sql = "SELECT a.*
    FROM (SELECT null as subcats.ID, cats.ID, cats.title FROM cats, subcats
    UNION SELECT subcats.ID, subcats.cat, subcats.title FROM subcats)a
    ORDER BY cats.ID, subcats.ID ";

    (the bold is what I changed)

    Try this:
    $sql = "SELECT a.*
    FROM (SELECT null as ID, cats.ID as cat, cats.title FROM cats
    UNION SELECT subcats.ID, subcats.cat, subcats.title FROM subcats)a
    ORDER BY cats.ID, subcats.ID ";

    I think that might work a little better.
    <fingers crossed>

    BlackCatt

  6. #6
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BlackCatt, I think you may be confusing mySQL for a *real* RDMS AFAIK, mySQL does not yet support UNION nor sub-queries (nested queries).

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    FreakySid,

    You are correct. Those were TWO MAJOR assumptions I was making. Well I guess that shows my experience level with MySQL. Thanks for pointing these things out. Now I don't feel so silly.

    Well Jazztie, I guess its back to the drawing board.

    BlackCatt

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Back to what drawing board?, your solution is sitting right above this post in my reply, its works perfect I just tested it.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Freddy,

    I meant back to the drawing board for a pure query solution. You solution should work fine. I was just trying to do it all in the database. I say if it works use it, so in this case use your solution.

    BlackCatt

  10. #10
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx guys, both of you... I'll try it immediately!

  11. #11
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    uh oh

    I seem to have a problem:

    Using the code from freddy I get the following result: (slighty moderated, the original code had a different problem)

    PHP Code:
    //Grab all the main cats and store them in an array
    $result mysql_query("select * from cats order by id");
    while(
    $row mysql_fetch_array($result)) {
        
    extract($row);
        
    $cats[$id] = array("titel" => $titel,
                           
    "subcats" => array()
                           );
        }

    //Get all the subcats and put them into the array at the corresponding main cat    
    $result mysql_query("select * from subcats order by cat");
    while(
    $row mysql_fetch_array($result)) {
        
    extract($row);
        
    $cats[$cat.ID]["subcats"][$id] = $titel;
        }    
        
        
    foreach(
    $cats as $key => $val) {
        print 
    "Cat $key ".$cats[$key]["titel"]."<br>";
        
    $i 1;
        foreach(
    $cats[$key]["subcats"] as $jey => $jal) {
            print 
    "&nbsp;&nbsp;&nbsp;Subcat $key.$i $jal<br>";
            
    $i++;
            }
        unset(
    $i);    
        print 
    "<br><br>";
        } 
    ----------
    Category 1 University

    Category 2 Education development

    Category 3 Professionalising Education

    Category 4 Organisation

    Category 1ID
    ...Subcat 1ID.1 university of leiden
    ...Subcat 1ID.2 blablablablabla

    Category 2ID
    ...Subcat 2ID.1 education blablabla
    ...Subcat 2ID.2 education blabladidid
    ...Subcat 2ID.3 education blakjlsdl

    Category 3ID

    et cetera, et cetera.
    -----------
    (subcats without the dots at the beginning)

    The tables I have are:
    cats
    - id
    - titel

    subcats
    - id
    - cat
    - titel

    Somehow it does find the names (titel) for the categories, prints them and then it uses the id's for the categories and prints the subcats... we're almost there.

    PS: this is part of a final year project I have to do for my study... (internship at the University)

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This thread has been continued here:
    http://www.sitepointforums.com/showt...threadid=22297

    jazztie, there was no need to start a new thread. It only leads to confusion and frustration. You can always edit your posts if you want to go back and explain things clearer.

  13. #13
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <?
    mysql_select_db($database);
    
    $SQL="select subcats.*,cats.titel AS yaks from subcats  LEFT JOIN cats ON subcats.cats=cats.id ORDER BY yaks,subcats.id";
    $int=mysql_query($SQL);//echo mysql_num_rows($int);
    
    
    while($yak=mysql_fetch_array($int)){	$temp=$yak[yaks];
    	if($temp!=$oldtemp){echo "<b>$yak[yaks]<br></b>";}
        $oldtemp=$temp;
    	echo " $yak[id]: $yak[titel]<br>";
    	}
    ?>
    Not sure how efficient a query like this is , 2 normal ones may be faster ? & to be honest I would have gone down the freddydoesphp route myself , but the above should work ie

    CATNAME
    subcatid:subcat_title
    subcatid:subcat_title
    subcatid:subcat_title
    CATNAME
    etc etc

    ANd while I am here... how do I get the syntax coloured code happening?

  14. #14
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear firepages and freakysid... thanks a lot for your help. It finally works!!!

    Don't know what I should do without the help of PHP gurus like yourself...

    I really appreciate it!

  15. #15
    SitePoint Enthusiast
    Join Date
    Mar 2001
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Firepages,

    While that query will work and is fairly efficient, it doesn't return the exact result set that he is looking for. I did think of a very similar query earlier but opted for the query in my original reply above. While this SELECT statement doesn't return the result set he was originally looking for, the information he is looking for could very easily be extracted from it.

    The query you suggested actually returns the result set in the format of:

    subcatid, catid, title, cattitle
    1, 1, Sub1.1, Category 1
    2, 1, Sub1.2, Category 1
    1, 2, Sub2.1, Category 2
    2, 2, Sub2.2, Category 2
    3, 2, Sub2.3, Category 2

    Something else that may be a problem is that if any Category doesn't have any sub Categories then it won't appear in the result set. To get it to return you would need to modify the query you provided just slightly. Instead of "Left Join" you would actually want "Right Outer Join". With this change if there is a Category that has no sub categories then it would display with Null's like this...

    subcatid, catid, title, cattitle
    1, 1, Sub1.1, Category 1
    2, 1, Sub1.2, Category 1
    1, 2, Sub2.1, Category 2
    2, 2, Sub2.2, Category 2
    3, 2, Sub2.3, Category 2
    Null, Null, Null, Category 3

    Hope this all makes sense to everyone.

    BlackCatt

  16. #16
    ********* wombat firepages's Avatar
    Join Date
    Jul 2000
    Location
    Perth Australia
    Posts
    1,717
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    On your second point, you are right, in that if there are no sub-categories the main category will not get listed, how important this is depends on the script, in saying that ideally you would return the empty categories instead, RIGHT OUTER JOIN sounds a bit painful to me - i.e never done one yet

    As for the result set, well yes and no, I am not sure how (or if) with MySQL you could return the exact result set required.

    So the for loop formats the result to print as requested by checking if the category name has changed or not, this can be easily changed if necc'y , I would also normally use mysql_fetch_row but thought fetch_array would be easier (on the eye) to play with.

    In any case you would have to manipulate the result set to display as needed, and this is why I prefer the 2 queries freddy suggested, which the more I think of it is probably faster than the JOIN and easier to manipulate.

  17. #17
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    jazztie, my code worked perfectly for me and gave me the rsults you were looking for, I know you already have it fixed in another fashion but your culprit was on this line

    PHP Code:
        $cats[$cat.ID]["subcats"][$id] = $titel
    and it should read

    PHP Code:
        $cats[$catID]["subcats"][$id] = $titel

    There are a few other things, that I imagine happened when porting my code to fit your db setup and such. But none the less my methodd does work. To prove it, I'll show you, here is the working code and the source code

    http://www.irq11.com/freddy/d.php
    http://www.irq11.com/freddy/d.phps
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  18. #18
    SitePoint Evangelist jazztie's Avatar
    Join Date
    Mar 2001
    Location
    the Netherlands
    Posts
    519
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Freddy,

    It does work... even better than the code the others provided. (This overview has <br> between the categories)

    Thanks a lot... I really appreciate it!


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
  •