Thread

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:

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.

Can anyone help me out?
Thanks in advance.

I believe your problem is on “‘timeslot.time_slot !=  appointments.time_slot’”
It repeats on the intersection of the tables, so you get multiple results…
my first suggestion would be to use something in your where as

“timeslot.time_slot not in (select timeslot from appointments)”

(yeah, i know that maybe is not the most efficient but it works fine)

the other option is:

left join appointments on appointments.time_slot = timeslot.time_slot
where appointments.time_slot is null

(uses only one select)

I dont know wich of the one ends being best and one does 2 selects but the other does a join…