Categories/Sub Categories

[phpstarter123]

Hi there .  I have figured out a way to make the proper products display under the proper subcategory, but now I need the proper subcategory to display under the proper category.  If there is no subcategory, then the products should display under the category.  I don't quite know how I can do the last of these two.  Here is what I have for the products under subcategories....I am trying to use spry tabbed panels:

 

<div id="TabbedPanels1" class="TabbedPanels">
  <ul class="TabbedPanelsTabGroup">
<?php
$result = mysql_query("SELECT subcategory FROM products WHERE subcategory !='none' GROUP BY subcategory");
$num=mysql_num_rows($result);
if (mysql_num_rows($result) > 0)
{
while ($row = mysql_fetch_array($result)) {

$items[] = $row[0]; // $row[0] contains one category ie: Main Retail
?>
<li class="TabbedPanelsTab" tabindex="0"><?php echo $row[0]; ?></li>

<?php } } ?>
  </ul>
  <div class="TabbedPanelsContentGroup">
    
  <?php


// the submenu is built, one thing left to do!
$i = -1;
while ($i <= $num-2) {
     $i++; 
showthegoods($items[$i], $city);// Display all products for the first submenu item...
}
function showthegoods($mycategory, $city) {
?> <div class="TabbedPanelsContent"><?php
$result = mysql_query("SELECT * FROM products WHERE subcategory = '$mycategory' AND city='".$city."'");
if (mysql_num_rows($result) > 0) {
 $i = 0;
    echo "
    <table width='50%' cellpadding='0px' cellspacing='0px'><tr>";
while ($row = mysql_fetch_array($result)) {
?>
<?php




$prod=$row['product'];
$id =$row["id"];
$price =$row["price"];
$text = $prod;
$newtext = wordwrap($text, 14);
$final1 ="".$newtext."
$".$price."";


echo "<td>";
?>

      
    

<form action="" method="POST" name="myform<?php echo $id; ?>" class="myform<?php echo $id; ?>"> 
        <input type="hidden" name="hiddenField" class="hiddenField" value="<?php echo $prod; ?>" />
        <input type="hidden" name="hiddenField2" class="hiddenField2" value="<?php echo $id; ?>" />
        <input type="hidden" name="hiddenField1" class="hiddenField1" value="<?php echo $price; ?>" />
      <input type="submit" name="submit" class="submit" value="<?php echo $final1; ?>" style="left: 0px; background-color:lightgreen; height:70px; width:100px;" onclick="update_data();">   </form>
<?php

echo "</td>";
if ($i && $i%6 == 5) echo '</tr><tr>';
      $i++;
    }

    echo "</table> ";
    ?>    
  <?php 
}?></div><?php
}

?>
  </div>

  </div>
<script type="text/javascript">
var TabbedPanels1 = new Spry.Widget.TabbedPanels("TabbedPanels1");
  </script>

 

here is the structure of the spry tabbed pannels:

<div id="TabbedPanels2" class="TabbedPanels">
  <ul class="TabbedPanelsTabGroup">
    <li class="TabbedPanelsTab" tabindex="0">Tab 1</li>
    <li class="TabbedPanelsTab" tabindex="0">Tab 2</li>
  </ul>
  <div class="TabbedPanelsContentGroup">
    <div class="TabbedPanelsContent">Content 1</div>
    <div class="TabbedPanelsContent">Content 2</div>
  </div>
</div>

 

My table structure contains "product", "Category", "Subcategory", "price", "id" 

Can anyone help me make this happen properly?  I would like to use spry tabs for all of this btw, so spry tabs for categories, then the spry tabs for subcategories inside the categories, then my buttons for the products.  hopefully this makes sense and hopefully someone can help

[xyph]

I can't put it much better than this guy did

 

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

 

I've seen more 'ideal' solutions, but they are complex in comparison. The above link should let you know where you're going wrong.

 

If you want to keep the same structure, try messing around with orders.

 

ORDER BY category, subcategory

[phpstarter123]

I will take a detailed look at it, but in the meantime, is there any suggestions you have that could help me out?

[phpstarter123]

ok, so that page was a little over my head......anything anybody could recomend to get me started....

[Barand]

The method in the referenced site needs a known depth of hierarchy whereas this approach works for any depth. Using the data from that site  ie

 

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |      0 |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

 

the following recursive code traverses the hierarchy producing the output shown after.

 

<?php

listProducts();

function listProducts($cat=0)
{
    $sql = "SELECT category_id, name
    FROM category
    WHERE parent = $cat";
    $res = mysql_query($sql);
    if (mysql_num_rows($res)==0) return;
    echo "<ul>\n";
    while (list($cat,$name) = mysql_fetch_row($res)) {
        
        echo "<li> $name</li>";
        
        listProducts($cat);
        
    }
    echo "</ul>\n";
}
?>

 

Results-->

<ul>
    <li> ELECTRONICS</li>
    <ul>
        <li> TELEVISIONS</li>
        <ul>
            <li> TUBE</li>
            <li> LCD</li>
            <li> PLASMA</li>
        </ul>
        <li> PORTABLE ELECTRONICS</li>
        <ul>
            <li> MP3 PLAYERS</li>
            <ul>
                <li> FLASH</li>
            </ul>
            <li> CD PLAYERS</li>
            <li> 2 WAY RADIOS</li>
        </ul>
    </ul>
</ul>

 

This should give you something to build on.

[phpstarter123]

is there a way not using a parent?  I already have a table developed wiht over 400 entries and I really don't want to go back to add a parent to each o f those....

[Mahngiel]

is there a way not using a parent?  I already have a table developed wiht over 400 entries and I really don't want to go back to add a parent to each o f those....

Do you have any other method to link parents and children?  If not, then how would you expect to match them together?

