Categories and subcategories

[GlenRogers]

Hi,

I have a problem I just cant solve.

First I will explain what I am trying to do.

I have a page with a menu consisting of categories of products, some of these categories have subcategories and some don't.

Categories and subcategories are kept in mysql tables. The tables are set out as so:

categories
id: int(10) primary ai null(no)
category: varchar(100) null(no)

subcategory:
id: int(10) primary ai null(no)
category: varchar(100) null(no)
cat_id: int(10)

I have code to add categories to the category table. And I have code to add subcategories to the subccategory table that takes the id value of category and puts the same value in the cat_id field of subcategory.

I now want to add products, I need it so that when i click a category that has no subcategory all the products for that category are displayed. And for categories with subcategories the products for each subcategory are displayed when the subcategory is clicked.

I dont think I have my tables set up right, I think i need both tables to have a common field?

Can anyone offeer advice on how to do this?

Thanks for looking.................

[pixelsoul]

Sounds like you just need to setup a column in your products table to hold the id of the category they belong to.

[GlenRogers]

Yeah but how do I do that if some products will have the id from the categories table and some will have the id from the subcategories table.
both tables might have rows with the same id!

I have id in both rows set to autoincrement, is there any way I can maybe have id in categories be even numbers and ai by 2 and id in subcategories be odd number and ai by 2. Then I could be sure id in both tables would always be different?

Then when I come to display the products have an if clause.

I dont know if this is the way to go about it or not.

If product_id is and even number{
   its linked to a category}
ELSE IF product_id is an odd number{
   it is linked to a subcategory}

Any ideas?

[urtrivedi]

I used 2 tables for solving this problem in past. Table pair gives unlimited categories or not categories at all. This is same as directory structure in your computer.
1) product_master (prod_id, prod_name, is_group, other_columns)
2) product_level (child_prod_id, parent_prod_id, level_no)

product_master
prod_id, prod_name, is_group, other_columns
0, Items, Y, XYZ (This is root group)
1, Pen, Y, xyz1 (This is sub group)
2, Black pen, N, PQR (This is first item of first group)
3, Red Pen, N, QBC (This is second item of first group)
4, HB Pencil, N, NCR (This is direct item under primary "Items" group)

