Click here to Skip to main content
15,886,873 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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|
+---------------+------------------+------+-----+---------+----------------+
Posted
Updated 21-Jun-20 2:45am
Comments
[no name] 21-Jun-20 8:39am    
The problem I think comes from student_prg. At least MS SQL will not even gives you a result, it will give an error message something like 'invalid field' because it is not an aggregate.
Have a read e.g. here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
Member 14865416 21-Jun-20 8:44am    
Hi, thank you for your reply. May I know what is the correct query to execute the expected output? Because there was no error message when I run it in MySQL console.
[no name] 21-Jun-20 8:49am    
Allow me first a question: Does MySQL console really give a result without error message for the _exactly same_ SQL statement? Hard to belive...

Member 14865416 21-Jun-20 8:55am    
Yes, it does as mentioned above. I couldn't attach an image here, so I couldn't share the screencapture.
[no name] 21-Jun-20 9:07am    
Very strange.
Now what is the correct query? It depends....
In case student_prg can be different for each group by student_city the only I see is GROUP BY student_city, student_prg. But looks like this is not what you like to have as result.

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900