组值和显示来自不同表的数据

Question

I have the following problem. I am trying to display total duration and workouts for a phase. I also want on the same time query a related table that stores the exercises for each workouts and displays how many exercises are in this phase:

The text output would be:

Phase 1
Duration: 268
Workouts: 6
Exercises: 17

Phase 2
Duration: 245
Workouts: 6
Exercises: 22

etc

table rk_workouts
id | phase | workout | duration
 1 |     1 |       1 |       45
 2 |     1 |       2 |       49
 3 |     1 |       3 |       48
 4 |     1 |       4 |       41
 5 |     1 |       5 |       42
 6 |     1 |       6 |       43
 7 |     2 |       1 |       41
 8 |     2 |       2 |       40
 9 |     2 |       3 |       41
10 |     2 |       4 |       40
11 |     2 |       5 |       43
12 |     2 |       6 |       40
13 |     3 |       1 |       45
14 |     3 |       2 |       40
15 |     3 |       3 |       40
16 |     3 |       4 |       43
17 |     3 |       5 |       47


table rk_exercises

id | workout_id | workout_name
 1 |          1 | exercise 1
 2 |          1 | exercise 2
 3 |          1 | exercise 3
 4 |          1 | exercise 4
 5 |          1 | exercise 5
 6 |          1 | exercise 6
 7 |          1 | exercise 7
 8 |          2 | exercise 1
 9 |          2 | exercise 2
10 |          2 | exercise 3
11 |          2 | exercise 4
12 |          2 | exercise 5
13 |          2 | exercise 6
14 |          3 | exercise 1
15 |          3 | exercise 2
16 |          3 | exercise 3
17 |          3 | exercise 4
etc

I am querying the following:

$this->db->qry = "SELECT
                    a.phase,
                    SUM(a.duration) as duration,
                    COUNT(a.workout) as workouts,
                    COUNT(b.id) as exercises
                FROM
                    rk_workouts a
                JOIN
                    rk_exercises b
                ON
                    a.workout=b.workout_id
                WHERE
                    a.category_id = '".$catid."'
                GROUP BY
                    a.phase
                ORDER BY
                    a.phase ASC";

I then insert the result into an array:

$rlt = $this->db->_result();
if(count($rlt) > 0){
    $new_arr = array();
    foreach($rlt as $dta){
        $new_arr["Phases"][] = array(
            "phase"=>$dta['phase'],
            "duration" => $dta['duration'],
            "workouts" => $dta['workouts'],
            "exercises" => $dta['exercises']
        );                        
    }

The result of my array output is not what I expected. I am trying to get the following output:

{
    "Phases": [{
        "phase": "1",
        "duration": "268",
        "workouts": "6",
        "exercises": "17"
    }]
}

But I get the following:

{
    "Phases": [{
        "phase": "1",
        "duration": "1788",
        "workouts": "40",
        "exercises": "40"
    }]
}

I am most certain my query is very wrong and wonder if someone could help me out or point me into the right direction. If need to, I can provide full table data.

Thank you for any help and time.

Answer 1

The new query I am using is before running the main query is:

$this->db->qry = "SELECT id FROM rk_workouts";
$mainSQL = $this->db->_execute();
$rlt = $this->db->_result();
  while ($row = mysqli_fetch_assoc($mainSQL)) {
    $this->db->qry = "SELECT COUNT(id) as exercises, workout_id FROM rk_exercises WHERE workout_id = '".$row['id']."' ";
    $subSQL = $this->db->_execute();
      while ($row = mysqli_fetch_assoc($subSQL)) {
        $this->db->qry = "UPDATE rk_workouts SET exercises = '".$row['exercises']."' WHERE id = ".$row['workout_id'];
      $this->db->_execute();
   }
}

I added an extra column to the rk_workouts Table to store the result of the COUNT(id).

The nain query on my rk_workouts table looks like this:

$this->db->qry = "SELECT phase, SUM(duration) as duration, COUNT(workout) as workouts, SUM(exercises) as exercises FROM rk_workouts WHERE category_id = '".$catid."' GROUP by phase ORDER BY phase ASC";

The result of my JSON output is now:

{
  "Phases": [
    {
      "phase": "1",
      "duration": "268",
      "workouts": "6",
      "exercises": "40"
    },
    {
      "phase": "2",
      "duration": "245",
      "workouts": "6",
      "exercises": "39"
    },
    {
      "phase": "3",
      "duration": "215",
      "workouts": "5",
      "exercises": "32"
    },
    {
      "phase": "4",
      "duration": "225",
      "workouts": "5",
      "exercises": "25"
    },
    {
      "phase": "5",
      "duration": "138",
      "workouts": "3",
      "exercises": "24"
    },
    {
      "phase": "6",
      "duration": "616",
      "workouts": "11",
      "exercises": "80"
    }
  ]
}

This is exactly what I needed for my iOS project. Thank you for everyone involved.