Difficult PHP MySQL while loop for html list

Question

I have been working on this for what seems like forever, so I finally decided to ask for help. I am trying to write an html list that is populated by info in a mysql database. I am trying to write a php loop (or multiple loops) to accomplish this. It begins to get complicated because there a multiple nodes to the list. I have tried many different methods but cannot get the desired results. Any help would be greatly appreciated!

The list is populated by items that have various "categories" and "subcategories". The list should be in ASC order by customer name, then category, then subcategory, then part number. Some items have only a category and no subcategory. Some items have no category and should just be listed under the customer's name. So the list should look like this...

Customer1
    - Category1
        - Subcategory1
                - Part1 (Item w/ Category & Subcategory)
                - Part2
        - Subcategory2
                - Part3
    - Category2
        - Part4 (Item with only a Category)
        - Part5
    - Part6 (Item with no Category or Subcategory
    - Part7
Customer2
    - Category1
        - Subcategory1
                - Part1 (Item w/ Category & Subcategory)
                - Part2
        - Subcategory2
                - Part3

Etc......

Hopefully that is clear enough.

Here is my first try with this problem and it comes close. It just places items in the wrong places (not sure why).

    <?php
    $con = mysql_connect("localhost:3306","root","");
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("pcu_ops", $con);

    $sql_customer="SELECT DISTINCT customer FROM common_parts ORDER BY customer ASC";

    $result_customer=mysql_query($sql_customer,$con);

    if (!mysql_query($sql_customer,$con))
    {
        die('Error: ' . mysql_error());
    }
?>
<table border="0">
    <colgroup>
        <col width="300px" valign="top">
        <col width="90%">
    </colgroup>
    <tr>
        <td valign="top">
            <!-- Add a <div> element where the tree should appear: -->
            <div id="common_parts_tree">
                <ul>
                    <?php
                        while ($row_customer = mysql_fetch_array($result_customer)) {
                            echo '<li class="expanded folder">'.$row_customer['customer'];
                                echo '<ul>';
                                    $customer=$row_customer['customer'];
                                    $sql_category="SELECT DISTINCT category FROM common_parts WHERE customer='$customer' ORDER BY customer ASC";
                                    $result_category=mysql_query($sql_category,$con);
                                    if (!mysql_query($sql_category,$con)) {
                                        die('Error: ' . mysql_error());
                                    }
                                    while ($row_category = mysql_fetch_array($result_category)) {
                                        if ($row_category['category'] != '') {
                                            echo'<li class="expanded folder">'.$row_category['category'];
                                                echo '<ul>';
                                                    $category=$row_category['category'];
                                                    $sql_subcategory="SELECT DISTINCT subcategory FROM common_parts WHERE (customer='$customer' AND category='$category') ORDER BY subcategory ASC";
                                                    $result_subcategory=mysql_query($sql_subcategory,$con);
                                                    if (!mysql_query($sql_subcategory,$con)) {
                                                        die('Error: ' . mysql_error());
                                                    }
                                                    while ($row_subcategory = mysql_fetch_array($result_subcategory)) {
                                                        if ($row_subcategory['subcategory'] != '') {
                                                            echo'<li class="expanded folder">'.$row_subcategory['subcategory'];
                                                                echo '<ul>';
                                                                    $subcategory=$row_subcategory['subcategory'];
                                                                    $sql_pn="SELECT DISTINCT pn FROM common_parts WHERE (customer='$customer' AND category='$category' AND subcategory='$subcategory') ORDER BY pn ASC";
                                                                    $result_pn=mysql_query($sql_pn,$con);
                                                                    if (!mysql_query($sql_pn,$con)) {
                                                                        die('Error: ' . mysql_error());
                                                                    }
                                                                    while ($row_pn = mysql_fetch_array($result_pn)) {
                                                                        $pn=$row_pn['pn'];
                                                                        echo '<li><a href="includes/phpscripts/part_quick_view.php?pn='.$pn.'&customer='.$customer.'" target="contentFrame">'.$pn.'</a>';
                                                                    }
                                                                echo '</ul>';
                                                        }
                                                        else {
                                                            if ($row['subcategory'] == '') {
                                                                $sql_pn="SELECT DISTINCT pn FROM common_parts WHERE (customer='$customer' AND category='$category') ORDER BY pn ASC";
                                                                $result_pn=mysql_query($sql_pn,$con);
                                                                if (!mysql_query($sql_pn,$con)) {
                                                                    die('Error: ' . mysql_error());
                                                                }
                                                                while ($row_pn = mysql_fetch_array($result_pn)) {
                                                                    $pn=$row_pn['pn'];
                                                                echo '<li><a href="includes/phpscripts/part_quick_view.php?pn='.$pn.'&customer='.$customer.'" target="contentFrame">'.$pn.'</a>';
                                                                }
                                                            }
                                                        }
                                                    }
                                                echo '</ul>';
                                        }
                                        else {
                                            echo '<li><a href="includes/phpscripts/part_quick_view.php?pn='.$pn.'&customer='.$customer.'" target="contentFrame">'.$pn.'</a>';
                                        }
                                    }
                                echo '</ul>';
                        }
                    ?>
            </div>
        </td>
        <td>
            <iframe src="" name="contentFrame" width="100%" height="500" scrolling="yes" marginheight="0" marginwidth="0" frameborder="0">
                <p>Your browser does not support iframes</p>
            </iframe>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <center>
                <form id="rcv_common_parts">
                <input type="hidden" id="pn" name="pn"/>
                <input type="hidden" id="customer" name="customer"/>
                <table class="table">
                    <tr>
                        <td>Quantity to Receive:</td>
                        <td><input type="text" name="qty" /></td>
                    </tr>
                </table>
                </form>
            </center>
        </td>
    </tr>
</table>

This is my most recent attempt. I gave up on the first method and have started trying with this file. Still haven't had any luck.

<?php
    $con = mysql_connect("localhost:3306","root","");
    if (!$con)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("pcu_ops", $con);

    $sql="SELECT * FROM common_parts ORDER BY customer ASC, category ASC, subcategory ASC, pn ASC";

    $result=mysql_query($sql,$con);

    if (!mysql_query($sql,$con))
    {
        die('Error: ' . mysql_error());
    }
?>
<table border="0">
    <colgroup>
        <col width="300px" valign="top">
        <col width="90%">
    </colgroup>
    <tr>
        <td valign="top">
            <!-- Add a <div> element where the tree should appear: -->
            <div id="common_parts_tree">
                <ul>
                    <?php
                        $row = mysql_fetch_array($result);
                            echo '<li class="expanded folder">'.$row['customer'];
                                echo '<ul>';
                                    while (($row['category'] != NULL) && ($row['subcategory'] != NULL)) {
                                        echo '<li class="expanded folder">'.$row['category'];
                                            echo '<ul>';
                                                echo '<li class="expanded folder">'.$row['subcategory'];
                                                    echo '<ul>';
                                                        echo '<li><a href="includes/phpscripts/part_quick_view.php?pn='.$row['pn'].'&customer='.$row['customer'].'" target="contentFrame">'.$row['pn'].'</a>';
                                                    echo '</ul>';
                                            echo '</ul>';
                                    }
                                echo '</ul>';
                    ?>
            </div>
        </td>
        <td>
            <iframe src="" name="contentFrame" width="100%" height="500" scrolling="yes" marginheight="0" marginwidth="0" frameborder="0">
                <p>Your browser does not support iframes</p>
            </iframe>
        </td>
    </tr>
    <tr>
        <td colspan="2">
            <center>
                <form id="rcv_common_parts">
                <input type="hidden" id="pn" name="pn"/>
                <input type="hidden" id="customer" name="customer"/>
                <table class="table">
                    <tr>
                        <td>Quantity to Receive:</td>
                        <td><input type="text" name="qty" /></td>
                    </tr>
                </table>
                </form>
            </center>
        </td>
    </tr>
</table>

Sure hope someone can help!

Thanks!

Ciro · Accepted Answer · 2012-05-13 18:32:45Z

You should implement recursive algorithm.

A tip for database... Simple example with categories and parts only:

 - Category1
        - Subcategory1
                - Part1
                - Part2
        - Subcategory2
                - Part3
    - Category2
        - Subcategory3
                - Part4
        - Part5
        - Part6
    - Part7 
    - Part8

Categories_table
id name          parent     user_id
1  category 1    0          1
2  category 2    0          1
3  Subcategory1  1          1
4  Subcategory2  1          1
5  Subcategory3  2          1

Parts_table
name     category
Part1    3
Part2    3
Part3    4
Part4    5
Part5    2
Part6    2
Part7    0
Part8    0

You get the point...

Then for show:

 $query_user = mysql_query("select * from users",$con);
while($row_user = mysql_fetch_array($query_user)) {
    echo "<ul>".$row_user['id'];
    show_category($row_user['id'],0);
    echo "</ul>";
}
function show_category($user,$parent){
    global $con;
    echo "<ul>";
    $query_cat = mysql_query("select * from categories where user_id=".$user." and parent=".$parent,$con);
    while($row_cat = mysql_fetch_array($query_cat)) {
        echo "<li>".$row_cat['name'];
        show_category($user,$row_cat['id']);
        echo "</li>";       
    }
    $query_parts = mysql_query("select * from parts where category=".$parent,$con);
    while($row_parts = mysql_fetch_array($query_parts)) {
        echo "<li>".$row_parts['name']."</li>";
    }
    echo "</ul>";
}

I took your suggestion and tried it. I changed it to reflect my column names in my database and it works great...except 1 thing. Parts with no category or subcategory print for both customers instead of just the customer they are actually for. I am going to try to figure out why, but I thought I might ask anyway. Thanks again for the help!
That's because first time function executes takes parameter parent=0. Create a Main category, like a placeholder, for all customers and you will avoid that.
Thanks again for the advice. It works perfectly! Saved me so much time!

pogeybait · Answer 2 · 2012-05-13 21:40:13Z

This is going to be a long answer but I think I can help you. First let's get a couple of things straight about how you will need to setup your database. I know your example says 'customers' but for my example below I will call them 'users':

1) You have a users table. That table should have an 'id' field that is an int 11, primary key, auto-increment. You will have more fields but the 'id' is all I am concerned with.
2) You need a 'categories' table to determine if the category is a "parent" category or "child" category. The structure should basically be something like 'id' (int 11, primary key, auto-increment), 'name' (varchar), 'user_id' (int 11), 'parent_id' (int 11). In your requirements above, Category 1, Category 2, etc... are "parent" categories so the parent_id should be 0 meaning they have no parent. For subcategories, you should use the id of the category that is it a subcategory of. For example if Category 1 has an id of 1, a sub-category of that category would have a parent_id of 1.
3) Finally you have a parts table. Again have an 'id' (int 11, primary key, auto-increment), 'user_id' (int 11), and a 'category_id' (int 11). You can obviously put other fields here but those are the three I am concerned with. If the part is not in a category, category_id is 0, otherwise use the id of the category or subcategory.