Product_level (This is to keep track of categories and items properly)
child_prod_id, parent_prod_id, level_no
0,0,0 (root under self at level 0)
1,1,0 (first group under itself at level 0)
1,0,1 (first gropu under items at level 1
2,2,0 (first item under itself at level 0)
2,1,1 (first item under first gropu at level 1)
2,0,2 (first item under "items" at level 2)
3,3,0 (second item under itself at level 0)
3,1,1 (second item under first gropu at level 1)
3,0,2 (second item under "items" at level 2)
4,4,0 (third item under "items" at level 2)
4,0,1 (third item under "items" at level 1)

[pixelsoul]

I'm not entirely sure I understand what you are attempting after looking at your example code.

If you are trying attribute products to categories and subcategories, then the products table should have two columns, one for the category id that they belong to and another for the subcategory id that they belong to. This way if you click on just a category, all of the products with that category id are displayed. If you click on a subcategory, all of the products that have tha subcategory id are displayed.

id: int(10) primary ai null(no)
product: varchar(100) null(no)
cat_id: int(10)
sub_cat_id: int(10)

[GlenRogers]

Thanks guys!

I ended up doing it as pixiesoul suggested.But now I am usure how to get the products displayed on the page!

I need it as you said, if you click on a category it displaay the product, or if you click on a subcategory it displays the subcategory product.

If a product is added to a category with no subcategories the subcat_id field gets a NULL value.

Should I be doing something along these line:
IF sucat_id =NULL THEN use cat_id to display products?

Thanks

[pixelsoul]

Do you already have some code started for looping through the query results? If you can post what you are working with so far, I/we could see what you need or point you in the right direction.

The query would be pretty basic

SELECT * FROM products WHERE cat_id = '$id' ORDER BY some_column DESC LIMIT 0, 30

Or (WHERE sub_cat_id =) for the subcategory query. Added LIMIT as I assume you would be doing some type of pagination for a large amount of products.

[GlenRogers]

This is code I used for another site, I just doctored it a bit.
I usees ajax to display data returned from a php file in a div on my webpage.

php file to build the html to display:
showproducts.php

<?php
   include 'connect.php';
   // if no category was sent, display some error message
   if(isset($_POST['subcategory'])) {
      // cast the category to integer (just a little bit of basic security)
      $subcat = (int) $_POST['subcategory'];
      // this will be the string that you will return into the product-data div
      $returnHtml = '';
      $q = "SELECT * FROM products WHERE subcat_id='$subcat' ORDER BY id DESC";
      if($r = mysql_query($q)) {
         // construct the html to return
         while($row = mysql_fetch_array($r)) {
           $returnHtml .= "<div class='image1'><a><img ";
           $returnHtml .= "class='cat_image1' ";
           $returnHtml .= "name='cat_image' ";
           $returnHtml .= "src='{$row['product_image']}'";
           $returnHtml .= "alt='{$row['product_name']}' ";
           $returnHtml .= "title='{$row['product_name']}' />";
           $returnHtml .= "<div class='imgLabel1'>{$row['product_name']} <br />&pound {$row['product_price']}</div></a></div>";
         }
       }
   }
   else if(isset($_POST['category'])){
      // cast the category to integer (just a little bit of basic security)
      $cat = (int) $_POST['category'];
      // this will be the string that you will return into the product-data div
      $returnHtml = '';
      $q = "SELECT * FROM products WHERE subcat_id='$cat' ORDER BY id DESC";
      if($r = mysql_query($q)) {
         // construct the html to return
         while($row = mysql_fetch_array($r)) {
           $returnHtml .= "<div class='image1'><a><img ";
           $returnHtml .= "class='cat_image1' ";
           $returnHtml .= "name='cat_image' ";
           $returnHtml .= "src='{$row['product_image']}'";
           $returnHtml .= "alt='{$row['product_name']}' ";
           $returnHtml .= "title='{$row['product_name']}' />";
           $returnHtml .= "<div class='imgLabel1'>{$row['product_name']} <br />&pound {$row['product_price']}</div></a></div>";
         }
       }
   }
   else{
      die('No category has been chosen');
   }
   // display the html (you actually return it this way)
   echo $returnHtml;
?>

And the ajax script

// whenever a link with clategory class is clicked
$('a.category, a.subcategory').click(function(e) {
// first stop the link to go anywhere
e.preventDefault();
//get the text of the link by converting the clicked object to string
var linkText = new String(this);
// the value after the last / is the category ID
var categoryValue = linkText.substring(linkText.lastIndexOf('/') + 1);
// send the category ID to the getProductData.php script using jquery ajax post method
// send along a category ID
// on success insert the returned text into the chosen div
$.post('../inc/showproducts.php', {category: categoryValue}, function(data) {
//find total number of records
var totalRecords = $(data).length;
//define how many records shown per page
var pageSize = 8;
//work out number of pages needed to hold records
var numOfPages = Math.ceil(totalRecords / pageSize);
//make page links
var i,
  pageLinks = '<div class="pageLinks">';
for (i = 0; i < numOfPages; i++) {
  pageLinks += '<a href="#" onclick="showProductPage(' + i + ');return false;">' + (i + 1) + '<\/a> ';
}
pageLinks += '<\/div>';
//display returned data and page links in chosen div ('info)
$('#catcontent').html(pageLinks + data);
showProductPage(0);
});
});
//function to slice up records into pages
function showProductPage( pageNo ) { 
    var perPage = 8; 
    var start = pageNo * perPage; 
    var end = start + perPage; 
    $('.image1').hide().filter(function(index) { 
        return ( (index > (start-1)) && ( index < end ) ); 
    } ).show(); 
}

And the menu with the categories and subcategories

<?php
   include 'connect.php';
   $q = "SELECT c.category, GROUP_CONCAT(s.subcategory) AS sublist FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id GROUP BY c.cat_id ORDER BY c.cat_id ";
   $r = mysql_query($q) or die( 'Could not execute query: ' . mysql_error() );;
   $output = '<ul id="nav">';
   while($data = mysql_fetch_array($r)){
      $output .= "<li><a href='home.php' class='category'>{$data['category']}</a>";
      if(!empty($data['sublist'])){
        $subcats = explode(",", $data['sublist']);
        $output .="<ul>";
        foreach($subcats as $s){
           $output .= "<li><a href='news.php' class='subcategory'>$s</a></li>";
        }
        $output .= "</ul>";
      }
      $output .="</li>";
   }
   $output .= '</ul>';
   echo $output;
?>

The problem now is that when you click on any category or subcategory in the menu, it display all product in the products table!

My tables are set up as such;

categories:
cat_id - int(10) ai
category - varchar(100)

subcategories:
subcat_id - int(10) ai
subcategory - varchar(100)
cat_id - int(10)

products:
id - int(10) ai
product_name - varchar(100)
product_description - text
product_price - decimal(10, 2)
product_image - varchar(100)
cat_id - int(10) null(yes)
subcat_id - int(10) null(yes)

Can you offer any advice to sort this problem out???

Thanks.....

[GlenRogers]

Can anyone help me out on this?

Do all products have a subcat? If so the cat_id in the products table is surplus to requirement.

[GlenRogers]

No some categories dont have a subcategory. So surely I need cat_id in the products table to display products for these categories?

I need it to go like this in my web page:
If a category with no subcats is clicked then display all products with that category cat_id.
If a subcategory is clicked then display all products with that subcat_id.

I echoed out $cat after this line:

$cat = (int) $_POST['category'];

and it is alway 0 for some reason

Have you any ideas?

Thanks

In this case, I would look at utrivedi's solution. It's similar to a navigation menu with multiple levels. Makes use of the 'parent_id'. It's not an easy one to get your head around, and in some circumstances makes info retrieval difficult, but it may help.

[pixelsoul]

I see a couple of different problems (not sure they have anything to do though with the results). The first is that both of your queries are set to grab products for subcat_id. The other is the AJAX request seems to be posting 'category' regardless of the link your clicked. In the PHP it is checking the POST for 'category' or 'subcategory', so I imagine that would be a problem.

Have you tried posting directly to the PHP page with out using AJAX to see if you get the same type of results?

Also, maybe move this to the first thing in your 'while' loop before setting the other?

$returnHtml = '';

[GlenRogers]

So Im trying to do this with just php now, ive took the ajax out.

I need help!
This is my cat-subcat menu file now
I have commented whats wrong.

<?php
   include 'connect.php';
   $q = "SELECT c.category, c.cat_id, s.subcat_id, GROUP_CONCAT(s.subcategory) AS sublist FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id GROUP BY c.cat_id ORDER BY c.cat_id ";
   $r = mysql_query($q) or die( 'Could not execute query: ' . mysql_error() );;
   $output = '<ul id="nav">';
   while($data = mysql_fetch_array($r)){
      $output .= "<li><a href={$data['cat_id']}>{$data['category']}</a>";//this is just trying to direct me to a page. ie.www.mysite/cat_id
      if(!empty($data['sublist'])){
        $subcats = explode(",", $data['sublist']);
        $output .="<ul>";
        foreach($subcats as $s){
           $output .= "<li><a href={$data['subcat_id']}>$s</a></li>";this is trying to direct me to www.mysite/subcat_id. but even then every its using the subcat_id of the first record in the table for every subcategory!
        }
        $output .= "</ul>";
      }
      $output .="</li>";
   }
   $output .= '</ul>';
   echo $output;
?>

Thanks for looking!

[GlenRogers]

Wit everything put back the way it was (ajax included again) and forgetting about the subcategories for now

This works, the data for product_name and product_price are displayed

if(isset($_POST['category'])){
      // cast the category to integer (just a little bit of basic security)
      $cat = (int) $_POST['category'];
      $q = "SELECT * FROM products WHERE cat_id=$cat";
      // this will be the string that you will return into the product-data div
      $returnHtml = '';

      if($r = mysql_query($q)) {
         // construct the html to return
         while($row = mysql_fetch_array($r)) {
           $returnHtml .= $row['product_name'];
           $returnHtml .= $row['product_price'];

         }
       }
   }
   else{
      die('No category has been chosen');
   }
   // display the html (you actually return it this way)
   echo $returnHtml;

But when I try to include the image and append it to $returnHtml like this

while($row = mysql_fetch_array($r)) {
           $returnHtml .= $row['product_name'];
           $returnHtml .= $row['product_price'];
           $returnHtml .= $row['product_image'];

         }

Nothing happens when you click on a category.

Can anyone shed any light on this?

Thanks..

[GlenRogers]

So now I have the category workin, you click a category the it displays the products for that catgory.
I cant get the subcategory working, when I click a subcategory it sends the cat_id and not the subcat_id.

Can anyone help?

Thanks

[GlenRogers]

Its something to do with this

foreach($subcats as $s){
           $output .= "<li><a href='news.php' class='subcategory'>$s</a></li>";
        }

It sayin that subcategory is undefined, but it works ok with category.

[pixelsoul]

I mentioned this previously. The ajax call has "category" hardcoded into it. So it will always pass "category" as the parameter. I mentioned using jQuery to grab the class name and use it as the parameter in the AJAX request so then it would switch over to use "subcategory" when a link was clicked with that as the class name.

[GlenRogers]

Thanks for your reply dude, but I havent a clue how to do that!

[pixelsoul]

Do you have any experience at all with jQuery?

Here is a jsfiddle where I show how to grab different attributes from a link tag including the class using jQuery. http://jsfiddle.net/pixelsoul/EVav5/

Once you have a the class name in the variable you can do pretty much anything you want with it. Just use the variable name as the parameter name in the AJAX request.

[GlenRogers]

No experince at all (bar from using a few plugins)!

I added this line in

var linkClass = $(this).attr("class");

So my script now looks like this

$('a.category, a.subcategory').click(function(e) {
// first stop the link to go anywhere
e.preventDefault();
var linkClass = $(this).attr("class");
//get the text of the link by converting the clicked object to string
var linkText = new String(this);
// the value after the last / is the category ID
var categoryValue = linkText.substring(linkText.lastIndexOf('/') + 1);
// send the category ID to the getProductData.php script using jquery ajax post method
// send along a category ID
// on success insert the returned text into the chosen div
$.post('../inc/showproducts.php', {linkClass: categoryValue}, function(data) {
//find total number of records
var totalRecords = $(data).length;
//define how many records shown per page
var pageSize = 6;
//work out number of pages needed to hold records
var numOfPages = Math.ceil(totalRecords / pageSize);
//make page links
var i,
  pageLinks = '<div class="pageLinks">';
for (i = 0; i < numOfPages; i++) {
  pageLinks += '<a href="#" onclick="showProductPage(' + i + ');return false;">' + (i + 1) + '<\/a> ';
}
pageLinks += '<\/div>';
//display returned data and page links in chosen div ('info)
$('#catcontent').html(pageLinks + data);
showProductPage(0);
});
});
//function to slice up records into pages
function showProductPage( pageNo ) { 
    var perPage = 6; 
    var start = pageNo * perPage; 
    var end = start + perPage; 
    $('.image').hide().filter(function(index) { 
        return ( (index > (start-1)) && ( index < end ) ); 
    } ).show(); 
}

But now if I click a link (category or subcategory) nothing happens!

[pixelsoul]

Put this in after the linkClass var just to make sure the value is correct.

alert(linkClass);

[GlenRogers]

I put that line in and its telling me category and subcategory when i click the corresponding links.
But my pasge is telling me no category has been chosen!

Any ideas whats up?........

[pixelsoul]

Hmm, sounds like the parameter name is not getting passed. In your PHP have it return $_POST to see what is getting sent to your code. Something like this:

else{
        print_r($_POST);
        die('No category has been chosen'); 
   }

Tell me what that says.

[GlenRogers]

Says nothing. The die message doesnt come up now, nothing does.............

[pixelsoul]

I just realized the problem... I forgot that {} in the ajax call can't take variables, it is literal. Give me a minute to think about it.

[GlenRogers]

Thanks very much :)................

