This page is to view on the most popular program in the most popular cities, so below are the code for that. The output of the most popular cities works fine but the most popular program in those cities displaying incorrect output. Can someone assist to check in my query if there is any mistake? Thank you very much for your time.
What I have tried:
<pre><table class="table table-striped table-dark">
<thead>
<tr>
<td>City</td>
<td>Popular Programme</td>
</tr>
</thead>
<tbody>
<?php
$query = " SELECT student_city, count(*) AS count, student_prg FROM marketing_data GROUP BY student_city ORDER by count desc LIMIT 3;";
if(count(fetchAll($query))>0){
foreach(fetchAll($query) as $row){
?>
<tr>
<td><?php echo $row['student_city'] ?></td>
<td><?php echo $row['student_prg'] ?></td>
</tr>
<?php
}
}else{
echo "<script>alert('No record')</script>";
}
?>
</tbody>
</table>
Table Structure
CREATE TABLE marketing_data
(
student_matric VARCHAR NOT NULL AUTO_INCREMENT,
student_prg TEXT NOT NULL,
semester VARCHAR(10) NOT NULL,
intake_session INT(1) NOT NULL,
intake_year INT(10) NOT NULL,
student_city TEXT NOT NULL,
city_lat VARCHAR(20) NOT NULL,
city_long VARCHAR(20) NOT NULL,
student_state TEXT NOT NULL,
state_code VARCHAR(100) NOT NULL
);
Output from MySQL console:
+---------------+------------------+------+-----+---------+-----------------
| student_city | count | student_prg |
+---------------+------------------+------+-----+---------+----------------+
| Kuala Dungun | 4 | Bachelor of Multimedia Industry |
| Jerantut | 3 | Bachelor of Multimedia Industry |
|Bukit Beruntung| 3 | Bachelor of Software Engineering|
+---------------+------------------+------+-----+---------+----------------+