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

group mysql results by id for looping

I have a table that has flight data in mysql. Im writing a php code that will group and display the data in groups using codeigniter 3

 journey_id     air_id  FlightDuration  out_or_in   flightduration2
    1           1           20hr 5min   outbound    1205
    2           1           20hr 5min   outbound    1300
    3           1           17hr 55min  inbound     2258
    4           1           17hr 55min  inbound     1075
    5           2           31hr 40min  outbound    1970
    6           2           31hr 40min  outbound    1900
    7           2           17hr 55min  inbound     2223
    8           2           17hr 55min  inbound     1987
    9           3           10hr 45min  outbound    645
    10          3           11hr 25min  inbound     685

im using a $this->db->get() to retrieve the data and i can easily loop through. but since each row is in array im finding it difficult to group them. i cannot use the mysql group coz i need each row.

for an example i want to display the items as below

air_id - 1
20hr 5min   outbound    1205
20hr 5min   outbound    1300
17hr 55min  inbound     2258
17hr 55min  inbound     1075

air_id - 2
31hr 40min  outbound    1970
31hr 40min  outbound    1900
17hr 55min  inbound     2223
17hr 55min  inbound     1987

air_id - 3
10hr 45min  outbound    645
11hr 25min  inbound     685

what would be the best way to group the result by the air_id so i can iterate through

Tags:

2

Answers


  1. 0

    1. Fetch the data from the database:

      $this->db->select('journey_id, air_id, FlightDuration, out_or_in, flightduration2');
 $this->db->from('your_table_name'); // Replace 'your_table_name' with the actual table name
 $query = $this->db->get();
 $data = $query->result_array();

    2. Create an empty array to hold the grouped data:

       $grouped_data = array();

    3. Iterate through the fetched data and group it by air_id:

      foreach ($data as $row) {

    $air_id = $row['air_id'];

    // Check if the air_id already exists in the grouped_data array
    if (!isset($grouped_data[$air_id])) {
        // If not, initialize an empty array for this air_id
        $grouped_data[$air_id] = array();
    }

    // Add the current row to the group for this air_id
    $grouped_data[$air_id][] = $row;
}

    4. Now, you have the data grouped by air_id in the $grouped_data array. You can iterate through this array to display the data as you specified:

      foreach ($grouped_data as $air_id => $group) {
    echo "air_id - $air_id<br>";

    foreach ($group as $row) {
        echo $row['FlightDuration'] . ' ' . $row['out_or_in'] . ' ' . $row['flightduration2'] . '<br>';
    }

    echo "<br>";
}

    This code will loop through the grouped data and display it as you described, with each group of flight data under the corresponding air_id.

    Login or Signup to reply.
  2. 0

    I’d start by getting the list of unique air_id’s:

    $air_ids = array_unique(array_column($rows, 'air_id'));

    Then you can loop through them all using various different methods, but some foreach’s are usually fastest:

    foreach ($air_ids as $air_id)
{
    echo 'air_id - ' . $air_id;

    foreach ($rows as $row)
    {
        // skip non-matching rows
        if ($row['air_id'] !== $air_id)
        {
            continue;
        } 
        
        // display row
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search