WHERE clause problem

[esm]

why doesn't the following IN clause work?

SELECT * FROM link WHERE category_id = '$cat' OR $cat IN (cat1) ORDER BY link_id ASC

where $cat= 4 and cat1 is 6, 11, 44 ( or whatever ). cat1 is a field in the link database ( i.e., cat1 is a column in the link table ) and is defined as a TEXT type.

it will return 6 but not 11 or 44.

"6", "11", "44" doesn't work at all.

[sofiah]

Gene,

It doesn't worked could be because cat1 is text and the whole value is kept in one record (separated by comma). I suggest a better solution, and it worked. But you must create a new table, named cat1 for example. It have two fields : category_id and cat1_id. The query will be :

SELECT link.link_id from link,cat1 where link.category_id=$cat or link.category_id=cat1.category_id GROUP BY (link.link_id)

Just a suggestion.

Sofiah

[esm]

OK. As I said, my SQL knowledge is small. I have no idea what your SELECT statement does. You know much more than me.

are you suggesting one value each for the category_id and cat1_id per row?

Here is what I did.

I actually had to change the category_id field in the cat1 table to link_id. This is what the table looks like:

row link_id cat1
1 5 6
2 5 11
3 5 44
etc, etc

What this means is that link_id #5 would not only appear in its primary category but also in categories 6, 11 and 44

the cat1 table would be better named the mcat table since it would contain the additional (multiple) categories.

Having done that, I had to change your SELECT statment to the following and it seemed to work...!

SELECT link.link_id from link,cat1 where link.category_id=$cat or ($cat=cat1.cat1 and link.link_id=cat1.link_id) GROUP BY (link.link_id)

I had previously got the multiple categories into the cat1 field of the link table and could retrieve them into the modify.html and search results pages.

But I could not get them into the category pages. Now I can get them in the categories but I have to move them from the links table and put them in a new table ( I want to change the name from cat1 to mcat).

I'm not sure how to do that.

Here is what I am thinking.
during a link add, just insert them into the mcat table.

during a modify link, delete from the mcat table all rows for the modified link and insert the new ones.

So whenever I need to access the mcat data, instead of using a field from the link table I would need to get the data from the mcat table. OUCH!

Well, it is time to sleep for me and I have to go see my daughter tomorrow so it will be sunday before I can do much more with this.

With a little luck, I will have a MOD for the lite version that will allow multiple categories. Make that a LOT OF LUCK.....

[dody]

I actually had to change the category_id field in the cat1 table to link_id. This is what the table looks like:

row link_id cat1
1 5 6
2 5 11
3 5 44
etc, etc

What this means is that link_id #5 would not only appear in its primary category but also in categories 6, 11 and 44

It seem you created new table to handle categories? If so, it's good.

But I could not get them into the category pages. Now I can get them in the categories but I have to move them from the links table and put them in a new table ( I want to change the name from cat1 to mcat).

What I do not understand is mcat field (formerly cat1). If you have new table to store categories, this field mcat is no need. Am I missing something here?

[esm]

actually, I just wanted to rename the cat1 table to the mcat table. In indexu3.1 there is a cat1 field and when I added a new field to link table in the free version, I called it cat1.

Then sofiah suggested instead of a field in the links table to hold the multiple catagories data that we should create a new table. She called it the cat1 table. I just want to call that table the mcat table.

the mcat table has just two fields - the link field and the additional category field.

[sofiah]

Gene,

It is called one to many (1-N) relation. It will be a little cumbersome, but I think that's the correct way according to database theory

Here is what I am thinking.
during a link add, just insert them into the mcat table.

during a modify link, delete from the mcat table all rows for the modified link and insert the new ones

That will be ok.

Sofiah