[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.