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

Question

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.

Rafiqul Islam · Accepted Answer · 2017-03-12 03:44:08Z

1

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);

answered Mar 12, 2017 at 3:44

Rafiqul Islam

1,6561 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RAAPereira Over a year ago

I can't believe it was so easy! Thank you so very mutch. It is working fine

icecub Over a year ago

I missed the INSERT duplication part in my answer. Well done :)

icecub · Accepted Answer · 2017-03-12 02:57:47Z

0

It is quite simple. You are only getting the last result because you are resetting the $sql variable on each loop. Everytime the loop passed this part: $sql = "INSERT ... it removes anything that is already in there from the previous loop. Try it like this instead and it will work like a charm:

<?php

$arrayUsers = '';// array here
$arrayData = '';// array here
$arrayTurno = '';// array here
$sql = '';

foreach($arrayUsers as $user){
    foreach($arrayData as $arrayDataKey => $arrayDataValue){
        $sql .= "INSERT INTO `escala`(`id_assistente`, `id_data`, `id_horario`) VALUES";

        $sql .= "('". $user ."', '". $arrayDataValue ."','". $arrayTurno[$arrayDataKey] ."'),";
    }
}

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

?>

answered Mar 12, 2017 at 2:57

icecub

8,7936 gold badges50 silver badges80 bronze badges

2 Comments

RAAPereira Over a year ago

Although the echo $sql; prints the whole query, it doesn't insert any data! Thank you for your help. I will go with Rafiq's approach.

icecub Over a year ago

@Motorui Yup, I missed the INSERT duplication part. My bad.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related