[pixelsoul]

Okay, try this instead. I just defined the literal outside of post() and put into 'params' and passing that instead.

$('a.category, a.subcategory').click(function(e) {
    // first stop the link to go anywhere
    e.preventDefault();

    // get the class of the link
    var linkClass = $(this).attr("class");

    //get the text of the link by converting the clicked object to string
    var linkText = new String(this);

    // the value after the last / is the category ID
    var categoryValue = linkText.substring(linkText.lastIndexOf('/') + 1);

    // put the post parameters into 'params' to pass through the AJAX post request
    var params = {};
    params[linkClass] = categoryValue;  

    // send the category ID to the getProductData.php script using jquery ajax post method
    // send along a category ID
    // on success insert the returned text into the chosen div
    $.post('../inc/showproducts.php', params, function(data) {
        //find total number of records
        var totalRecords = $(data).length;
        //define how many records shown per page
        var pageSize = 6;
        //work out number of pages needed to hold records
        var numOfPages = Math.ceil(totalRecords / pageSize);
        //make page links
        var i,
          pageLinks = '<div class="pageLinks">';
        for (i = 0; i < numOfPages; i++) {
          pageLinks += '<a href="#" onclick="showProductPage(' + i + ');return false;">' + (i + 1) + '<\/a> ';
        }

        pageLinks += '<\/div>';
        //display returned data and page links in chosen div ('info)
        $('#catcontent').html(pageLinks + data);
        showProductPage(0);
    });
});

