CODEWITHSUNDEEP
phpmysqlsql
Code_Ed_Student
Code_Ed_Student

Reputation: 1190

Foreach loop to display all records with same id

In my table courses I have several entries that share the same academy_id. I am using a foreach loop to retrieve the values in the result set. The for loop only displays one result and not all the others that share the same academy_id. How can I display all the rows that share the same academy_id?

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  | 2013-11-30    | 2013-12-25 |
| 3  |        345 | Chemistry            | 2013-11-30    | 2013-12-25 |
| 4  |        678 | Calculus             | 2013-11-30    | 2013-12-25 |
+----+------------+----------------------+---------------+------------+

PHP

$academy_id = '123';

$db_select  = $db_con->prepare("
SELECT c.course_name,
       c.course_start_date,
       c.course_end_date
FROM courses c
WHERE c.academy_id = 123
");
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>Course Name: </b>".$value['course_name']."</br><b>Start Date: </b>".$value['start_date']."</br><b>End Date: </b>".$value['end_date']."</br>";
    }
print_r($_POST);
}

Curent Result

+----+------------+-------------------+---------------+------------+
| id | academy_id |   course_name     |  start_date   |  end_date  |
+----+------------+-------------------+---------------+------------+
| 1  |        123 | Biology - Basic   | 2013-11-30    | 2013-12-25 |
+----+------------+-------------------+---------------+------------+

Wanted Result all academy_id = 123 to show

+----+------------+----------------------+---------------+------------+
| id | academy_id |     course_name      |  start_date   |  end_date  |
+----+------------+----------------------+---------------+------------+
| 1  |        123 | Biology - Basic      | 2013-11-30    | 2013-12-25 |
| 2  |        123 | Biology - Nutrition  | 2013-11-30    | 2013-12-25 |
+----+------------+----------------------+---------------+------------+

Upvotes: 2

Views: 4748

Answers (2)

Leonardo
Leonardo

Reputation: 736

Change 

$academy_id = '123';

....

foreach ($results as $value){
    $final_result = "<b>Course Name: </b>".$value['course_name']."</br><b>Start Date: </b>".$value['start_date']."</br><b>End Date: </b>".$value['end_date']."</br>";
}

for

$academy_id = 123;

.....

$final_result = '';
foreach ($results as $value){
    $final_result .= "<b>Course Name: </b>".$value['course_name']."</br><b>Start Date: </b>".$value['start_date']."</br><b>End Date: </b>".$value['end_date']."</br>";
}

echo $final_result;

Upvotes: 3

Noam Rathaus
Noam Rathaus

Reputation: 5598

(seeing other people's answer I know mine resolves a different question you had or might have had) I am not sure I fully understand your request, but here is something you can try

Split your code into two parts

1) Part one will do:

SELECT c.academy_id
FROM courses c
GROUP BY c.academy_id

This will be your primary results for the loop

2) Part two inside the previous loop will do your current SQL but instead of hardcoded 123, it will use the result of the previous query as the value

Upvotes: 0

Related Questions