For each loop to display values from mysql table

Question

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

You show column $value['name'] and `$value['type'] for academy name/type that do not exist in the query result set. How are you currently getting the display to actually work the way you have shown? Second of all, if you are only ever querying for one academy at a time, why output the academy information inside the loop at all? Simply move the display of the academy information outside the loop and leave the class-specific stuff inside it - problem solved. — Mike Brant
– Mike Brant, Commented Nov 21, 2013 at 19:16
@MarkBryant good point. How would it look like the academy information outside the loop? My mind can't grasp the concept — Code_Ed_Student
– Code_Ed_Student, Commented Nov 21, 2013 at 20:05

Fallenreaper · Accepted Answer · 2013-11-21 19:21:56Z

1

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. :)

edited Nov 21, 2013 at 19:21

answered Nov 21, 2013 at 19:16

Fallenreaper

10.8k15 gold badges77 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fallenreaper Over a year ago

of course it is! I wrote it. ;) heh. j/k. Im glad I could help.

Collectives™ on Stack Overflow

For each loop to display values from mysql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related