0

I've been searching for days, now i got to ask here ... I have 1 array which i'd like to insert into sql .. however the structure of the array makes it difficult.

$the_array = [
    [
        "3_Bachelor",
        [
            3 => "English",
            4 => "German"
        ]
    ],
    [
        "3_Master",
        [
            1 => "Marketing",
            2 => "Accountancy"
        ]
    ]
];

Here is the first draw, not working :

$id = "3";

$sql = "INSERT INTO users (id,i d_training_key ,training_lectures)
VALUES (:id, :id_training_key, :training_lectures)";
$stmt= $pdo->prepare($sql);
foreach ($the_array as $lec => $l) {
  foreach ($l as $a) {
    $stmt->execute($l[0], $id, $a);
  }
}

The difficult part lays in the array, in the array, in array ... You see what i mean ?

The DB looks like and "English" and "German" should be in one single line with 'id' and 'id_training_key' repeated on each line: id | id_training_key | training_lectures

Do you guys have any idea or hint ? If you thing that the array should be restructured, what's your opinion ?

Thanks a lot !

Improve this question
3
  • To help clarify, which three values from your array do you want to populate your query with? Right now you're trying to insert an array for the parameter training_lectures - do you want English and German concatenated as a string, a separate line inserted per, or...? Commented Sep 22, 2020 at 23:45
  • 1
    @TCooper thx for comment and the edit ;) The following answers should clarify all your questions : The three values got to be inserted in the db, in three different fields / columns. Ideally, training_lectures should be inserted as separate lines. But if a string with a delimiter is the only solution, then I'd be okay with it. Do you think that the current structure of the array is appropriate for such query ? Commented Sep 23, 2020 at 0:06
  • 1
    Could you please show a SQL example of what the inserts should look like? Commented Sep 23, 2020 at 0:58

2 Answers 2

Reset to default
1

Your main problem is you are using named parameters but only passing in a positional array.

When executing statements in a loop, it can often be simpler to bind parameters pre-emptively and call execute() without any arguments. Try something like this

$id_training_key = 3;
$sql = "INSERT INTO users (id, id_training_key, training_lectures)
    VALUES (:id, :id_training_key, :training_lectures)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(":id", $id);
$stmt->bindParam(":id_training_key", $id_trainging_key);
$stmt->bindParam(":training_lectures", $lang);

foreach ($the_array as [ $id, $langs ]) {
    foreach ($langs as $lang) {
        $stmt->execute();
    }
}

This will perform inserts with like

INSERT INTO users (id, id_training_key, training_lectures)
VALUES ('3_Bachelor', 3, 'English');

INSERT INTO users (id, id_training_key, training_lectures)
VALUES ('3_Bachelor', 3, 'German');

-- etc
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You're close to achieving what you want already.

$id = "3";

$sql = "INSERT INTO users (id,id_training_key,training_lectures)
VALUES (:id,:id_training_key,:training_lectures)";
$stmt= $pdo->prepare($sql);
foreach ($the_array as $lec => $l) {
  foreach ($l as $a) {
    foreach($a as $training_lecture){
        $stmt->execute([':id' => $l[0], ':id_training_key' => $id, ':training_lectures' => $training_lecture);
    }
  }
}

This will populate the query as INSERT INTO users (id,id_training_key,training_lectures) VALUES ('3_Master','3','English') for the first iteration. You can always test what you're queries will look like by replacing the execute statement with manual value bindings (See example 1) and then echoing the statement after.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.