Codeigniter App Tutorial

Codeigniter App Part 9 – Relational Table Joins

all right so we're almost there what I want to do now is I want to be able to show a list of the tasks that are in that particular list all right so we

have to do a few things we have to make a join so we can join the list and the tasks tables together so if we go to our list controller so let's let's dot php' under controllers and we go down to the

show method that's this view right here we're showing the list with the ID of two and you can see that I did comment out some things here earlier and now we're going to tackle these and make it

so we can display all the tasks all right so we have two values in this data array here that we want to uncomment ones completed tasks one is uncompleted tab so basically we'll have two sections

in the list that will show completed tasks and uncompleted tasks so the first thing we need to do is create this model these model methods the first one is get list tasks which takes an ID which is

coming from the URL so that's the idea of the list all right so let's see let's copy that go to the a list model and see all right so we got function get list

tasks and this here the second parameter is going to be active all right so this is going to tell us if if the task is active I'm meaning complete or not so the default is going to be true

all right so if the task is uncompleted then it's going to be active if it's complete then it's not active all right so we have to do things a little different here because we're going to be

doing a table join and I can actually show you that syntax if we go to the documentation the active record let's search for join so this is how we would do it we're gonna select something in

this case it select everything from the blog's table alright now we have a blog stable and the comments table and the in the comments table each comment has a blog ID field okay so what we want to do

to bring these two together is do this DB join and bring in the comments table and we want to match them comments dot ID so the ID field in the comments should match the blog's ID all right and

then we can just do what we usually do and grab that so you can see this active record code is the same as this standard SQL alright so we're gonna do that and up until now we've been just using the

this DB get we haven't actually said select because we have a need to need it too alright so what we're gonna do here and say this dB select now since since these two

tables both have Nied field things could get a little confusing if if we just say select all alright so if we just say select all then it's we're gonna have issues later on when we're trying to

separate the task ID from the list ID because they're both named ID so we have to select the actual fields that we want by name all right so let's say we want to select the task name from the task

table so we can just say tasks dot task name you should be in quotes all right let's take a look at the table the task table you can see we have task name so we do want to select that

because we're going to be displaying it actually you know what these don't need coats quotes comma and we want the task body as well so we're saying the table tasks we want to grab

the task body filled column all right we also want the ID but like I said we don't want to mix it with the list ID so what we can do is say tasks dot ID as task underscore ID so

now when we're in the view we can use this and it'll know that we're talking about the ID and the task table and not the list table all right so now we also want to get lists in the list table we

want to get the list name and we want to get the list body as well all right so that's how you can select individual rows or columns instead of just saying select everything

all right so what we need to do doubt now is have a from when you say this DB from and the main table that we're trying to get here is tasks alright so I'm going to say select all select these

tables from tasks so now that we're selecting these fields or these columns from tasks now we want to join in the list table all right so here we're going to say this DB join and the first

parameter that we're going to put here is the table which is list all right so the next thing we want to put is where we want to relate these two tables and let's just go back to PHP myadmin for a

second I mean you probably already understand this but just in case so we have it's a list table all right and we have some fields for each list and we have a task

table with some fields and in this table we have a column called list ID all right so this list ID is an integer all right so you can see we have ones and twos here and if we go back to lists

that field that lists ID field I just showed you relates to the ID field here alright so we have to claim that in this model and that's what we're going to do right here basically we're going to say

lists list dot ID equals tasks dot list ID all right so we're saying that the ID field in the list table is equal to the list ID field and the task table so hopefully that

makes sense all right so now we need a where clause I'm gonna say this DB where you know let's say where tasks dot list ID is equal to this variable list ID all right actually we have to change

this up here let's change this to list ID because that's where this is coming from okay now we're going to also use another where Clause but it's going to be conditional if you remember we have

this active here which basically says if activists true that means that the task is not complete if this is false then the task is complete alright because of its if it's complete

then it's not active so we're going to say we're going to put in a conditional here and say let's say if active is equal to true alright and this is going to be an

if-else so we'll say if active is equal to truth then let's just grab this here's way our clause and we're gonna say this DB we're tasks dots is complete is equal to zero

all right because if it's true if it's active then it's not complete so that is complete will be a zero and that is coming from tasks that's coming from this field right here okay which all of

them are not complete right at the moment we'll add a link that will allow us to mark them complete or incomplete alright so the same thing then if if it's not active so if active equals

false then we're going to say that it is complete so we want that one okay so down here we want to say this a DV yet and before when we've used this this this D be get we would pass in the table

name but we're not going to do that because we use the Select statement up here so we're saying select these from tasks and then we just leave this blank but we do get a set

a variable here okay so now what we want to do is check and see if there's any rows so to check and see if there's any tasks basically so we'll say if say if query query num rows is less than one

all right so if queries if the number of rows is less than one so basically zero then we want to return false else we are going to actually know what we don't need an else we're just gonna go right

under here and return query result all right so in foal this this method get' let's test is just gonna get all the tasks in a particular list alright

