CODEWITHSUNDEEP
phpmysql
Ivan Sinigaglia
Ivan Sinigaglia

Reputation: 1072

Splitting and distributing data from two tables into a new one

I have been studying and learning PHP and MySQL and I have started a system that I'm developing for a friend's little school and to help me to improve my learning. I basically have in this case a table with the names of the students (tb_std) and another with the names of the teachers (tb_tch). The work is to distribute these students among the teachers in a new table, which is the way I think it will work better (tb_final).

  1. I basically need each student to have a randomly chosen teacher so that the distribution is numerically even among the teachers.

In this example, I have 7 teachers and 44 students. Using SELECT query I did the operations to find out how many students would be for each teacher (add/division/mod), but how to make this draw to play in this new table I have no idea where to start.

1) Students (tb_std)
    ---------
    id |std
    1  | A1
    2  | A2
    3  | A3
    (...)
    44 | A44

2) Teachers (tb_tch)
    --------
    id |tch
    --------
    1  | T1
    2  | T2
    3  | T3
    (...)
    7  | T7

3) Final Combo (tb_final)
    --------------------
    id| student | teacher 
    --------------------
    1 |  A1     | T1   
    2 |  A2     | T2    
    3 |  A3     | T4    
    4 |  A4     | T6    
    5 |  A5     | T2    
    (...)

This is what I her in code until now:

<?php
session_start();
include_once("../conn/conexao.php");

$str_std="SELECT COUNT(*) as tstd FROM tb_std ";
$qry_std=mysqli_query($conn,$str_std);
while($res1=mysqli_fetch_assoc($qry_std)){
    $v1 = $res1['tstd']; // 44 students
    echo "Students: ".$res1['tstd']."<br>";
}

$str_tch="SELECT COUNT(*) as ttch FROM tb_tch ";
$qry_tch=mysqli_query($conn,$str_tch);
while($res2=mysqli_fetch_assoc($qry_tch)){
    $v2 = $res2['ttch']; // 7 teachers
    echo "Teachers ".$res2['ttch']."<br><br>";
}

$div = number_format($v1 / $v2);
$res = $v1 % $v2;
echo "Stud/Teach: ".$div."<br>"; // 6 std per teach
echo "Mod: ".$res."<br>"; // mod 2

?>

I have found some discussions including instructions CREATE and UNION, but I couldn't find other discussions around here that would help me with this division and distribution between tables to generate this new result.

Upvotes: 1

Views: 141

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can solve this by next (a bit a complicate) query using window functions:

    select 
        s.id, std student, tch teacher
    from (
      -- get Students with row_numbers 
      select 
          id,
          row_number() over w as rn,
          std
      from tb_std
      window w as (order by id)
    ) s
    cross join (
        -- get Teachers count and join to each student row
        select count(*) tcnt from tb_tch
    ) tcnt
    join (
      -- select Teachers and join them to students depends row_number
      -- divided to teachers count
      select 
          row_number() over w as trn,
          tch
      from tb_tch
      window w as (order by id)
    ) t on mod(s.rn, tcnt) = t.trn -1;

MySQL 8.0 fiddle

Result:

+====+=========+=========+
| id | student | teacher |
+====+=========+=========+
| 1  | S0      | T2      |
+----+---------+---------+
| 2  | S1      | T3      |
+----+---------+---------+
.........................
+----+---------+---------+
| 20 | S19     | T7      |
+----+---------+---------+
| 21 | S20     | T1      |
+----+---------+---------+
| 22 | S21     | T2      |
+----+---------+---------+

Upvotes: 1

Related Questions