[eluser]JamieBarton[/eluser]
Hey guys,
I have been trying to work out JOINS for a while now and can't get my head around them, below is the code I have so far and hoping someone can guide me in the right direction or provide me with modified code that will make this work for me.
I have a get_family_albums MODEL that I want to query the database for albums where family_id is equal to that passed through the get_family_albums($family_id). I also want to get the members info of who posted it, which is stored as member_id.
The tables are setup like:
Albums:
- id
- name
- description
- family_id (this is what family it is for)
- member_id (this is who posted it)
Members:
- member_id
- member_forename
- member_surname
My code so far:
Code: function get_family_albums($family_id)
{
$this->db->select('
albums.id as album_id,
albums.name as album_name,
albums.location as album_location,
albums.description as album_description,
albums.created_on as album_created_on,
families.family_id as family_id,
families.family_name as family_name,
members.member_id as member_id,
members.member_forename as member_forename,
members.member_surname as member_surname
');
$this->db->from('families');
$this->db->join('albums', 'albums.family_id = families.family_id', 'LEFT');
$this->db->join('members', 'members.member_id = albums.member_id', 'LEFT');
$query = $this->db->get();
return $query;
}
Will appreciate any response into helping me make this work.
[eluser]CI_avatar[/eluser]
first test the query string in phpmyadmin if it works. after confirming that your string is correct, you can now convert it into codeigniter query.
note:
it is wiser to user db->query with complicated string.
[eluser]CI_avatar[/eluser]
the query with join usually looks like this
Code: $this->db->query("SELECT
workorders.ID,
workorders.USERNAME,
workorders.FOR_ID_DEPT,
workorders.IS_ATTACHMENT,
workorders.FOR_ID_UNIT,
workorders.FOR_ID_BRANCH,
workorders.ID_USERTYPE,
workorders.ID_DEPARTMENT,
workorders.ID_UNIT,
workorders.DATE_CREATED,
workorders.DATE_UPDATESTAT,
workorders.TITLE,
workorders.DESCRIPTION,
workorders.PRIORITY,
workorders.ID_CATEGORY,
departments.DEPT_NAME,
units.UNIT_NAME,
branches.BRANCH_NAME,
workorders.`STATUS`,
task_category.TASK_NAME
FROM
workorders
INNER JOIN departments ON (workorders.FOR_ID_DEPT = departments.ID)
INNER JOIN units ON(workorders.FOR_ID_UNIT = units.ID)
INNER JOIN branches ON (workorders.FOR_ID_BRANCH = branches.ID)
INNER JOIN task_category ON (workorders.ID_CATEGORY = task_category.ID)");
[eluser]CI_avatar[/eluser]
please show your tables and their relations. so that it is easier to help you.
[eluser]JamieBarton[/eluser]
Thanks for that, I'll give it a try, would I put the WHERE family_id = '$family_id' at the very end after the 4 JOINS?
[eluser]JamieBarton[/eluser]
Hey CI_avatar,
Code: CREATE TABLE `albums` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
`location` varchar(100) NOT NULL,
`description` varchar(250) NOT NULL,
`family_id` bigint(20) NOT NULL,
`member_id` bigint(20) NOT NULL,
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `family_id` (`family_id`),
KEY `member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
CREATE TABLE `families` (
`family_id` bigint(20) NOT NULL auto_increment,
`family_name` varchar(60) NOT NULL,
`family_registered` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`family_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
CREATE TABLE `members` (
`member_id` bigint(20) NOT NULL auto_increment,
`member_family_id` bigint(20) NOT NULL,
`member_forename` varchar(120) NOT NULL,
`member_surname` varchar(120) NOT NULL,
`member_email` varchar(120) NOT NULL,
`member_password` varchar(40) NOT NULL,
`member_avatar_url` varchar(120) NOT NULL,
`member_language` varchar(30) NOT NULL,
`location` varchar(120) NOT NULL,
PRIMARY KEY (`member_id`),
KEY `member_family_id` (`member_family_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
I've used InnoDB and made a relationship from the albums member_id and family_id to the member/family tables id column.
[eluser]jedd[/eluser]
[quote author="Jamie B" date="1258474310"]
Code: CREATE TABLE `albums` (
`id` bigint(20) NOT NULL auto_increment,
`family_id` bigint(20) NOT NULL,
`location` varchar(100) NOT NULL,
...
CREATE TABLE `families` (
`family_id` bigint(20) NOT NULL auto_increment,
...
CREATE TABLE `members` (
`location` varchar(120) NOT NULL,
...
[/quote]
{shudder}
I'm pretty [url="/forums/viewthread/114249/"]strongly opposed[/url] to using plurals for table names, and for inserting the table name into the field name ... but this is just perverse.
At the very least, come up with a standard and then be consistent about it.
Here you've got some id fields having the table name as part of them and some not. You've got one table with the table name prefixed onto every column except, bewilderingly, one (members.location). You've got no algorithm for getting from table name to column prefix (because of the plural->singular switcheroo, and also because some columns didn't get the prefix in the first place). You've also left yourself with no easy way of identifying would-be FK's in your schema - so family_id's source is unclear in the two places you've defined that column. Location is defined differently in two places - this might make sense in your design, but in that case there's actually an imperative to have a prefix - more likely I expect it just wants to be normalised out into its own table.
Oh, and your three different values for AUTO_INCREMENT suggests that you might have misunderstood the point of an ID field in your tables - you might want to read up on some schema design tutorials on the intergoogle.
Glad to be of help. 
[eluser]JamieBarton[/eluser]
Hey thanks for your comments about the database, yeah, totally agree with you - I've restarted it many times, and took code from older projects and testings of making the gallery. So that's why it is a bit messy at the moment, just got carried away by starting to code before making the db normalised as it can be.
What do you mean quickly by the Auto Increment?
Thanks again,
Jamie
[eluser]jedd[/eluser]
[quote author="Jamie B" date="1258478645"]
What do you mean quickly by the Auto Increment?
[/quote]
What is your intent by having auto increments of 10, 2 and 3 for the different tables you've shown here?
[eluser]JamieBarton[/eluser]
Nothing, all I did was dump the database. And rows have been erased from the DB. Obviously that ID isn't used again, and it starts from the last increment for the next insert - If you know what I mean?
|
