0

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("'", "'", $progID) . "',
    lessonID='" . str_replace("'", "'", $value ) . "',
    comments = '" . str_replace("'", "'", $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.

4
  • 1
    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. Commented Sep 18, 2019 at 20:00
  • Your INSERT query isn't inside the foreach loop. Commented Sep 18, 2019 at 20:01
  • Why are you reassigning $lessonIDs in the loop? Commented Sep 18, 2019 at 20:02
  • You should be getting the last lesson ID, not 0. Commented Sep 18, 2019 at 20:05

2 Answers 2

1

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>";
    }
}
  1. 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.

  2. 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.

  3. 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?

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

Comments

0

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.

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.