Php mysql, inserting data from 3 different tables (one with a different length)

advertisements

I'm trying to insert these 3 different arrays into my table:

Array
(
    [0] => 1
    [1] => 2
)
Array
(
    [0] => 2017-01-01
    [1] => 2017-01-02
    [2] => 2017-01-03
    [3] => 2017-01-04
    [4] => 2017-01-05
    [5] => 2017-01-06
    [6] => 2017-01-07
    [7] => 2017-01-08
    [8] => 2017-01-09
    [9] => 2017-01-10
    [10] => 2017-01-11
    [11] => 2017-01-12
    [12] => 2017-01-13
    [13] => 2017-01-14
    [14] => 2017-01-15
    [15] => 2017-01-16
    [16] => 2017-01-17
    [17] => 2017-01-18
    [18] => 2017-01-19
    [19] => 2017-01-20
    [20] => 2017-01-21
    [21] => 2017-01-22
    [22] => 2017-01-23
    [23] => 2017-01-24
    [24] => 2017-01-25
    [25] => 2017-01-26
    [26] => 2017-01-27
    [27] => 2017-01-28
    [28] => 2017-01-29
    [29] => 2017-01-30
    [30] => 2017-01-31
)
Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 3
    [4] => 3
    [5] => 3
    [6] => 1
    [7] => 2
    [8] => 4
    [9] => 4
    [10] => 4
    [11] => 4
    [12] => 1
    [13] => 2
    [14] => 3
    [15] => 3
    [16] => 3
    [17] => 3
    [18] => 1
    [19] => 2
    [20] => 4
    [21] => 4
    [22] => 4
    [23] => 4
    [24] => 1
    [25] => 2
    [26] => 3
    [27] => 3
    [28] => 3
    [29] => 3
    [30] => 1
)

The arrays are filled with some sql select queries, so first array ($arrayUsers) is always a different length, but the second ($arrayData) and third ($arrayTurno) will be always the same length.

I want to something like this:

|id(first array)|date(second array)|shift(third array)|
|1              |2017-01-01        |1                 |
|1              |2017-01-02        |2                 |
|1              |2017-01-03        |3                 |
|1              |2017-01-04        |3                 |
|...            |...               |...               |
|2              |2017-01-01        |1                 |
|2              |2017-01-02        |2                 |
|2              |2017-01-03        |3                 |
|2              |2017-01-04        |3                 |
|...            |...               |...               |

And so on.

I'm trying with the following code:

foreach($arrayUsers as $user) {

    $sql = "INSERT INTO `escala`(`id_assistente`, `id_data`, `id_horario`) VALUES";

    for ($i = 0; $i < count($arrayTurno); $i++) {
        $sql .= "('$user', '$arrayData[$i]','$arrayTurno[$i]'),";
    }

}

$sql = rtrim($sql, ',');
echo $sql;
$result = mysqli_query($conn,$sql);

The issue is I only get the last number in first array.

Thanks in advance for any help.


Put INSERT INTO statement outside for loop

$sql = "INSERT INTO `escala`(`id_assistente`, `id_data`, `id_horario`) VALUES";
    foreach($arrayUsers as $user) {
        for ($i = 0; $i < count($arrayTurno); $i++) {
            $sql .= "('$user', '$arrayData[$i]','$arrayTurno[$i]'),";
        }
    }
    $sql = rtrim($sql, ',');
    echo $sql;
    $result = mysqli_query($conn,$sql);