1

im doing a system for school. and my problem was i cant display total student enrolled in each class. i can make a query in DB/ phpmyadmin for the data i need to be diplay. but for the hard code, its not working. can anyone help?

here's the body of my codes. 

<?php
         $key="";
    if(isset($_POST['searchtxt']))
        $key=$_POST['searchtxt'];

    if($key !="")
        $sql_sel=mysql_query("SElECT * FROM class_tbl WHERE class_name  like '%$key%' ");
    else
            $sql_sel=mysql_query("SELECT * FROM class_tbl");


            $i=0;
    while($row=mysql_fetch_array($sql_sel)){
    $i++;
    $color=($i%2==0)?"lightblue":"white";
        ?>
      <tr bgcolor="<?php echo $color?>">
            <td><?php echo $i;?></td>
            <td><?php echo $row['class_name'];?></td>
            <td><?php echo $row['class_id'];?></td>
            <td><?php echo $row['class_year'];?></td>
            <td><?php

                    $result = mysql_query("SELECT class_name, COUNT( class_name ) FROM stu_tbl c GROUP BY class_name");
                    $num_rows = mysql_num_rows($result);
                    echo "$num_rows";
                    // $total_enrolled=mysql_query("SELECT COUNT( * ) FROM stu_tbl WHERE class_name =  'class_name'");
                    //echo $total_enrolled;
                ?>

if im using this codes, the output display will be 6 for all classes. which i think its the total number of class. not the student enrolled. the query i used in phpmyadmin works fine and its displayed the output i wanted to. thanks in advances for the helps. really appreciate it.

in phpmyadmin, im using this query, and its works fine.

SELECT class_name, COUNT( class_name ) FROM stu_tbl c GROUP BY class_name

i want the output to be display as :

no class_name  class_id  year  total_student_enrolled  
1   1 Amanah    1        2014   6       
2   1 Bestari   2        2014   2       
3   2 Amanah    9        2014   5       
4   2 Bestari   14       2014   10      
5   3 Amanah    15       2014   7       
6   3 Bestari   16       2014   1

total student enrolled will be count from student table which is based on the class_name in student table and how many student is associate with the class_name will be calculated as total_student_enrolled

mya
  • 21
  • 4

2 Answers2

2

This query inside the loop is wrong, This will always return the same result for all iterations of your loop

SELECT class_name, COUNT( class_name ) FROM stu_tbl c GROUP BY class_name

And you are always grabbing only first row from it.

It should be

SELECT COUNT(1) FROM stu_tbl c WHERE class_name= 'your variable'

This way it gets you the count only for that very class. This is almost exactly like the query you have disabled in comments. Just that you are not using the variable name correctly there.

How can I prevent SQL injection in PHP?

Community
  • 1
  • 1
Hanky Panky
  • 46,730
  • 8
  • 72
  • 95
0

Is there a special reason, why you don't try to do it with One select? depending on the number of items in the class_tbl this will cause you would send (too) many selects within your while clause.
Try this:

"SELECT 
   class_tbl.class_id, class_tbl.class_name, class_tbl.class_year,
   COUNT(stu_tbl.stu_id) AS total_student
FROM class_tbl
LEFT JOIN stu_tbl ON class_tbl.class_name = stu_tbl.class_name
GROUP BY class_tbl.class_name"

With class key:

"SELECT 
   class_tbl.class_id, class_tbl.class_name, class_tbl.class_year,
   COUNT(stu_tbl.stu_id) AS total_student
FROM class_tbl
LEFT JOIN stu_tbl ON class_tbl.class_name = stu_tbl.class_name
WHERE class_tbl.class_name  like '%$key%'
GROUP BY class_tbl.class_name"

Be sure to avoid SQL injection while using values ($key) from formular parameters or from query string!

pantome
  • 45
  • 1
  • 7
  • the result will returned 0. hmm – mya Feb 24 '15 at 07:59
  • what is the structure of your tables "class_tbl" and "stu_tbl"? – pantome Feb 24 '15 at 08:13
  • stu_tbl ( `stu_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `stu_name` varchar(100) NOT NULL, `gender` char(10) NOT NULL, `dob` date NOT NULL, `parent_name` varchar(50) NOT NULL, `parent_ic` varchar(100) NOT NULL, `address` varchar(100) NOT NULL, `phone` varchar(50) NOT NULL, `email` varchar(70) NOT NULL, `class_name` varchar(100) NOT NULL, PRIMARY KEY (`stu_id`), UNIQUE KEY `stu_name` (`stu_name`), UNIQUE KEY `stu_id` (`stu_id`), KEY `class_name` (`class_name`) ) – mya Feb 24 '15 at 08:34
  • class_tbl ( `class_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `class_name` varchar(50) NOT NULL, `class_year` int(11) NOT NULL, `total_stu_enrolled` int(11) NOT NULL, PRIMARY KEY (`class_id`) ) – mya Feb 24 '15 at 08:37
  • You said that it retured 0. Does it mean it returns no class entry or that for each returned class entry the number of student is 0? If you use the query with the $key variable be sure that the Where-clause comes before the "Group-By-command (see correction above). – pantome Feb 24 '15 at 09:00
  • if i used these query in phpmyadmin, i will get the correct result regarding the total students register to the class: SELECT class_name, COUNT( class_name ) FROM stu_tbl c GROUP BY class_name – mya Feb 24 '15 at 09:18
  • $key = ""; $total_enrolled=mysql_query("SELECT class_tbl.class_id, class_tbl.class_name, class_tbl.class_year, COUNT(stu_tbl.stu_id) AS total_student FROM class_tbl LEFT JOIN stu_tbl ON class_tbl.class_name = stu_tbl.class_name WHERE class_tbl.class_name like '%$key%' GROUP BY class_tbl.class_name"); echo $total_enrolled; – mya Feb 24 '15 at 15:26
  • if i used the codes above, the result will be Resource id #7/8/9/10 and so on. – mya Feb 24 '15 at 15:27
  • Why do you try to print the result with "echo $total_enrolled"? The result of the mysql_query statement should be an array or an object. Please try this: $sql_sel = mysql_query("the query above"); while($row=mysql_fetch_array($sql_sel)){ echo echo $row['class_name']; echo echo $row['total_student']; .... } Or use either php function print_r() or var_dump() to see the structure of your variable after the query statement. – pantome Feb 24 '15 at 18:53