count and display each record number

Question

let say i have two tables example below

1st Table

CREATE TABLE `student` (
  `student_id` int(11) NOT NULL,
  `student_name` varchar(255) NOT NULL,
  `class_id` int(11) NOT NULL,
);

Example Data

1,James,1
2,Dorris,1
3,Maximus,2
4,Paul,1

2nd table

CREATE TABLE `class` (
  `class_id` int(11) NOT NULL,
  `class_name` varchar(255) NOT NULL,
);

example data

1, Red 
2, Blue

For each student record i would like to give some serial number such as

classname/totalstudentoftheclass/recordnumber

example for james - red/3/1 doris - red/3/2 Paul - red/3/3

Maximus - blue/1/1

what i tried so far

$result="SELECT * FROM class where class_id='1' ";
     $result=mysqli_query($connection,$result);
      $row=mysqli_fetch_array($result);
  $class_name=$row['class_name'];


$getstudent="SELECT * FROM student where class_id='1' and student_id='1'";
     $result=mysqli_query($connection,$getstudent);
      $totalstudent=mysqli_num_rows($getstudent);

      echo "$class_name/$totalstudent/";

How do i get the record number, i think using count in query..i need help

Tom Lima · Answer 1 · 2019-04-19T14:29:54.763

Try this solution :)

 $sql = "SELECT T0.student_id as studentId,  T0.student_name as studentName, 
 T0.class_id as classId, T1.class_name as className, (SELECT COUNT(*) FROM 
 student WHERE class_id = T1.class_id) as totalStudents FROM student T0 
 INNER JOIN class T1 ON T0.class_id = T1.class_id";

 $result = mysqli_query($conn,$sql);
 while($row = mysqli_fetch_array($result)){
     $studentName = $row["studentName"];
     $classId = $row["classId"];
     $className = $row["className"];
     $totalStudents = $row["totalStudents"];
     if(!isset($record[$classId])){
        $record[$classId] = 0;
     }
     $record[$classId] = $record[$classId] + 1;

     // Print results
     printf("<b>".$studentName."</b></br> Class name: ".$className." 
     </br> 
     Total students: ".$totalStudents."</br> Record number: 
     ".$record[$classId]."</br></br><hr></br> ");
 }

My bad, you're right! @cmnardi thx for comment :) I made some adjustments, now I think it's better. — Tom Lima, Apr 19 '19 at 14:30

score 0 · Answer 2 · answered Apr 19 '19 at 13:40

Try this SELECT, and see the result You can concat the columns as you need in your PHP script I don't test it, if you have problems with the row conter make it in the script too, or check this post generate an integer sequence in MySQL

SELECT 
    s.student_name,
    c.class_name, 
    (SELECT count(*) FROM student WHERE class_id = s.class_id),
    @row := @row + 1 as row
FROM student s, (SELECT @row := 0) r
INNER JOIN class c

score 0 · Accepted Answer · answered Apr 19 '19 at 14:05

Join the 2 tables and create the new column like this:

SELECT 
  s.*,
  concat(
    c.class_name, '/',
    (SELECT count(*) FROM student WHERE class_id = s.class_id), '/',
    (SELECT count(*) FROM student WHERE class_id = s.class_id AND student_id < s.student_id) + 1
  ) serial 
FROM student s INNER JOIN class c
ON c.class_id = s.class_id

See the demo.
Results:

| student_id | student_name | class_id | serial   |
| ---------- | ------------ | -------- | -------- |
| 1          | James        | 1        | Red/3/1  |
| 2          | Dorris       | 1        | Red/3/2  |
| 3          | Maximus      | 2        | Blue/1/1 |
| 4          | Paul         | 1        | Red/3/3  |

count and display each record number

3 Answers3