[SOLVED] Query Question

[therealwesfoster]

Ok, I'm wanting to only show categories that have subcategories. Here is the SQL:

 

SELECT cat_title, cat_id,  FROM categories WHERE (SELECT COUNT(*) FROM subcats WHERE subcat_catid LIKE '%;cat_id;%')>0

 

(The sub-cat's subcat_catid field is setup to have more than 1 category. So if a sub-category was under 3 categories (1, 2, and 3), then the subcat_catid field would be ";1;2;3;".)

 

How can I get this part to work? WHERE subcat_catid LIKE '%;cat_id;%'.

 

------

 

Side question, how can I count how many sub-categories there are in the same query that grabs the main category information? I think it'd look like this:

SELECT cat_title, cat_id, (SELECT COUNT(*) FROM subcats....) as subcat_count  FROM categories

 

 

Thanks!

 

Wes

[fenway]

Why are you storing arrays in the mysql field?

[therealwesfoster]

It's a string that will be converted to an array in php. This is because there is an un-numbered amount of categories a subcategory can be under.

 

Wes

[corbin]

Wouldn't it be easier to just have a cat_parent_id column?

[therealwesfoster]

Wouldn't it be easier to just have a cat_parent_id column?

 

That is what the "subcat_catid" column is. If it belongs to 3 different categories with ID's of 3, 5, and 7; then it's subcat_catid would be ";3;;5;;7;".

 

Wes

[corbin]

Hrmmm my question was more of rhetorical (except for I thought you had a 1 to 1 relationship) ;p.  You should look into database normalization.

 

 

Basically the concept would be a middle-man table.

 

 

CREATE TABLE categories (

    cat_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

    cat_name VARCHAR(255),

    cat_desc TEXT

);

 

CREATE TABLE cat_parents (

    cat_id INT NOT NULL,

    parent_id INT NOT NULL DEFAULT 0

);

 

Then, to show categories that have subcategories:

 

SELECT DISTINCT cp.parent_id as cat_id, c.cat_name, c.cat_desc FROM cat_parents cp JOIN categories c ON c.cat_id = cp.parent_id WHERE cp.parent_id != 0;

 

 

 

The query would of course have to be modified to work with your schema, but that would work with my sample schema.

 

 

Edit:  Forgot to put the cp alias after cat_parents.

[therealwesfoster]

Mucho appreciado. You not only answered my question, but opened my eyes to DB normalization.

 

Wes

[corbin]

Fue nada*. ;p

 

 

And my thoughts on DB normalization are that the earlier one is introduced to the concept of DB normalization the better.  ;p

 

 

 

 

*No idea if that actually makes sense.