generating lists and excluding unpopulated lists

[envexlabs]

Hey,

 

I will try to explain this as best as i can.

 

Right now i have this code:

 

$select_tags_query = mysql_query('SELECT * FROM `tag_category`');
                
                while($tag_category = mysql_fetch_row($select_tags_query))
                {
                    
                    $tags_query = mysql_query('SELECT * FROM `tags` WHERE `cat_id` = ' . $tag_category[0] . '');
                                                      
                    echo '<div class="tag_cat">';
                
                    //renders out the category name
                    
                    echo '<h3>' . $tag_category[1] . '</h3>';
                    
                    //renders out each tag in the category
                    
                    while($tags = mysql_fetch_row($tags_query))

                    {   
                    
                        $tag_used = mysql_query('SELECT * FROM `store_tags` WHERE `tag_id` = ' . $tags[0] . '');
                    
                        if(mysql_num_rows($tag_used) == 0){
                            //do nothing
                        }else{
                            echo '<p class="tag_check"><a href="search.php?tag=' . $tags[0] . '">' . $tags[2] . '</a></p>';
                        }                     
                        
                    }
                    
                    echo '</div> <!-- tag_cat div -->';
                    
                }

 

Which renders out all the catagories, and then only tags that have been selected by users of the site. ie:

 

Cat One

 

Cat Two

 

Cat 3

Tag Tag Tag Tag

 

Cat 4

Tag

 

Cat 5

 

Cat 6

 

The way the database is setup is:

 

store_tags

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

store_id | tag_id

 

tags

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

tag_id | cat_id | tag_name

 

tag_cat

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

cat_id | cat_name

 

The problem i am having is, how to only render out categories that have tags that are used.

[kernelgpf]

Try this- is this what you mean?

 

$select_tags_query = mysql_query('SELECT * FROM `tag_category`');
                
                while($tag_category = mysql_fetch_row($select_tags_query))
                {
                    
                    $tags_query = mysql_query('SELECT * FROM `tags` WHERE `cat_id` = ' . $tag_category[0] . '');
if(mysql_num_rows($tags_query) == "0"){
continue;
}
                                                      
                    echo '<div class="tag_cat">';
                
                    //renders out the category name
                    
                    echo '<h3>' . $tag_category[1] . '</h3>';
                    
                    //renders out each tag in the category
                    
                    while($tags = mysql_fetch_row($tags_query))

                    {   
                    
                        $tag_used = mysql_query('SELECT * FROM `store_tags` WHERE `tag_id` = ' . $tags[0] . '');
                    
                        if(mysql_num_rows($tag_used) == 0){
                            //do nothing
                        }else{
                            echo '<p class="tag_check"><a href="search.php?tag=' . $tags[0] . '">' . $tags[2] . '</a></p>';
                        }                     
                        
                    }
                    
                    echo '</div> <!-- tag_cat div -->';
                    
                }

[envexlabs]

Hey,

 

That didn't seem to do the trick.

 

If you look right now:

 

http://www.werehavingasale.com/moretags.php

 

it's display every category, i only want to show the categories that have tags.

[kernelgpf]

Hmm.. okay- what does the "tags" table do that's different from the "store_tags" table?

[envexlabs]

Store tags holds the information for each store.

 

ie. store_id and tag_id

 

The tags table holds the tag_id, tag_name, and cat_id.

[pocobueno1388]

Give this a try

 

<?php

$query = "SELECT t.cat_name
            FROM tag_cat t
            INNER JOIN tags
            ON tags.cat_id = t.cat_id";
            
$select_tags_query = mysql_query($query)or die(mysql_error());

while ($tag_category = mysql_fetch_assoc($select_tags_query)) {
    
    $tags_query = mysql_query('SELECT * FROM `tags` WHERE `cat_id` = ' . $tag_category['cat_id'] . '');
    
    echo '<div class="tag_cat">';
    
    //renders out the category name
    
    echo '<h3>' . $tag_category['cat_name'] . '</h3>';
    
    //renders out each tag in the category
    
    while ($tags = mysql_fetch_assoc($tags_query)) {
            echo '<p class="tag_check"><a href="search.php?tag=' . $tags['tag_id'] . '">'. $tags['tag_name'] .'</a></p>';
        }
        
    }
    
    echo '</div> <!-- tag_cat div -->';
    

?>

[pocobueno1388]

I see you have an error on the page now that says

Unknown table 'tag_category' in on clause

 

I think I saw in your previous code that the table was actually named "tag_categories", so just change this

 

$query = "SELECT t.cat_name
            FROM tag_cat t
            INNER JOIN tags
            ON tags.cat_id = t.cat_id";

 

To

 

$query = "SELECT t.cat_name
            FROM tag_categories t
            INNER JOIN tags
            ON tags.cat_id = t.cat_id";

 

Or if "tag_categories" isn't the name either, just change it to whatever it is.

[envexlabs]

Hey,

 

I've tried your code, but i got an error, so i print_r'd $tag_category and all i'm getting is the 'cat_name'.

[pocobueno1388]

Ah, sorry...I guess you need the cat_id as well.

 

Just add it to the query

 

$query = "SELECT t.cat_name, t.cat_id
            FROM tag_cat t
            INNER JOIN tags
            ON tags.cat_id = t.cat_id";

[envexlabs]

Lets see if i can explain this fully (sidenote: not being an asshole)

 

store_tags hold the tag_id each store has selected.

 

store_id | tag_id

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

    2      |    3

    2      |    12

    3      |    15

    4      |    28

 

tags holds the tag_id, name for each tag, and the cat_id it belongs to.

 

tag_id | name | cat_id

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

    3  | men        |    2

  12  | women    |    3

  16  | children  |    4

 

 

tag_category holds the cat_id and name for each category.

 

cat_id | cat_name

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

  2    |  shirts

  3    |  pants

  4    |  hats

 

I need to render out a list like so:

 

Category Name

Tag Tag Tag

 

The thing is, i only want to display categories that have tags that stores are using.

 

I setup the database not knowing that the client wanted it this way, so i've done this completely the wrong way.

 

Thanks for all your help, it's greatly appreciated!

 

[pocobueno1388]

Okay, here is some code I put together that has none of your formatting in it...but I am just trying to get your desired results for now.

 

<?php

$query = "SELECT t.cat_name, t.cat_id
FROM tag_category t
INNER JOIN tags
ON tags.cat_id = t.cat_id";

$result = mysql_query($query)or die(mysql_error());

while ($category = mysql_fetch_assoc($result)){
   echo "The </b>{$category['cat_name']}</b> category<br>";
   
   $get_tags = "SELECT * FROM tags WHERE cat_id='{$category['cat_id']}'";
   $tags = mysql_query($get_tags)or die(mysql_error());
   
   while ($tag = mysql_fetch_assoc($tags)){
      echo $tag['tag_name'].', ';
   }
   
echo '<p>';
}

?>

 

See if thats what you want. If there is something wrong with it, it has to be that first query in the code...I'm not completely positive thats how you would do it...I think it is though.

[envexlabs]

thanks man,

 

i will try it after lunch!

[envexlabs]

Still no dice.

 

I'm going to skip this for now, have other parts to develop

 

Thanks again.