Store an array in multiple rows in MySQL

Question

I am just trying to store an array in multiple rows using MySQL Database. Unfortunately, it saves only the last value.

<?php    
session_start();
require_once "../phpquery/dbconnection.php";
    
$testings       = $_POST["testings"];
$presid         = $_POST["presid"];
$lab_status     = "0";
$patient_status = "1";
$update_status  = "0";
    
foreach( $testings as $testing) {
    
    if($stmt = mysqli_prepare($con,"INSERT INTO testing_schedule 
                                (p_id,pres_id,testing_perform,submit_date,
                                    lab_status,patient_status,update_status) 
                            VALUES ( ?,?, ?, ?, ?,?,?)")){
    
        $cur_date = date('Y-m-d');
        mysqli_stmt_bind_param($stmt, "sssssss",
                                    $_SESSION['p_id'],
                                    $presid , 
                                    $testing,
                                    $cur_date,
                                    $lab_status,
                                    $patient_status,
                                    $update_status);
    
        echo "Successfully saved";
    } else{
        echo "ERROR: Could not prepare query: $con. " . mysqli_error($con);
    }
}
?>

image of use with printr():

While that is not coded in an optimal way, that code should create more than one row if `$testings` is an array with more than one occurance. So please check/prove that `$testings` is actually an array with more than one occurance — RiggsFolly, Aug 25 '20 at 19:49
Having this line `echo "Successfully saved";` inside the foreach loop is misleading ay best — RiggsFolly, Aug 25 '20 at 19:51

score 1 · Accepted Answer · answered Aug 25 '20 at 19:07

You should do it this way:

• First, prepare the statement once, with ? placeholders as you are now doing. Separately prepare each statement that you will need.

• Now, before starting the loop, BEGIN TRANSACTION.

• Next, iterate through the loop, executing the prepared statement with the proper placeholder values each time.

• Finally, COMMIT.

• Now, use a try{} block to catch any errors that may occur during the loop. If this occurs, ROLLBACK the transaction instead of committing it.

"SQL transactions," as illustrated here, make the entire update atomic. No one will see the changes while you are in the process of making them. Instead, they will see the entire change happen "all at once" when you commit. Whereas, if the transaction is instead rolled back, "nothing happened at all." (Plus, in most systems, transactions make these operations considerably more efficient ...)

@MohamedNasik See https://stackoverflow.com/a/63318861/1839439 — Dharman, Aug 25 '20 at 21:38

Store an array in multiple rows in MySQL

1 Answers1