That gives you a sound database structure for mapping relationships for users, categories, and parts. Now, how do you get all that data into something usable? An array. Build an associative array that helps you display the data like you want it. Without writing a million lines of PHP, here is the gist.

$arr_user = array();
$sql = mysql_query('SELECT * FROM users ORDER BY name ASC', $con);
while($user = mysql_fetch_object($sql))
{
    $arr_user[$user->id]['name'] = $user->name;
    $sql2 = mysql_query('SELECT * FROM parts WHERE user_id='.$user->id.' AND category_id=0 ORDER BY name ASC', $con);
    while($part = mysql_fetch_object($sql2))
    {
        $arr_user[$user->id]['parts'][$part->id] = $part->name;
    }
    $sql3 = mysql_query('SELECT * FROM categories WHERE user_id='.$user->id.' AND parent_id=0 ORDER BY name', $con);
    while($category = mysql_fetch_object($sql3))
    {
        $sql4 = mysql_query('SELECT * FROM parts WHERE user_id='.$user->id.' AND category_id='.$category->id.' ORDER by name', $con);
        while($part = mysql_fetch_object($sql4))
        {
            $arr_user[$user->id]['category'][$category->id]['name'] = $category->name;
            $arr_user[$user->id]['category'][$category->id]['parts'][$part->id] = $part->name;
        }
        $sql5 = mysql_query('SELECT * FROM categories WHERE parent_id='.$category->id.' ORDER BY name', $con);
        while($subcat = mysql_fetch_object($sql5))
        {
            $sql6 = mysql_query('SELECT * FROM parts WHERE category_id='.$subcat->id.' ORDER BY name', $con);
            while($part = mysql_fetch_array($sql6))
            {
                $arr_user[$user->id]['category']['subcat'][$subcat->id]['name'] = $subcat->name;
                $arr_user[$user->id]['category']['subcat'][$subcat->id]['parts'][$part->id] = $part->name;
            }
        }
    }
}

