Question 375781 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How can I display the number of doctors for each department in this code?

I have a table in the database that I named department, and another that I named doctor. I plan to display the number of doctors for each department. But when I try to display them, I always get the result 0 even if the department contains doctors.Here is the department table:

CREATE TABLE `department` (
  `id` int(10) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `description` varchar(1000) CHARACTER SET utf8 DEFAULT NULL,
  `x` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `y` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `hospital_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is the doctor table:

CREATE TABLE `doctor` (
  `id` int(10) NOT NULL,
  `img_url` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `phone` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `department` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `profile` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `x` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `y` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `ion_user_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `hospital_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I put a foreign key from the department table to the doctor table as follows:

ADD COLUMN department_id INT,
ADD CONSTRAINT fk_department_id
FOREIGN KEY (department_id)
REFERENCES department(id);

Here is the function that counts the number of doctors in the department (models):

public function getDoctorsCountByDepartment($department_id) {
        $this->db->select('department_id, COUNT(*) as doctor_count');
        $this->db->from('doctor');
        $this->db->where('department_id', $department_id); // Filtre par ID de département
        $this->db->group_by('department_id');
        $query = $this->db->get();

        return $query->row()->doctor_count; // Retourne le nombre de médecins pour ce département
    }

Here is the function that retrieves the number of departments (controller):

public function getDoctorsCountByDepartment() {
         $this->load->model('department_model'); 
    $departments = $this->department_model->getDepartments(); 

    foreach ($departments as $department) {
        $department->doctor_count = $this->department_model->getDoctorsCountByDepartment($department->id);
    }

    $data['departments'] = $departments;
    $this->load->view('department_view', $data);
    }

And this is the view to display data:

          <div class="panel-body">
                <div class="adv-table editable-table ">
                    <div class="space15"></div>
                    <table class="table table-striped table-hover table-bordered" id="editable-sample">
                        <thead class="bg-info">
                            <tr>
                                <th> <?php echo lang('name') ?></th>
                                <th> <?php echo lang('description') ?></th>
                                <th> <?php echo lang('NbreMedecins') ?></th>
                                <th class="no-print"> <?php echo lang('options') ?></th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($departments as $department) { ?>
                                <?php
                                // Initialisation de la propriété doctor_count si elle n'est pas définie
                                $department->doctor_count = $department->doctor_count ?? 0;
                                $doctors_count_by_department=array();
                                foreach ($doctors_count_by_department as $count) {
                                    if ($count->department == $department->id) {
                                        $doctor_count = $count->doctor_count;
                                        break;
                                    }
                                }
                                ?>
                                <tr class="">
                                    <td><?php echo $department->name; ?></td>
                                    <td><?php echo $department->description; ?></td>
                                    <td><?php echo $department->doctor_count; ?></td>
                                    <td class="no-print">
                                        <button type="button" class="btn btn-info btn-xs btn_width editbutton" data-toggle="modal" title="<?php echo lang('edit'); ?>" data-id="<?php echo $department->id; ?>"><i class="fa fa-edit"></i> </button>   
                                        <a class="btn btn-info btn-xs btn_width delete_button" title="<?php echo lang('delete'); ?>" href="department/delete?id=<?php echo $department->id; ?>" onclick="return confirm('Etes-vous sûr de vouloir supprimer cet élément?');"><i class="fa fa-trash-o"></i> </a>
                                    </td>
                                </tr>
                            <?php } ?>
                        </tbody>
                        
                    </table>
                </div>
            </div>
Tags:

2

Answers


  1. 0 
    select department, count(id) as `doctor_count`
from `doctor`
group by department

    Should get you the results you are looking for

    Login or Signup to reply.
  2. 0

    The group_by(‘department_id’) line is unnecessary and may lead to unexpected results. Here’s why:

    1. The where(‘department_id’, $department_id) line filters the result set to include only records where the department_id matches the provided $department_id. This means that each row in the result set will already correspond to the specified department ID.
    2. When you use group_by(‘department_id’), it instructs the database to group the result set by the department_id column. However, since you’ve already filtered the result set by a specific department ID, each row will represent a single department, and there’s no need for further grouping.
    3. Grouping by department_id in this context could potentially result in incorrect counts if there are multiple rows with the same department_id. It would collapse those rows into a single row, which is not what you want when counting doctors by department.

    So, your modification removes the unnecessary group_by(‘department_id’) line, which makes the function simpler and ensures accurate results:
    (models)

    public function getDoctorsCountByDepartment($department_id) {
    $this->db->select('COUNT(*) as doctor_count');
    $this->db->from('doctor');
    $this->db->where('department_id', $department_id); // Filter by department ID
    $query = $this->db->get();
    return $query->row()->doctor_count;
}

    This revised function correctly counts the number of doctors for the specified department ID without unnecessary grouping. It’s cleaner, more efficient, and less prone to errors.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search