John Davidson

Message:

I'd like to output two rows into one and change the output based on the values

SQL syntax:

SELECT * 

FROM course 

  LEFT OUTER JOIN located ON course.course_id = located.course_id 

  LEFT OUTER JOIN city ON city.city_id = located.city_id

PHP syntax:

  while ($row=$result->fetch_assoc()) { ?>

     <div class="col-lg-4 col-md-6" style="margin-bottom: 20px">

          <div class="card bg-black" style="text-align: center;">

               <div class="card-body">

                   <h3 class="card-text text-white"><?= $row['name']?></h3>

                   <p class="text-danger"><?= $row['city_name'];?></p>

               </div>

          </div>

     </div>

   <?php

   }

   ?>

The output:

How I'd like it to be:

But if it is only one city it would show the name of the city which happened to be "City 1" or "City 2".

---

My Answer:
To achieve the desired output where two rows are combined into one and the city name is displayed based on the values, you can modify the PHP code as follows:

php
$previous_course_id = null;
$city_names = array();

while ($row = $result->fetch_assoc()) {
if ($row['course_id'] != $previous_course_id) {
// Output the combined row for the previous course
if (!empty($city_names)) {
$city_display = implode(' and ', $city_names);
?>

}

// Reset city names for the new course
$city_names = array();
$previous_course_id = $row['course_id'];
$previous_course_name = $row['name'];
}

// Add city name to the array
$city_names[] = $row['city_name'];
}

// Output the combined row for the last course
if (!empty($city_names)) {
$city_display = implode(' and ', $city_names);
?>

}


This code will combine the rows based on the course ID and display the course name along with the city names in the desired format. If there is only one city for a course, it will display the city name as is.