CODEWITHSUNDEEP
php
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

For each loop to display values from mysql table

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

Answers (1)

Fallenreaper
Fallenreaper

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

Related Questions