PHP/MySQL: Insert multiple data to database and each data add to a new row-CodePudding

I'm new to programming. Regarding my question, I have 3 students, and each student has their own score. Thus, how I want to add those data and each data insert a new row. From this case, will add 3 rows to the database using ONE QUERY ONLY. Below is my current code

save.php

    <?php

        $student1 = "Thomas";
        $student2 = "Benjamin";
        $student3 = "Thomas";

        $student1_score = "100";
        $student2_score = "80";
        $student3_score = "70";


        $query = "INSERT INTO student_record (student_name, student_score) VALUES ('$student1', '$student1_score')";
        $sql = $conn->prepare($query);
        $sql->execute();


            if($sql){

                echo "<script>alert('Record inserted successfully!')</script>";
                echo "<script>window.location = 'index.php'</script>";

            }else{

                echo "<script>alert('Something went wrong. Please try again!')</script>";


    ?>

Can anyone help me on this? I think using foreach. But dont know how to implement.

CodePudding user response：

Try this solution it will work for you:

$conn = mysqli_connect($host, $user, $dbpass, $db);

// 
$students = array();

    $student1 = (object) [
        'name' => "Thomas",
        'score' => "100"
    ];

    $student2 = (object) [
        'name' => "Benjamin",
        'score' => "80"
    ];

    $student3 = (object) [
        'name' => "Thomas",
        'score' => "70"
    ];

    $students[0] = $student1; 
    $students[1] = $student2; 
    $students[2] = $student3; 

    foreach($students as $student)
    {
        $query = "INSERT INTO student_record (`student_name`, `student_score`) VALUES ('$student->name', '$student->score')";
        $sql = $conn->prepare($query);
        $sql->execute();
    
        if($sql){

            echo "<script>alert('Record inserted successfully!')</script>";
            echo "<script>window.location = 'index.php'</script>";

        }else{

            echo "<script>alert('Something went wrong. Please try again!')</script>";

        }
    }

CodePudding user response：

Just create an array like this. and you are good to go, just at least get the idea. analyze it.

$array = array(
        'Thomas' => '100',
        'Benjamin' => '80',
        'Thomas' => '70'
    );
              
foreach( $array as $key => $value ){
    $query = "INSERT INTO student_record (student_name, student_score) VALUES ('$key', '$value')";
        $sql = $conn->prepare($query);
        $sql->execute();
}

CodePudding user response：

Put your student details into multi dimensional array. Then use an Looping Statement for your query execution. I.e

 $student_details = array(
         'Thomas' => '100',
         'Benjamin' => '80',
         'Thomas' => '70');

 foreach( $student_details as $key => $values ){
     $query = "INSERT INTO student_record (student_name, student_score) VALUES ('$key', '$values')";
     $sql = $conn->prepare($query);
     $sql->execute(); }