if( sizeof( $arr_user ) )
{
    foreach( $arr_user as $k=>$v )
    {
        echo $v['name']; //echo the user's name
        if( isset( $v['category'] ) && sizeof( $v['category'] ) )
        {
            foreach( $v['category'] as $ck=>$cv )
            {
                echo $cv['name']; //echo category name
                if( isset( $cv['subcat']) && sizeof( $cv['subcat'] ) )
                {
                   foreach( $cv['subcat'] as $sk=>$sv )
                   {
                       echo $sv['name']; //echo subcat name
                       if( isset( $sv['parts'] ) && sizeof( $sv['parts'] ) )
                       {
                           foreach( $sv['parts'] as $spk=>$spv )
                           {
                               echo $spv; //echo part name
                           }
                       }
                   }
                }
                if( isset($cv['parts']) && sizeof($cv['parts']) )
                {
                    foreach( $cv['parts'] as $cpk=>$cpv )
                    {
                        echo $cpv; //echo part name
                    }
                }
            }
        }
        if( isset($v['parts']) && size($v['parts']) )
        {
            foreach( $v['parts'] as $pk=>$pv )
            {
                echo $pv;
            }
        }
    }
}

Thanks for your help. I am trying to work through this code you provided and change it fit my variable names, etc. I am (obviously) not an experienced php programmer, so I am trying to make sense of the code. I have copied and pasted it into my file and have commented out everything but the first 5 lines (and the closing bracket for the while statement. Why would it continually loop and never stop? Shouldn't it stop looping after it is at then end of the db (which is really small for now).
You can't use $sql = "select..."and while($row = mysql_fetch_object(mysql_query($sql,$con))). You need to use $sql = mysql_query("select...",$con)and while($row = mysql_fetch_object($sql)). You can check my answer if you want, I've tested it and works exactly as you wanted.
Correct. I wrote the code above like at 2am and its probably not my best work. mysql_fetch_object() operates on a result set.
@bwlange3 if my answer did answer your question, please one-up my response. Otherwise you are marking this issue as unanswered.

Yoav Kadosh · Answer 3 · 2012-05-13 05:22:19Z

The first code you posted seemed ok, but it was too long and I couldnt figure it out. basiclly all you need is a while loop inside a while loop and so on for all the categories, subcategories etc...

I dont know your database structure, so you might need to change some of the columns name but other than that this should work:

$query1 = "the customer query";
while($customerRow = mysql_fetch_array(mysql_query($query1,$con))) {
    echo '<ul><li>';
    echo $customerRow['customer'];
    $query2 = "the category query";
    while($categoryRow = mysql_fetch_array(mysql_query($query2,$con))) {
        echo '<ul><li>';
        echo $categoryRow['category'];
        $query3 = "the subcategory query";
        while($subcategoryRow = mysql_fetch_array(mysql_query($query3,$con))) {
            echo '<ul><li>';
            echo $subcategoryRow['subcategory'];
            echo '</li></ul>';
        }
        echo '</li></ul>';
    }
    echo '</li></ul>';
}

asked	11 months ago
viewed	680 times
active	11 months ago

Difficult PHP MySQL while loop for html list

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql while-loop html-lists or ask your own question.

Community Bulletin

Difficult PHP MySQL while loop for html list

3 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql while-loop html-lists or ask your own question.

Community Bulletin

Related