MySQL join+Codeigniter

[eluser]desertjinn[/eluser]
i am new to using codeigniter and i ran into a snag while trying to use the mysql join function. i am not sure on what i might be doing wrong. any help is appreciated.

i am trying to create an 'online appointment booking'. MySQL code is as follows:

Code:

function get_time_details($time_interval,$dateofappointment,$doctor_id)
    {
        
        $this->db->select('timeslot.time_slot');
        $this->db->from('timeslot');
        $this->db->join('appointments','timeslot.time_slot !=  appointments.time_slot');
        $this->db->where('appointments.date',$dateofappointment);
        $this->db->where('appointments.doctor_id',$doctor_id);
        $this->db->where('timeslot.time',$time_interval);
        $result = $this->db->get();
        return $result->result();
        
    }

I am trying to pick out and display timeslots that haven't been booked by someone else already . I have already stored the time intervals($time_interval) and the time slots available in those time intervals in a seperate table and am storing the appointments in another table.

My problem is that as long as there is only one appointment in the table the list of available slots are returned accurately. But as soon as more than one appointment is present the code returns all the time slots and also duplicate values of each time slot.

i am using the following tables :

timeslot

time_interval time_slot
8am-12noon 08:00am-08:10am
8am-12noon 08:10am-08:20am
8am-12noon 08:20am-08:30am


(similarly for 12noon-4pm and 4pm-8pm)
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------

appointments

id patient_id patient_name doctor_id
1 1004 Roy MJ 12

2 1011 Mathew R 6

------------------------------------------------------------------------

doctor_name date time_slot status
Aravind J 2011-08-16 08:00am-08:10am 1

Mark Z 2011-08-18 09:00am-09:10am 1

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------



Can anyone help me out?
Thanks in advance.

[eluser]jblack199[/eluser]

Code:

function get_time_details(time_interval, $dateofappointment,$doctor_id) {
   $query = $this->db
     ->select('time_slot')
     ->from('timeslot')
     ->join('appointments', 'appointments.time_slot != timeslot.time_slot');
     ->where('appointments.date', $dateofappointment);
     ->where('appointments.doctor_id', $doctor_id);
     ->where('timeslot.time', $time_interval);
     ->get();

return $query->result();
}

try something like that... based off of:

Code:

function getAllPosts(){
        $data = array();
        //all records from the database are retrieved as an array.
        $this->db->select('*');
        $this->db->from('posts');
        $this->db->join('comments', 'comments.post_id = posts.id');
        $Q = $this->db->get();
        if ($Q->num_rows() > 0) {
        foreach ($Q-> result_array() as $row) {
            $data[] = $row;
            }
        }
        $Q-> free_result();
        return $data;
    }

[eluser]jblack199[/eluser]
you also might try:

Code:

$this->db
    ->select('timeslot.time_slot, appointments.time_slot')
    -> from ('appointments', 'timeslot')
    -> join ('appointments', 'appointments.time_slot != timeslot.time_slot')
    ->where('appointments.date', $dateofappointment);
    ->where('appointments.doctor_id', $doctor_id);
    ->where('timeslot.time', $time_interval);
    ->get();

actually choosing all the info you need to get from both tables and what not is always a safe way to go..

[eluser]desertjinn[/eluser]

Code:

$result = $this->db
        ->select('timeslot.time_slot')
        ->from('timeslot')
        ->join('appointments','timeslot.time_slot = appointments.time_slot')
        ->where('timeslot.time',$time_interval)
        ->where('appointments.doctor_id',$doctor_id)
        ->where('appointments.date',$dateofappointment)
        ->get();
        
        if($result->num_rows() > 0)
        {
            foreach($result->result_array() as $row)
            {
                $result_2[] = $row;
            }
        }
        
        $result_final = $this->db->query("SELECT time_slot FROM timeslot WHERE time='$time_interval' AND time_slot !='??????'");
        
        return $result_final->result();

i tried to do it this way in the model after you gave me that idea ... i tried it in reverse by selecting the values present in the appointments table and trying to exclude them from the final selection. is there any way i could do it this way? can i write a second query to exclude those values that i do not need?

[eluser]desertjinn[/eluser]
still going around in circles .... someone pls help !!

[eluser]desertjinn[/eluser]
i was able to fix it finally...thanks for pointing me in the right direction jblack199. I just selected the values already present in the appointments table and then excluded them in my final selection query. Thanks a lot