Only getting one subcat_id and apply to all subcategories

[glen-rogers]

Hi

I need some help!

I have some code that displays a menu. The menu has categories of products, some of these categories have subcategories.

When a category is clicked, the class is sent to some jquery/ajax and when a subcategory is clicked the class is also sent to the jquery/ajax. This is working fine.

 

The problem I have is that if I click any subcategory in a category only the data for one subcat is shown for all the subcat.

 

This is the code

 

<?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 anyone see what the problem is?

 

Thanks for looking.

Glen

[jazzman1]

Change the statement:

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 
WHERE c.cat_id = $_GET['cat_id']
GROUP BY c.cat_id ORDER BY c.cat_id

 

 

 

[glen-rogers]

Thanks.

 

The page gives me this error now:

Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in C:\wamp\www\acraftyaffair\inc\productmenu.php on line 5

 

on this line

WHERE c.cat_id = $_GET['cat_id']

[jazzman1]

My mistake, the $_GET valiable must be $_GET['subcat_id'] coming from $data['subcat_id'], right?

 

Try this,

 

$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";
if(isset($_GET['subcat_id'])) $q .= " WHERE s.cat_id=".$_GET['subcat_id'];
$q .=" GROUP BY c.cat_id ORDER BY c.cat_id";

[glen-rogers]

Its still doing the same! showing the same data for every subcategory.

[jazzman1]

Well, let us know how you designed the tables in the database.

 

And......tell us what value you've got from $data['subcat_id'] too!

Edited March 28, 2013 by jazzman1

[glen-rogers]

Ok,

 

My tables are set up like this

 

 

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)

 

When I click on a category it displays all the products with that categories cat_id, as it should.

When I click on to a any subcategory belonging to a given category it displays only one subcategories products, for every subcategory in that group.

So for example, if i have a category 'animals' with subcategories 'dogs' 'cats' 'rabbits' and click on any one of them it will display whats is in 'cats' every time!

[jazzman1]

Hm.... you need to normalize your database tables.

I recommend you to watch those

Also, you would check here to find ready database schemata, otherwise you will have a lot of problems in the future.

[jazzman1]

If you run this query inside phpMyAdmin for example what result do you get:

Assuming that a category with id: 1 has many subcategories.

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 
WHERE s.cat_id = 1
GROUP BY c.cat_id ORDER BY c.cat_id

[glen-rogers]

Wow, it all seems a bit above me!

 

Do I not need all my fields though?

 

In categories cat_id is the id of the category.

In subcategories subcat_id is the id of the subcategory and cat_id is the same as cat_id in categories, so we know that subcategory belong to that category.

In product subcat_id is the same as subcat_id in subcategories so we can tll the subcategory that product belongs to (if any). And cat_id is the same as cat_id in categories so we can tell what category the product belongs to.

 

Have I got this all wrong?

[glen-rogers]

 

If you run this query inside phpMyAdmin for example what result do you get:

Assuming that a category with id: 1 has many subcategories.

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 
WHERE s.cat_id = 1
GROUP BY c.cat_id ORDER BY c.cat_id

Where am I meant to be getting a result?

 

This has come up

 

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

WHERE s.cat_id =1

GROUP BY c.cat_id

ORDER BY c.cat_id

LIMIT 0 , 30

[glen-rogers]

Sorry

 

 

When I ran this

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
WHERE c.cat_id =8
GROUP BY c.cat_id
ORDER BY c.cat_id
LIMIT 0 , 30

 

8 is the cat_id of Ribbon which has subcat 1 inch, 7/8 inch, 5/8 inch, 3/8 inch

 

I got this

 

category   cat_id   subcat_id   sublist

Ribbon      8          1                1 inch,7/8 inch,5/8 inch,3/8 inch

Edited March 28, 2013 by glen-rogers

[glen-rogers]

It seems I'm only gett one subcat_id, even though there are four subcats!

[Barand]

You need to group_concat the sub_ids with their descriptions

 

 

SELECT c.category, c.cat_id,  GROUP_CONCAT(s.subcat_id,'|', s.subcategory ) AS sublist ...

Edited March 28, 2013 by Barand

[glen-rogers]

Hey thanks for that.

 

But now when i run that query in phpmyadmi it give me this

 

category   cat_id   subcat_id   sublist

Ribbon     8           1                1|1 inch,2|7/8 inch,3|5/8 inch,4|3/8 inch

Edited March 28, 2013 by glen-rogers

[Barand]

You don't select the subcat_id with the cat_id, you are now selecting it with each subcategory

 

explode the sublist to get subcat_id|subcategory pairs.

 

explode those pairs to get the id and subcat for your links

[glen-rogers]

This is all getting a bit confusing for me, as you can tell I'm a novice!

 

I explode the sublist on this line

$subcats = explode(",", $data['sublist']);

 

How do I explode the pairs?

[Barand]

 

foreach ($subcats as $pair) {
    list($subcat_id, $subcategory) = explode('|', $pair);
    // output link
}

[glen-rogers]

That works! Thank you, I appreciate it.

 

Just one more thing though. This-

 

SELECT c.category, c.cat_id,  GROUP_CONCAT(s.subcat_id,'|', s.subcategory ) AS sublist ...

 

Put subcat_id in sublist with subcategory. So now my subcategory button go have subcat_id along with the subcategory text, like '1|dogs' '2|cats'.....

Is there any way not to show the subcat_id in the button?

[Barand]

The obvious way would be to explode them before creating the button

Edited March 28, 2013 by Barand

[glen-rogers]

Ah. What I was doing was this

$output .= "<li><a href=$subcat_id class='subcategory'>$pair</a></li>";

 

When it should have been this

 

$output .= "<li><a href=$subcat_id class='subcategory'>$subcategory</a></li>";

 

All sorted now!

 

Thank you

 

Glen