[eluser]JoostV[/eluser]
You need three tables. One for files, one for categories and a linktable that stores the links between categories and files
CREATE TABLE files (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 150 ) NOT NULL ,
PRIMARY KEY ( id ),
KEY name (name )
) ENGINE = MYISAM
CREATE TABLE categories (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 150 ) NOT NULL ,
PRIMARY KEY ( id ),
KEY name (name )
) ENGINE = MYISAM
CREATE TABLE IF NOT EXISTS files_categories (
id int(11) NOT NULL auto_increment,
file_id int(11) NOT NULL,
category_id int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM
When you want to display all the filenames per category you query like this:
Code:
// Select all categories and their linked files
$this->db->select('categories.name as category, files.name as name');
$this->db->from('files_categories');
$this->db->join('categories', 'files_categories.file_id = files.id');
$this->db->join('files', 'files_categories.category_id = categories.id');
$this->db->order_by('categories.name, files.name');
$query = $this->db->get();
// Store results in multi dimensional array
$result = array();
if ($query->num_rows() > 0) {
foreach ($query->result_array() as $row) {
$result[$row['category']][] = $row['name'];
}
}