Array problem with 2 columns

closer2u2

After some hours of hard research I can't find an answer that works for me, would appreciate help.

Case:

Table1 contains column "category", a post can be assigned 3 categories and will be stored in Table1 > category column as 0|1|6|23|0 (3 categorie ids being 1,6 and 23)

Table2 contains category names, category ids.

I want to show in what categories a poster is most active in, limit to 5, AND i want to eliminate duplicates, e.g. post 1 has category 1,2,3 and post 2 has category 2,3,4

result should gove 1,2,3,4 (eliminating the double 2 and 3)

as per post, the category column is an array, i cannot seem to get the right MySql query setup

I got something like this which does not work correctly:

$sql="SELECT DISTINCT * FROM TABLE2 as a, category FROM TABLE1 as b WHERE a.CID=b.category LIMIT 5";

as the category in TABLE1 is stored like 0|1|6|23|0 i cannot proceed

NogDog

Are you committed to this non-normal scheme of storing 3 category IDs in one table column? I would suggest a separate post_to_category table which would only need two columns: post_id and category_id (with a primary/unique index on those 2 columns combined). Then it would be relatively straight-forward to create a query JOIN-ing the applicable tables for what you want to do.

closer2u2

Hey NogDog

I'm working on an open source script, and never saw that way of storing ids either.

Maybe it will be better to change it to 1 id per cat row, only thing is, I already have 1000+ entries in the DB :queasy:

NogDog

It might be something like:

$query = "
SELECT t2.category_name, COUNT(*) AS cat_total
  FROM table1 AS t1 
    INNER JOIN table2 AS t2 ON t2.CID REGEXP t1.category
  WHERE t1.user_id = '$userID'
  GROUP BY t2.category_name
  ORDER BY cat_total DESC
  LIMIT 5
";

I'm guessing there's some sort of user_id column in table1, so change that appropriately to match the actual column name. (If there isn't, then I need a better understanding and probably more details about the actual tables involved.)