and let's see where did we set that get this task alright so they'll go we put in this this value here completed tasks so next we have this uncompleted tasks is equal

to the same function get list asked except we're going to pass in false instead of true so that'll give us both completed and uncompleted and we can then use them inside of our

view which will be the show view so let's do that save and we want to go to lists and show so under all this I'm actually just going to paste in a loop so we're saying

if completed tasks which is coming from here completed task which is coming from this function in the model get less tasks all right and then we're putting out an li tag which has a link to the

actual task by ID and it's using the task name as the anchor alright and we're saying if else so if there are no completed tasks then we're just going to spit out this paragraph so let's make

sure that that's working all right so it's saying that the column ID and our where clause is ambiguous which means that it can't tell the task ID from the list ID and it's not in the

method we just created because our where Clause says where tasks dot list ID is list ID so it's got to be it's got to be in this here this in the list model to get list method so let's go to this list

model get list yeah we're just gonna we're gonna mom we're trying to return the row even if it's not there so we're gonna edit this a little bit this get list method so we're gonna say this DB

select and we're going to select all alright so now we need this DB from and this is going to be from the list table and now for the where clause will say this DB

where let's see where I D is equal to ID query equals this DB get and we don't need anything inside they get because we're using select from alright so now

what we want to do is check the number a row we want to see if a list actually came back so we'll say if so if query the num rows is not equal to 1 then we want to return

false because because we should only be getting one record back because what we're getting it by the ID and I'm going to say else then we want to return query row

okay so let's save that reload alright so now we're in our family stuff list which shows all the tasks if we go to our work stuff list we see our work stuff tasks if we click on it

alright so we're having a little issue because everything we click on goes to pick up kids for some reason that's strange let's see

all right I know why this is going this is happening we need to go to our task model and get tasks what we need to do is we need to have a join here as well we need to join the lists and the task

table so you're pretty much going to get rid of this and we want to do something like we did right here where we select the fields and then we join in the list table all right so let's see this

TV select and we're gonna select the fields manually so we wants let's see tasks dots past name and TAS start ID

createdate body is complete all right so that's all the stuff in the task table now we want sum over the list

field we want list ID but we're going to say as list ID so there's no confusion list dot let's name and finally lists dot list body okay so we want to select those from from tasks

and now we want to join in a list table so we'll say this DB joined and the table we want to join is less and here's what we want to we want to specify the field so list dot ID is equal to tasks

dot list ID and so that looks good now we want our where clause so we'll say this DV where I'm going to say where tasks dot ID is equal to ID

get now we want to check and see if any rows were returned so if query but if query num rows is not equal to 1 then we want to return false

else we want to return the query row all right so let's see what that does so if we go back to my list all right let's see we have a syntax error in the task model number 19 out we need a

semicolon here all right so I'll click on family stuff pick up kids parents dinner alright so now it's working dinner with wife and the task view shows the date the status

and the due date and then a description all right so we should be able to delete a task all right we didn't set that up yet all right so let's set up the delete we can

add a task I know that okay so that works we can edit the task okay so we can head it so all we need to do now is we need to be able to delete to delete the task because right now

it's it's not doing anything so we want to go to our task controller and I'm going to paste in the delete function all right so it's calling this task model delete task so let's go to the

task model and we need our delete task so public function delete and that's going to take in a task ID and say this DV where this DV where ID is equal to the task ID

and we're gonna say let's see this DV delete delete queries are very easy tasks and then we're just going to return alright so now we should be able to delete let me just make sure we get

our messages so we get this task deleted should be in the show the list show view and we have it right here so that should be all set all right lists will go to a task and

delete undefined delete tasks whoops you want tasks all right delete list ID task ID oh I see we're calling

task model delete task and in the model you can see we're calling just to leave not delete tasks so change that let's reload and try to delete it and it's been deleted alright alright one thing

that I do notice is if we go to our tasks all our tasks are not complete alright so that's set to 0 in our view in our list view where we're displaying our tasks we have if completed tasks and

that's wrong it should be if active because active tasks mean that they're not complete so we're going to change this inside of the list controller and right here where we have this

completed tasks let's change this to active tasks all right and then here is going to be the the inactive tasks all right I just want to change that because it doesn't really make sense all right

so then back in the in the view all we need to do was change this to active tasks change this to active tasks all right and above this I'm going to put in a heading

all right and then below this I'm just gonna copy copy this and we're going to say completed tasks and this is going to be inactive same thing right here and then down here I'm going to say there

are no completed tasks and here I'll say there are no active tasks okay so let's see its reload all right so let's actually go to the other list which has a bunch of tasks and let's set

one of these will say pickup Sun will change this to 1 which means it's complete and then if we reload that's under the completed tasks alright so one of the the main thing that we have left

to do is we need to be able to mark a task complete all right so if we go in we want to be able to click mark complete and have it switch which we don't have implemented yet so we'll do

that in the next video and then we should be all set

We will combine the lists and tables using active record “joins”

The full source code is at – https://github.com/bradtraversy/mytodo_ci