How insert select multiple data into db, with same reference ID for all rows

Question

I'm trying to insert data from a select multiple form field into a MySQL db.

Each insert row needs to have the same reference field,($_POST['progID'] in the code below. The select array comes from $_POST['lessonID']. So if the array is filename1, filename5, filename 6, etc. they would be entered into distinct rows, each row having the same $progID. Below is my code sample. (I haven't done any sanitization, etc. of the data yet.)

if(isset($_POST["submit"])){
    $progID=$_POST['progID'];
    $lessonIDs=$_POST['lessonID'];
    $comments=$_POST['comments'];


    foreach ($lessonIDs as $value){
        $lessonIDs= $value;
    }
    $query = "INSERT INTO lesson_school_lessonstocourses SET
    date=current_timestamp(),
    progID='" . str_replace("'", "&#039;", $progID) . "',
    lessonID='" . str_replace("'", "&#039;", $value ) . "',
    comments = '" . str_replace("'", "&#039;", $comments ) . "'
    ";
    if (mysqli_query($link,$query)) {
        $last_id = mysqli_insert_id($link);
        echo "New record created successfully<br>";
    } 
    else {
        echo("<p>Error adding lesson: " .
             mysqli_error($link) . "</p>");
    }

I expect to see many rows with the same progID, with each row having a distinct lessonID. All I get now is only one row entered with the proper progID (instead of many rows), but with the lessonID column is "0". I'm not getting any on-screen errors.

Why are you replacing quotes with HTML entities in the database? Keep the database pure, and encode the data when you display it on a web page. — Barmar, Sep 18 '19 at 20:00

score 1 · Answer 1 · answered Sep 18 '19 at 20:08

No offense, but there is just so much wrong with your code that nothing is worth saving. Let me show you some of the main problems in your code by writing a new one as an example.

$host = 'localhost';
$db = 'test';
$user = 'username';
$pass = '1234';
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli($host, $user, $pass, $db);
$link->set_charset('utf8mb4');

if (isset($_POST["submit"])) {
    $stmt = $link->prepare('INSERT INTO lesson_school_lessonstocourses SET date=current_timestamp(), progID=?, lessonID=?, comments=?');
    $stmt->bind_param('sss', $_POST['progID'], $lesson, $_POST['comments']);
    foreach ($_POST['lessonID'] as $lesson) {
        $stmt->execute();
        $last_id = $link->insert_id;
        echo "New record created successfully<br>";
    }
}

Always use prepared statements. Your code is vulnerable to SQL injection and you should always use parameterized prepared statements instead of manually building your queries. As an added benefit you only need to prepare the query once and you can execute it multiple times with different data which should speed up your code.
Enable mysqli error reporting. Use mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); and you won't have to check the return value of mysqli functions.
Forget about data sanitization. All it means is that you are damaging your data. Never sanitize data unless you want it broken. To prevent SQL injections use prepared statements and to prevent XSS see https://paragonie.com/blog/2015/06/preventing-xss-vulnerabilities-in-php-everything-you-need-know and How to prevent XSS with HTML/PHP?

score 0 · Answer 2 · answered Sep 18 '19 at 20:10

Use a prepared statement and do the INSERT queries inside the loop.

$stmt = $link->prepare("INSERT INTO lesson_school_lessonstocourses SET
    date = current_timestamp(),
    progID = ?,
    lessonID = ?,
    comments = ?");
$value = null;
$stmt->bind_param("iis", $progID, $value, $comments);
foreach ($lessonIDs as $value) {
    if ($stmt->execute()) {
        echo "New record inserted successfully";
        $last_id = $stmt->insert_id;
    } else {
        echo "<p>Error adding lesson: " . $stmt->error . "</p>";
    }
}

Note that $last_id will just be the ID of the last lesson that was inserted. If you need all of them you should put them in an array instead of a single variable.

How insert select multiple data into db, with same reference ID for all rows

2 Answers2