#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    51
    Rep Power
    14
    I have been working on a problem here for sometime now. I have a MySQL data base that holds a list of categories I have on my site I want to display this list in a directory tree.
    IE:
    --------------------
    --Dog Stuff
    -----Dog Toys
    --------Chewies
    --------Balls
    ------Dog Food
    --------Dry Food
    --------Can Food
    --Cat Stuff
    ---------------------
    I also want to only display 25 at a time.
    Here is the code I have so far.
    The problem Im having is, listing 25 at a time.
    CODE...
    -------------------------------------------
    <HTML>
    <HEAD>
    <TITLE></TITLE>
    </HEAD>
    <BODY bgcolor="#FFFFFF" link="#0000C0" alink="#0000C0" vlink="#0000C0" text="#000000">
    <? include("config.php"); ?>

    <?
    $limit=5; // rows to return
    $numresults=mysql_query("select CID from CATS",$db);
    $numrows=mysql_num_rows($numresults);

    // next determine if offset has been passed to script, if not use 0
    if (empty($offset) &#0124; &#0124; $page < 0) {
    $offset=0;
    }

    $begin =($offset+1);
    $end = ($begin+($limit-1));
    if ($end > $numrows) {
    $end = $numrows;
    }
    ?>
    <TABLE width="100%" border="0" cellspacing="0" cellpadding="2">
    <TR>
    <TD><STRONG><FONT size="2" face="Arial,Lucida">
    <?
    if ($offset >= 1) { // bypass PREV link if offset is 0
    $prevoffset=$offset - $limit;
    print("<A href="categories.php?offset=$prevoffset">PREV </A> &nbsp;");
    }
    else { print("<FONT color="#DDDDDD">PREV </FONT>"); }

    // calculate number of pages needing links
    $pages=intval($numrows/$limit);

    if ($pages < ($numrows/$limit)){
    $pages=((intval($numrows/$limit)) + 1);
    }


    for ($i = 1; $i <= $pages; $i++) {
    $newoffset = $limit*($i-1);
    if ($newoffset == $offset) {
    print("$i&nbsp;");
    }
    else {
    print("<A href="categories.php?offset=$newoffset">$i</A>&nbsp;");
    }
    }

    if (! ( ($offset/$limit) == ($pages - 1) ) && ($pages != 1) ) {
    $newoffset = $offset + $limit;
    print "<A href="categories.php?offset=$newoffset"> NEXT</A>";
    }
    else { print("<FONT color="#DDDDDD"> NEXT</FONT>"); }
    ?>
    </STRONG></FONT>
    </TD>
    <TD colspan="4" align="right"><FONT size="2" face="Arial,Lucida"><A href="insert.cat.php">Insert Top Category</A></FONT>
    </TD>
    </TR>
    <TR bgcolor="#CC0000">
    <TH align="left" width="75%"><FONT color="#FFFFFF" size="2" face="Arial,Lucida">Categories: <? print( $begin." - ".$end." of ".$numrows ); ?></FONT>
    </TH>
    <TH align="center"><FONT color="#FFFFFF" size="2" face="Arial,Lucida">Allow</FONT>
    </TH>
    <TH align="center"><FONT color="#FFFFFF" size="2" face="Arial,Lucida">Edit</FONT>
    </TH>
    <TH align="center"><FONT color="#FFFFFF" size="2" face="Arial,Lucida">Insert</FONT>
    </TH>
    <TH align="center"><FONT color="#FFFFFF" size="2" face="Arial,Lucida">Delete</FONT>
    </TH>
    </TR>
    <?

    $get_top=mysql_query("select CID, PID, CAT, ALW from CATS where PID='0' order by CAT asc limit $offset,$limit",$db);
    while($cat_row=@mysql_fetch_array($get_top)){

    $row_cid=$cat_row[CID];
    $row_cat=$cat_row[CAT];
    $row_pid=$cat_row[PID];
    $row_alw=$cat_row[ALW];
    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";

    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");

    // ------------------- SUB ONE CATEGORIES -------------------------- //

    $get_subs_1=mysql_query("select CID, PID, CAT, ALW from CATS where PID='$row_cid' order by CAT asc limit $offset,$limit",$db);
    while($sub_row=@mysql_fetch_array($get_subs_1)){

    $row_cid=$sub_row[CID];
    $row_cat=$sub_row[CAT];
    $row_pid=$sub_row[PID];
    $row_alw=$sub_row[ALW];

    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";
    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<FONT color="$cell_color">---</FONT>+ "."<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");



    // ------------------- SUB TWO CATEGORIES -------------------------- //

    $get_subs_2=mysql_query("select CID, PID, CAT, ALW from CATS where PID='$row_cid' order by CAT asc limit $offset,$limit",$db);
    while($sub_row=@mysql_fetch_array($get_subs_2)){

    $row_cid=$sub_row[CID];
    $row_cat=$sub_row[CAT];
    $row_pid=$sub_row[PID];
    $row_alw=$sub_row[ALW];

    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";
    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<FONT color="$cell_color">------</FONT>+ "."<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");



    // ------------------- SUB THREE CATEGORIES ------------------------ //

    $get_subs_3=mysql_query("select CID, PID, CAT, ALW from CATS where PID='$row_cid' order by CAT asc limit $offset,$limit",$db);
    while($sub_row=@mysql_fetch_array($get_subs_3)){

    $row_cid=$sub_row[CID];
    $row_cat=$sub_row[CAT];
    $row_pid=$sub_row[PID];
    $row_alw=$sub_row[ALW];

    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";
    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<FONT color="$cell_color">---------</FONT>+ "."<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");



    // ------------------- SUB FOUR CATEGORIES -------------------------- //

    $get_subs_4=mysql_query("select CID, PID, CAT, ALW from CATS where PID='$row_cid' order by CAT asc limit $offset,$limit",$db);
    while($sub_row=@mysql_fetch_array($get_subs_4)){

    $row_cid=$sub_row[CID];
    $row_cat=$sub_row[CAT];
    $row_pid=$sub_row[PID];
    $row_alw=$sub_row[ALW];

    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";
    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<FONT color="$cell_color">------------</FONT>+ "."<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");



    // ------------------- SUB FIVE CATEGORIES -------------------------- //

    $get_subs_5=mysql_query("select CID, PID, CAT, ALW from CATS where PID='$row_cid' order by CAT asc limit $offset,$limit",$db);
    while($sub_row=@mysql_fetch_array($get_subs_5)){

    $row_cid=$sub_row[CID];
    $row_cat=$sub_row[CAT];
    $row_pid=$sub_row[PID];
    $row_alw=$sub_row[ALW];

    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";
    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<FONT color="$cell_color">---------------</FONT>+ "."<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");


    // ------------------- SUB SIX CATEGORIES -------------------------- //

    $get_subs_6=mysql_query("select CID, PID, CAT, ALW from CATS where PID='$row_cid' order by CAT asc limit $offset,$limit",$db);
    while($sub_row=@mysql_fetch_array($get_subs_6)){

    $row_cid=$sub_row[CID];
    $row_cat=$sub_row[CAT];
    $row_pid=$sub_row[PID];
    $row_alw=$sub_row[ALW];

    $row_alw=="Y" ? $font_color="#00C000" : $font_color="#CC0000";
    $cc++;
    $cell_color = "#eeeeee";
    $cc % 2 ? 0 : $cell_color = "#dddddd";
    print(" <TR bgcolor="".$cell_color."">n");
    print(" <TD align="left" valign="center"><FONT face="Lucida,Arial" size="2">");
    print("<FONT color="$cell_color">------------------</FONT>+ "."<A href="edit.apps.php?cat=".$row_cid."">".$row_cat);
    print("</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT color="".$font_color."" face="Lucida,Arial" size="2">".$row_alw."</FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="edit.cat.php?cid=".$row_cid."">Edit</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="insert.cat.php?pid=".$row_cid."">Insert</A></FONT>n");
    print(" </TD>n");
    print(" <TD align="center"><FONT face="Lucida,Arial" size="2"><A href="delete.cat.php?cid=".$row_cid."">Delete</A></FONT>n");
    print(" </TD>n");
    print(" </TR>n");


    }
    }
    }
    }
    }
    }
    }

    ?>
    </TABLE>
    </BODY>
    </HTML>
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    London/UK
    Posts
    91
    Rep Power
    14
    Hi

    sorry I've not fully read your post as it was miles too long, but I guess you've got a parent/child relationship going on here..?

    I had a similar problem recently, which was driving me insane as the long and verbose way I was doing it was killing my server and limiting me on the depth of my 'branches', I opted to read everything into a 2D array thus:

    $sql = "select parentID,categoryID,categoryname from table <some limiting clause>";
    $res= mysql_query($sql);
    $error = mysql_error();
    if($error) {
    print "nDoh! " . $error;
    exit;
    } else {
    while ($loop = mysql_fetch_array($res)) {
    $matrix[$loop["parentID"]][$loop["categoryID"]] = $loop["categoryname"];
    }
    }

    right, there's just one mysql query with all the info I need in memory now I want to sort them out nicely.

    This is where I came unstuck, I was nearly there, but a few guys on the php mailing list helped me out, I therefore deliver back into the community =)


    function display_indented($array,$parent,$level=0) {

    $indent = "----->";
    unset($prefix);
    for ($x=0;$x<$level;$x++) {
    $prefix .= $indent;
    }
    $level++;

    while (list ($catID, $category) = each ($array[$parent])) {
    print $prefix . $category;
    if($array[$catID]){
    display_indented($array,$catID,$level);
    }
    }
    }

    now, to get your display, just call:

    display_indented($matrix,0);

    hth

    Bealers

    ------------------
    http://back-end.org

    [This message has been edited by Bealers (edited August 12, 2000).]
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2000
    Location
    London/UK
    Posts
    91
    Rep Power
    14
    Oops, I just read your post again

    well only showing the first 25 is difficult, first 25 of what, top level, second level etc..?

    I suggest you LIMIT by an arbitrary amount and then page between them, you could maybe cut-off when you get to a record with a parentID of 0

    Bealers
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    51
    Rep Power
    14
    Well all categories will be in alphbetical
    order sorted by the directory tree.
    I have gotten the directory tree to work out
    so up to six subcategories can be added.
    I'm just stuck only displaying only 25 at a time I would hate to have to load a page that held 1500 rows on one page was hoping someone had already tackeled this problem.
    I will take a look at your code but if you load all categories in a array the that would
    burn alot of memory, that's the way I see it anyway.

    Charlie...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    Seaside, CA
    Posts
    84
    Rep Power
    14
    I'm afraid I haven't read all that code either, but is there some reason "LIMIT 0,25" won't work in your SELECT statement?

    ------------------
    From the day we're born, we're running out of time.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    51
    Rep Power
    14
    No Limit 0,25 seems to work ok.
    I have not had a problem with a MySQL
    error. Now that you point that out it seems to me I should be getting an error.

    I am still working of other ways to solve this problem.

    Charlie...

Similar Threads

  1. Replies: 2
    Last Post: August 18th, 2004, 10:45 PM
  2. Replies: 3
    Last Post: February 11th, 2004, 01:33 PM
  3. functions slowing code
    By dave2k in forum PHP Development
    Replies: 5
    Last Post: February 7th, 2004, 06:40 PM
  4. Ensuring a file's code doesn't cause error in another.
    By Jason Doucette in forum C Programming
    Replies: 0
    Last Post: February 6th, 2004, 12:51 PM
  5. vb code error combo box
    By xyfix in forum Visual Basic Programming
    Replies: 15
    Last Post: January 27th, 2004, 09:04 AM

IMN logo majestic logo threadwatch logo seochat tools logo