PHP + SQL: Selecting random strings from a sql table and storing it in a new table

Question

So I am trying to find a way to have a random name selected based on information from a table.

Users Table:

UserID	Name	Class
KLM99	Kim Lowe	Maths1
JPM17	Jasper Pepper	Science2
TES1	Tester 1	Science2
TES2	Tester 2	Science2
TES3	Tester 3	Science2
TES4	Tester 4	Science2

Assignment Table

AssignmentID	Class	AssignmentName
1	Maths1	MathsAssignment1
2	Science2	ScienceAssignment1
3	Science2	ScienceAssignment2

Maker Table

MarkerID	AssignmentID	User
1	2	JPM17
2	2	TES1
3	2	TES2
4	2	TES3
5	2	TES4

I want to get a list of all the students in that are in a class (Science2) and randomly select 2 students to mark each others work. Students can only mark 2 other students work.

E.g JPM17 should mark TES1 and TES2. TES1 should mark JPM17 and TES2

All the tables have been created and data is inputed in. I'm just having difficulty with the PHP randomizing it to ensure a student doesn't mark more than 2 students work or doesn't mark their own work.

Code: <?php

                                $students = array();
                                // $getModuleID = "SELECT ModuleID FROM ModuleSelected WHERE UserID = '$UserID'";  ------ AND UserID<>'$UserID'
                                $getModuleID1 = "SELECT 
                                                UserID
                                                FROM
                                                ModuleSelected
                                                WHERE ModuleID='$m' 
                                                ORDER BY RAND()";
                                $ModuleIDquery1=mysqli_query($con, $getModuleID1);
                                $count =1;
                                foreach($ModuleIDquery1 as $row1) {
                                    $UserID = $row['UserID'];
                                    echo "<tr>";
                                    echo "<td>".$count."</td>";
                                    echo "<td> test </td>";
                                    echo "<td>".$row1['UserID']."</td>";
                                    echo "</tr>";
                                    array_push($students, $row1['UserID']);
                                    $count++;
                                    
                                }
                                
                                foreach($students as $key => $value){
                            echo "$value<br>";
                                            
                        }

                                    
                        
                        $my_array = $students;
                        shuffle($my_array);
                        foreach($my_array as $key => $value2){
                            
                            
                            echo "<br>$value2<br>";
                        }
                            ?>

Using the sufflearray it would randomize the array but it only does it once. Is there a way to get two randomized names and then remove them 2 from the list?

I have a sql table to store the randomized markers.

I am open to better suggestions of randomizing my data if there is a different function in PHP available.

So in the example above can you work through the rest of the students? Example is JPM17 -> TES1/TES2, TES1 -> JPM17/TES2, TES2 -> JPM17/TES3, TES3 -> TES4, TES4 -> TES3? — hppycoder, Mar 17 '21 at 17:54
Yes, there doesn't have to be a specific order to it, i want each student to have 2 unique markers. It can be random. So TES4's work can be marked by TES1 and TES3 E.g [link](https://ibb.co/QPwGj9M) — A Alizadeh, Mar 17 '21 at 18:10
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — hppycoder, Mar 17 '21 at 19:06

score 0 · Accepted Answer · answered Mar 17 '21 at 19:04

This is not going to be a true random way but it will ensure that there's no overlap and handle unbalanced student amounts. To get a random list when you are selecting the users from the database you could randomize, or shuffle $students initially.

<?php
$fixtureStudents = ['JPM17', 'TES1', 'TES2', 'TES3', 'TES4'];

$students = array();
// Your code to pull from the database, using prepared statements
foreach ($fixtureStudents as $row1) {
    // Your formatting for the output
    array_push($students, $row1);
}

// Clones the $students array into $firstReviewers
$firstReviewers = array_merge(array(), $students);
// Move the first element in the array to the end
array_push($firstReviewers, array_shift($firstReviewers));

// Clones the $firstReviews (so the shift has happened) into new array
$secondReviewers = array_merge(array(), $firstReviewers);
// Move the first element to the end (so now it's shifted by two)
array_push($secondReviewers, array_shift($secondReviewers));

$reviews = [];
foreach($students as $key => $student) {
    $reviews[$student] = [$firstReviewers[$key], $secondReviewers[$key]];
}

var_dump($reviews);

Output:

array(5) {
  ["JPM17"]=>
  array(2) {
    [0]=>
    string(4) "TES1"
    [1]=>
    string(4) "TES2"
  }
  ["TES1"]=>
  array(2) {
    [0]=>
    string(4) "TES2"
    [1]=>
    string(4) "TES3"
  }
  ["TES2"]=>
  array(2) {
    [0]=>
    string(4) "TES3"
    [1]=>
    string(4) "TES4"
  }
  ["TES3"]=>
  array(2) {
    [0]=>
    string(4) "TES4"
    [1]=>
    string(5) "JPM17"
  }
  ["TES4"]=>
  array(2) {
    [0]=>
    string(5) "JPM17"
    [1]=>
    string(4) "TES1"
  }
}

Thank you so much hppycoder, this really helps. I modified the fixtureStudents array to store the list of students from my sql and randomise them which now works. What would be the best way of storing the data into sql. So I want to store the students name, marker one's name and also marker twos name into my markers table. Will i need to convert the $reviews to string first? — A Alizadeh, Mar 17 '21 at 19:46
Happy this helped! Please accept my answer if you feel it was good. As far as what to do next it's up to you. I personally would iterate over `$reviews` and using `\PDO` perform parameterized insert statements. The fixture students was just so you could see what it looked like — hppycoder, Mar 17 '21 at 19:52
The key of `$reviews` is the user who should review the students in the array. Example key of `JPM17` needs to review `TES1`, and `TES2` — hppycoder, Mar 17 '21 at 19:54

score 0 · Answer 2 · answered Mar 17 '21 at 19:27

I hope this is what you are looking for:

$students = ["s1", "s2"];
$teachers  = ["t1", "t2", "t3", "t4", "t5", "t6"];


foreach ($students as $student) {
    $two_teachers = array_rand($teachers, 2);
    
    echo "Student: {$student}, Teachers: {$teachers[$two_teachers[0]]} and {$teachers[$two_teachers[1]]}";

    foreach ($two_teachers as $teacher) {
        unset($teachers[$teacher]);
    }
}

Output:

Student: s1, Teachers: t4 and t6 Student: s2, Teachers: t1 and t2

array_rand function chooses two random, and not equal, values from the array but stores them as their index: https://www.w3schools.com/php/func_array_rand.asp

PHP + SQL: Selecting random strings from a sql table and storing it in a new table

2 Answers2