Jump to content

Only getting one subcat_id and apply to all subcategories


glen-rogers

Recommended Posts

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

Link to comment
Share on other sites

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

 

 

 

Link to comment
Share on other sites

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']
Link to comment
Share on other sites

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";
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.