• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Compound Query

#1
[eluser]bhbutter123[/eluser]
I have what I believe to be a tough situation and am looking for advice. I have a mysql table with a column named 'name' and another called 'category'. There will be multiple files with the same category. I want the display to do something similair to the following:

Category
Name
Name
Name
Another Category
Name
Name

Name in the first and second categories is not the same, it is just a placeholder.
I believe this to be possible using one model function and returning it as a multilevel array but am not sure how to do it.

#2
[eluser]jedd[/eluser]
[quote author="bhbutter123" date="1260030143"]
I have a mysql table with a column named 'name' and another called 'category'.
[/quote]

Another column or another mysql table?

Quote:There will be multiple files with the same category.

What is a file? Do you mean multiple names with the same category?

If you have one table only at this point, you need to fix your schema.

#3
[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'];
    }
}

#4
[eluser]bhbutter123[/eluser]
I forgot to mention that there is also a path field that has a link to the file, would I need another table for that or can I put it in one the files table? I am not familiar with the way the three table system works or with join. If you could give me some insight that would be appreciated.

#5
[eluser]JoostV[/eluser]
Google for 'database normalization'. If you're new to databases it may seem a bit daunting at first, but it's really all about common sense.

Since every file has a path and only one path, this path can be in the files table.
CREATE TABLE files (
id INT NOT NULL AUTO_INCREMENT ,
name VARCHAR( 150 ) NOT NULL ,
path VARCHAR( 150 ) NOT NULL ,
PRIMARY KEY ( id ),
KEY name (name )
) ENGINE = MYISAM
If you wanted to store the file extension and size, you would also store these in the files table.

Every file can have multiple catgories. And every category can have multiple files. That is why categories and files are in different tables. You need the link table to enable multiple files to link to multiple categories.

#6
[eluser]jedd[/eluser]
[quote author="bhbutter123" date="1260064180"]I forgot to mention that there is also a path field that has a link to the file, would I need another table for that or can I put it in one the files table? I am not familiar with the way the three table system works or with join. If you could give me some insight that would be appreciated.[/quote]

As mentioned, you need to learn about normalisation.

This comes down to mapping entities (things) and relationships (relationships) between them.

Anything that has a 1:1 relationship - such as the path of a file - should, generally, live next to the file.

Anything that has a 1:n relationship - such as a file's categories - needs to be expanded out to separate tables with a joining table to connect them.

Having said that, file paths (and indeed names) should probably be determined by the application at the time the files arrive - you can then algorithmically determine the file's name. UUID's are ideal - consistent size, virtually guaranteed to be unique, safe across pretty much every file system out there, etc.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.