[phpstarter123]

well there is the category/subcategory.  if the subcategory is present, a category must be present then.....it is working in my first post code, I would like a way to kindof use my first code to do the same thing using the category.  I know how to do two, but I am not sure how I would do three that are based off each other....any help with that?

[Barand]

the "parent" is just the category that the sub-category belongs to.

[phpstarter123]

ok if that is the case, I could get the subcategories into the categories, or the products into the subcategories, but how do I do all three?  the subs into the categories, and the products into the subs so that all three are present?????? I know how to do 2, but not three.

[Mahngiel]

well there is the category/subcategory.  if the subcategory is present, a category must be present then.....it is working in my first post code, I would like a way to kindof use my first code to do the same thing using the category.  I know how to do two, but I am not sure how I would do three that are based off each other....any help with that?

Instead of fishing around, why don't you tell us what your table(s) look like?

[phpstarter123]

 

My table structure contains "product", "Category", "Subcategory", "price", "id" 

 

 

there, that is what my table looks like, obviously with values filled in! 

[Mahngiel]

My table structure contains "product", "Category", "Subcategory", "price", "id" 

 

Assuming everything is spelt and capitalized the same way, you could build a 2d array which will overwrite the 1st value/2nd key and build one array

<?php
$query= select `Category`, `Subcategory` FROM ...;

while( $data = $mysql_fetch_assoc($query) ){
    $products[$data['Category']] =  $data['Subcategory'];
}

[phpstarter123]

ok, I get that, but then how do I incorporate it with my first code so that they work together?  I would like these being tabbed panels as well

[Psycho]

If you only have 1 category and 1 optional sub-category for each product then it is a simple as adding a sort. The problem is that you ARE using a subcategory when there isn't one. Don't use the string "None" to indicate that the record has no category. Either set the field to an empty string or set to NULL. Then you simply need to sort by Category, Subcategory.

SELECT *
FROM products
ORDER BY category, subcategory

 

But, if you are using a textual description to indicate when there is no subcategory you make it harder since you would have to create a derived column to use for sorting or add some other logic. This should work for you, but the better solution is to correct your data.

SELECT *
FROM products
ORDER BY category, subcategory<>'none', subcategory

 

So, records are all first sorted by category. Next they are sorted based upon the condition of the subcategory not being equal to the string 'none'. The records with none will be false(0) and the records that are not 'none' will be true(1) - so the none records come first. Lastly it sorts based upon the subcategory.

 

EDIT: I also assume that you are putting the actual category and subcategory names in those fields - which is also wrong. You should have separate tables to define those and only include foreign key references in your product table.

[phpstarter123]

OK!!!!! I get that.  if this will be easier to acomplish using all the categories having a subcategory, like main events, but nothing else but the products, I can do this(I know this is not what you are saying psycho) but I would like to know exactly where to place this in my code and how the spry tabs would be organized with it....this is the problem I am  running into!  I am not good at using loops and such like that to get tabbed panels inside of another tabbed pannel.  this may be easier if I use everything having a subcategory of just Main "category".  any help with that?

[Psycho]

This forum is for people to get help with code they have written and the expectation is that they are knowledgeable enough to implement it. I have no clue about "spry tabs" and am not interested in reading the documentation to find out.

 

Perhaps the freelance forum would be better suited for your needs if there is no one with that product knowledge willing to help you in the PHP Help forum.

 

Good luck.

[phpstarter123]

ok, I am going to try what barand suggested, but how would I set up my current table that I use for this?

[xyph]

Your first step should be understanding Barand's solution. Until you understand it, you'll never be able to convert it even with guidance.

[phpstarter123]

ok, so I did set up my table and I am able to return the results properly like how his showed it.  Now I would just like to know how I can incorporate it into a format like so:

<div id="TabbedPanels1" class="TabbedPanels">

  <ul class="TabbedPanelsTabGroup">

    <li class="TabbedPanelsTab" tabindex="0">Tab 1</li>

  </ul>

  <div class="TabbedPanelsContentGroup">

    <div class="TabbedPanelsContent">

      <div id="TabbedPanels2" class="TabbedPanels">

        <ul class="TabbedPanelsTabGroup">

          <li class="TabbedPanelsTab" tabindex="0">Tab 2</li>

        </ul>

        <div class="TabbedPanelsContentGroup">

          <div class="TabbedPanelsContent">Content 1</div>

        </div>

      </div>

    </div>

  </div>

</div>

 

and I think I will have it!  thanks in advance!!!!!!!

[xyph]

Have you attempted it? Or are we to code it for you?

[phpstarter123]

I have tried it and everytime I get stuck at the point as to how to make the tabs inside the other tabs work

 

<div id="TabbedPanels1" class="TabbedPanels">

<?php

listProducts();

function listProducts($cat=0)
{
    $sql = "SELECT id, product, category, subcategory
    FROM products
    WHERE parent = $cat AND category !='hidden'" ;
    $res = mysql_query($sql);
    if (mysql_num_rows($res)==0) return;
    echo '<ul class="TabbedPanelsTabGroup">';
    while (list($cat,$name,$cat1, $sub) = mysql_fetch_row($res)) {
       
        echo '<li class="TabbedPanelsTab" tabindex="0">"'.$name.'"</li><br />
';echo '<div class="TabbedPanelsContent">';
        
        listProducts($cat);
        echo '</div>';
    }
    echo "</ul>\n";
}
?>
  </div>

 

this returns a tree result with everything  being tabs, but not functional tabs...I am also confused as to where the products come from.....

[phpstarter123]

I am pretty sure it is because the <divs> are repeating too soon.....

[Drummin]

Any chance of uploading a sql file of your products table?

[phpstarter123]

I guess:

18641_.doc