如何遍历SQL表并从预定义的表中将值分配给另一个SQL表？

Question

I am creating a program that takes current made shifts from a table and gives users shifts in another table called schedule based on their staffID is which is from the userStaff table.

I am simply looking for it to loop through each staff member on each date and give them a random shift from the the preset shifts in the table.

My first theory was, iterate through the userStaff table where a random integer from the amount of shifts available for the shift position and then choose one and assign it on the schedule table. However, it keep coming up with no values and only looking through one staffID.

I couldn't find anything that was as specific as this. The error I am having is: no values are assigned and only one user ID is iterated for?

UserStaff Table Example:

ID - 1, 2, 3

Username - jenk3194, jake1233, rodger1293

Position - BM, CT, CM

Shifts Table Example:

ID - 1, 2, 3

shiftstart - 09:00:00, 12:00:00, 16:00:00

shiftend - 17:00:00, 21:00:00, 00:00:00

shiftperson - BM, CT, CM

Schedule Table Example:

ID - 1, 2, 3

staffID - 1, 2, 3

cdate - 2017-01-01, 2017-01-01, 2017-01-01

starttime - 12:00:00, 16:00:00, 09:00:00

endtime - 21:00:00, 00:00:00, 17:00:00

Here is my full current code:

$sql = "SELECT * FROM userStaff";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) 
{
// output data of each row
while($row = mysqli_fetch_assoc($result)) 
{
    $date = "2017-03-13";
    #$date = date("Y-m-d");

    $begin = new DateTime($date);
    $end = new DateTime(date('Y-m-d', strtotime($date . '+ 7 day'))); // you can change + 1 year to what you need

    $interval = new DateInterval('P1D'); // one day
    //$interval = new DateInterval('P1W'); // one week

    $daterange = new DatePeriod($begin, $interval, $end);

    foreach ($daterange as $date) { // loop through dates
        $fDate = $date->format("Y-m-d");
        $rowID = $row['id'];
        $sql = "SELECT position FROM userStaff WHERE id = '$rowID'";
        echo $sql;
        $result = mysqli_query($conn, $sql);
        if (mysqli_num_rows($result) > 0 )
        {
            $int = mt_rand(1, 3);
            $sql = "SELECT * FROM shifts WHERE id = $int AND shiftperson = '".  $row['position'] . "'";
            $result = mysqli_query($conn, $sql);
            if (mysqli_num_rows($result) > 0)
            {
                while ($row = mysqli_fetch_array($result)) {
                    $shiftSt = $row['shiftstart'];
                    $shiftEn = $row['shiftend'];
                    $sql = "INSERT INTO schedule (staffID, cdate, starttime, endtime) VALUES ('$rowID','$fDate','$shiftSt','$shiftEn')";
                    echo $sql . "<br>";
                    if (mysqli_query($conn, $sql)) 
                    {
                        echo "<br>Success<br>";
                    }
                    else
                    {
                        echo "<br>Failure<br>";
                    }
                }
            }
        }
        else
        {
            echo "Failed";
            }
        }
   }
} 
else 
{
    echo "<p>No Results</p>";
}

Answer 1

This code here is the working code that includes an entire year of data. Therefore 2190 results for all 6 users in my database. I have 17 results in my preset shift table for each position therefore it loops through all of them.

#echo "User Schedule Populated";

$sql="SELECT id FROM userStaff";

$result = mysqli_query($conn, $sql); 

$allID = Array();

while ($row=mysqli_fetch_array($result,MYSQLI_ASSOC)) 
{
    $allID[] = $row['id'];

}

foreach($allID as $idR)
{
    $date = "2017-01-01";

    $rowID = $idR;
    echo "RowID: " . $rowID . "<br>";
    echo "Count 1: " . $counter;
    $begin = new DateTime($date);
    $end = new DateTime(date('Y-m-d', strtotime($date . '+ 1 year')));

    $interval = new DateInterval('P1D'); // one day

    $daterange = new DatePeriod($begin, $interval, $end);
    foreach ($daterange as $date)
    {   
        $fDate = $date->format("Y-m-d");
        $counter ++;
        echo "<br> Count 1: " . $counter . "<br>";
        echo "<br>";
        $sql = "SELECT position FROM userStaff WHERE id ='$rowID'";
        echo $sql;

        $result = mysqli_query($conn, $sql);

        $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
        $int = mt_rand(1,17);
        echo "<br>";
        $sql = "SELECT * FROM shifts WHERE shiftnum = '$int' AND shiftperson = '" . $row['position'] . "'";
        $result = mysqli_query($conn, $sql);
        echo $sql;
        $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
        $shiftSt = $row['shiftstart'];
        echo "<br> Shift St: " . $shiftSt;
        $shiftEn = $row['shiftend'];
        echo "<br> Shift En: " . $shiftEn;
        $sql = "INSERT INTO schedule (staffID, cdate, starttime, endtime) VALUES ('$rowID','$fDate','$shiftSt','$shiftEn')";
        echo "<br> Insert: " . $sql;
        mysqli_query($conn, $sql);

    }

}
die();