1

I am doing my first project using Codeigniter 4. I am trying to make a summary page which consist of some sum from different columns and different tables.

Here is the tables :

table project

id project_name
1 project 1
2 project 2

table leading | id | id_project | permit | job | | -- | ---------- |--------| ---- | 1 | 1 | 2 | 2 | | 2 | 1 | 2 | 2 | | 3 | 2 | 1 | 1 |

table man_hours | id | id_project | branch | base | sum_manhours | | -- | ---------- |--------| ---- | ------------ | | 1 | 1 | 1 | 2 | 3 | | 2 | 1 | 1 | 2 | 3 | | 3 | 2 | 1 | 1 | 2 |

I've tried this :

ProjectModel :

    public function getSummary()
    {

        $this->join('leading', 'leading.id_project=project.id', 'LEFT');
        $this->join('man_hours', 'man_hours.id_project=project.id', 'LEFT');
        $this->select('sum(permit) as permit, sum(job) as job');
        $this->select('sum(sum_manhours) as sum_manhours');
        $this->select('project.id, project.project_name');
        $this->groupBy('project.id');
        $result = $this->findAll();

        return $result;
    }

Controller Project :

public function manhours(){
        $data = [
            'title' => 'Summary',
            'project' => $this->projectModel->getSummary()
        ];

        return view('summary/view-summary-manhours', $data);
}

The results :

no project name permit leading sum_manhours
1 1 8 8 12
2 2 1 1 2

It seems like the query working twice that's why is giving the wrong results, how do I fix this? I am really looking forward for the solution, thank you so much for reading my question, even more for giving me solution.

Shannaz Wakid
  • 21
  • 1
  • Please share the responses of: `show create table project;` , `show create table leading;` , and `show create table man_hours;` ln addition, share your expected results (behavior). – steven7mwesigwa Jul 06 '23 at 19:35
  • I created the tables directly on mysql database using mamp phpmyadmin. Here is the Model of each table : ProjectModel : ` protected $table = 'project'; protected $useTimestamps = true; protected $returnType = 'array'; protected $allowedFields = ['project_name', 'location'];` LeadingModel : `protected $table = 'leading'; protected $useTimestamps = true; protected $returnType = 'array'; protected $allowedFields = [ 'id_user', 'id_project', 'date', 'permit', 'job' ];` – Shannaz Wakid Jul 07 '23 at 03:51
  • ManhoursModel : `protected $table = 'man_hours'; protected $useTimestamps = true; protected $returnType = 'array'; protected $allowedFields = [ 'id_user', 'id_project', 'date', 'branch', 'base', 'sum_manhours' ];` – Shannaz Wakid Jul 07 '23 at 03:52
  • expected result for column project 1 the sum of permit and job should be 4 and sum of sum_manhours should be 6, but it gives twice of the sum amount. – Shannaz Wakid Jul 07 '23 at 03:55
  • When in development mode it shows the generated and executed SQL-query in the debug toolbar under `Database`. Could you post that here? – Simon Weber Jul 11 '23 at 10:00
  • Run the SQL queries from my comment in your PHPMyAdmin application and share the output/response. – steven7mwesigwa Jul 11 '23 at 12:52
  • Hi @steven7mwesigwa how do i share the screenshot here? I am sorry this is my first time asking in stackoverflow. – Shannaz Wakid Jul 12 '23 at 00:53

0 Answers0