Reputation: 1190
In my table courses
I have several entries that share the same academy_id
value. I am using a foreach loop to retrieve all the rows with the same academy_id
. In the example below, there is an academy that has two courses. The values are displaying correctly but there are some unnecessary values shown. For example it creates two results. Is it possible to have one combined result like shown below on the wanted result to display
section?
PHP
$academy_id = 123
$db_select = $db_con->prepare("
SELECT ca.course_name,
ca.course_start_date,
ca.course_end_date
FROM courses_by_academy ca
WHERE ca.academy_id = :academy_id
");
$final_result = '';
if (!$db_select) return false;
if (!$db_select->execute(array(':academy_id' => $academy_id))) return false;
$results = $db_select->fetchAll(\PDO::FETCH_ASSOC);
if (empty($results)) return false;
foreach ($results as $value){
$final_result .= "<b>Academy Name: </b>".$value['name']."</br><b>Academy Type: </b>".$value['type']."</br><b>Status: </b>".$value['status']."</br>";
$final_result .= "<b>Course Name: </b>".$value['course_name']."</br><b>Start Date: </b>".$value['course_start_date']."</br><b>End Date: </b>".$value['course_end_date']."</br>";
}
}
Table Values
+----+------------+----------------------+---------------+------------+
| id | academy_id | course_name | start_date | end_date |
+----+------------+----------------------+---------------+------------+
| 1 | 123 | Biology - Basic | 2013-11-30 | 2013-12-25 |
| 2 | 123 | Biology - Nutrition | 2014-01-15 | 2014-01-25 |
+----+------------+----------------------+---------------+------------+
Current Result Displaying
Academy Name: North Valley Schools
Academy Type: Post-Secondary
Status: INACTIVE
ID: 123
Course Name: Biology - Basic
Start date: 2013-11-30
End date: 2013-12-25
Academy Name: North Valley Schools
Academy Type: Post-Secondary
Status: INACTIVE
ID: 123
Course Name: Biology - Nutrition
Start date: 2014-01-15
End date: 2014-01
Wanted Result- Combined
Academy Name: North Valley Schools
Academy Type: Post-Secondary
Status: INACTIVE
ID: 123
Course Name: Biology - Basic
Start date: 2013-11-30
End date: 2013-12-25
Course Name: Biology - Nutrition
Start date: 2014-01-15
End date: 2014-01-25
Upvotes: 0
Views: 1065
Reputation: 10704
your loop:
foreach ($results as $value){
$final_result .= "<b>Academy Name: </b>".$value['name']."</br><b>Academy Type: </b>".$value['type']."</br><b>Status: </b>".$value['status']."</br>";
$final_result .= "<b>Course Name: </b>".$value['course_name']."</br><b>Start Date: </b>".$value['course_start_date']."</br><b>End Date: </b>".$value['course_end_date']."</br>";
}
instead do:
$first = true;
foreach ($results as $value){
if($first){
$first = false;
$final_result .= "<b>Academy Name: </b>".$value['name']."</br><b>Academy Type: </b>".$value['type']."</br><b>Status: </b>".$value['status']."</br>";
}
$final_result .= "<b>Course Name: </b>".$value['course_name']."</br><b>Start Date: </b>".$value['course_start_date']."</br><b>End Date: </b>".$value['course_end_date']."</br>";
}
it will execute the academy information only once. :)
Upvotes: 1