[GlenRogers]

That is awesome! It works!

Could you maybe help me with one more thing on this?

When I click on a categories subcategories, it shows me the products that are in one subcategory for every subcategory in that category.
So if I have a category 'animals' with the subs 'cats' 'dogs' 'rabbits' ant 'cats' has a picture of a cat in it, it shows me that cat picture even if I click 'dogs' or 'rabbits'

I think that may come from the menu file?

<?php
   include 'connect.php';
   $q = "SELECT c.category, c.cat_id, s.subcat_id, GROUP_CONCAT(s.subcategory) AS sublist FROM categories AS c LEFT JOIN subcategories AS s ON c.cat_id = s.cat_id GROUP BY c.cat_id ORDER BY c.cat_id ";
   $r = mysql_query($q) or die( 'Could not execute query: ' . mysql_error() );

   $output = '<ul id="nav">';
   while($data = mysql_fetch_array($r)){
      $output .= "<li><a href={$data['cat_id']} class='category'>{$data['category']}</a>";
      if(!empty($data['sublist'])){
        $subcats = explode(",", $data['sublist']);
        $output .="<ul>";
        foreach($subcats as $s){
           $output .= "<li><a href={$data['subcat_id']} class='subcategory'>$s</a></li>";
        }
        $output .= "</ul>";
      }
      $output .="</li>";
   }
   $output .= '</ul>';
   echo $output;
?>

Can you offer any advice on this?

[GlenRogers]

Afterlooking at this and playing around with echos it seems that

foreach($subcats as $s){
           $output .= "<li><a href={$data['subcat_id']} class='subcategory'>$s</a></li>";
        }

is only getting the one subcat_id per category and putting it in every subcategory in the same category!

I cant figure out why!