what's the best datatype?
how to count values within the fields
example :
row1: 1,2,3,4,5
row2: 3,1,5,2
row3: 6,3,4,1
how to get the result for how many times a specific value was selected in a gives set of rows?:
value 1 = 3 (times)
value 2 = 2 (times)
Value ...
Value 6 = 1 (times)
Use string type to store, set VARCHAR for MySQL.
Use php explode (www.php.net/manual/en/function.explode.php) to convert the string to array. Then you can use php array-count-values to count (www.php.net/manual/en/function.array-count-values.php).
thank you Danielc
but can't it be done using MySql statement.. i need to have it as a query/table so i can do further operations with it ( group by year /user etc..) the record that contains this multi-select field has many other fields such as year, categories, user...
thank you again
CREATE TABLE activities (
aid int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
description text NOT NULL,
presenter varchar(255) NOT NULL,
date date NOT NULL,
period_f int(11) NOT NULL,
period_c int(11) NOT NULL,
cat_sel varchar(255) NOT NULL,
PRIMARY KEY (aid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
<--- cat_sel is the column/row field that contain multiselect values (row id from table categories)---->
CREATE TABLE categories (
catid int(11) NOT NULL AUTO_INCREMENT,
pgcat varchar(255) NOT NULL,
Program varchar(255) NOT NULL,
desc varchar(255) NOT NULL,
PRIMARY KEY (catid)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
now when adding a record into activities table (which represents an actual event), i select more than one category into cat_sel field (because that activity covers/ is related to more than one category).
what i need to do is count records into activities based on cat_sel values. ( one activity if the multi-select field contains values [1,3,6,2,4] this record would be counted 4 times as total but once each per category.
then next row [1,7,2]
my query should produce the result:
category 1 = 2 ( events)
....2 = 2
3 = 1
4 = 1
5 = 0
6 = 1
7 = 1