[SOLVED] skipping a duplicate table

[envexlabs]

Hey,

 

I have a page that renders out a shopping list that a user has created.

 

the shopping_list table reads like this:

 

mem_id | product_id | cat_id

---------------------------------

    1      |      2        |    1

    1      |      3        |    2

    1      |      5        |    1

    1      |      4        |    2

    1      |      1        |    2

 

the code that renders out the list is:

 

$grab_lists_query = mysql_query('SELECT * FROM `shopping_list` WHERE `mem_id` = ' . $_GET[mem_id] . '') or die(mysql_error());
            
            //grabs all the lists, and renders them out
            while($all_lists = mysql_fetch_row($grab_lists_query)){
                
                //grabs the list name and renders it out
                $grab_list_name_query = mysql_query('SELECT * FROM `list_name` WHERE `list_id` = ' . $all_lists[2] . '') or die(mysql_error());
                
                while($list_name = mysql_fetch_row($grab_list_name_query)){
                    
                    echo'<div class="list_name">

                    <p class="list_nav"><img src="images/icons/page_white_delete.gif" height="16px" width="16px" alt="" /> delete list</p>
                    <p class="list_nav"><img src="images/icons/printer.gif" height="16px" width="16px" alt="" /> print list</p>
                    
                    <h2><a href="javascript:Effect.toggle(\'' . $list_name[0] . '\',\'blind\');">' . $list_name[1] . '</a></h2>
                    
                    </div>';
                    
                    //grabs product info
                    $product_info_query = mysql_query('SELECT * FROM `products` WHERE `product_id` = ' . $all_lists[1] . '');
                    $product_info = mysql_fetch_row($product_info_query);
                    
                    
                    //grabs store info
                    $store_info = select_store($product_info[1]);
                        
                    echo '<div class="list_container" id="' . $list_name[0] . '" style="display: none"><p>';
                    echo '<img src="' . $product_info[5] . '" height="50px" width="50px" alt="" / class="favstore_img_border">';
                    echo '<div class="list_content"><h2>' . $product_info[2] . '</h2></div>'; //product name
                    
                    echo '<div class="list_store_name">
                    <h2><a href="billy.com">' . $store_info[1] . '</a></h2>
                    </div>';
                    
                    echo '<div class="list_store_info">
                    <p>' . $store_info[2] . '</p>
                    <p>' . $store_info[4] . ', ' . $store_info[5] . ', ' . $store_info[6] . '</p>
                    <p>' . $store_info[3] . '</p>
                    </div>';
                             
                    echo '<div class="list_price"><p>$' . $product_info[3] . '</p></div>'; //price
                    echo '</p></div>';
                    
                    //makes the products draggable for sorting
            
                    echo' <script type="text/javascript">
                        new Draggable(\'' . $list_name[0] . '\',{revert:true});
                    </script>';

                    
                }
            }

 

The problem that i'm having is i want php to grab the lists, and populate each list.

 

What i'm getting right now is:

 

List 1

-Product 2

List 2

-Product 3

List 1

-Product 5

List 2

-Product 4

List 2

-Product 1

 

What i want is:

 

List 1

- Product 2

- Product 5

 

List 2

- Product 1

- Product 3

- Product 4

 

What am i doing wrong?

 

Thanks,

 

envex

[BlueSkyIS]

what am i doing wrong?

 

neglecting to use GROUP BY and ORDER BY in your SQL.

[cooldude832]

Order by and then use logic in the mysql to say if(category of this is differerent than last then make a new section)

[Barand]

I don't know the mysql method to make new sections but here's another way

 

<?php
$sql = "SELECT cat_id, product_id 
        FROM `shopping_list` 
        WHERE `mem_id` = {$_GET['mem_id']}
        ORDER BY cat_id, product_id";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

$prev_cat = '';
while (list($cat,$prod) = mysql_fetch_row($res))
{
    if ($prev_cat != $cat)
    {
        echo "<h3>List $cat</h3>";
        $prev_cat = $cat;
    }
    echo "Product $prod<br />";
}
?>