PHP Update multiple records

[lwaters55]

I am trying to update multiple records in a database and nothing happen with the existing code. It should update all entries’ supplemental_info value when the $_GET[“dataID”] variable equals the originating_dataID value in the new_file2 query. Thank you in advance with your help

Here is my code:

if ($originator == 'Y') {
$new_file2 = new WA_MySQLi_RS("new_file2",$sdpc_i,1);
$new_file2->setQuery("SELECT supplemental_info FROM data WHERE dataID = ?");
$new_file2->bindParam("i", "".$_GET['dataID']  ."", "-1"); //colname
$new_file2->execute();
$supplemental_info_file_name = $new_file2->getColumnVal("supplemental_info");
$new_file = new WA_MySQLi_RS("new_file",$sdpc_i,0);
$new_file->setQuery("SELECT dataID FROM data WHERE originating_dataID = ?");
$new_file->bindParam("i", "".$_GET['dataID']  ."", "-1"); //colname
$new_file->execute();
while($row = mysqli_fetch_array($new_file)) {
   $multiple_dataID[] = $row['dataID'];
   }
foreach ($multiple_dataID as $thedataID) {
$UpdateQuery = new WA_MySQLi_Query($sdpc_i);
$UpdateQuery->Action = "update";
$UpdateQuery->Table = "`data`";
$UpdateQuery->bindColumn("supplemental_info", "s", "".((isset($supplemental_info_file_name))?$supplemental_info_file_name:"")  ."", "WA_NULL");
$UpdateQuery->addFilter("dataID", "=", "i", "".($thedataID)  ."");
  $UpdateQuery->execute();
  }
  $UpdateGoTo = "";
  if (function_exists("rel2abs")) $UpdateGoTo = $UpdateGoTo?rel2abs($UpdateGoTo,dirname(__FILE__)):"";
  $UpdateQuery->redirect($UpdateGoTo);
}

[Strider64]

if ($originator == 'Y') {
    // Establish a MySQLi connection
    $mysqli = new mysqli('localhost', 'username', 'password', 'your_database_name');

    // Check connection
    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }

    // Prepare statement to fetch supplemental_info
    $stmt1 = $mysqli->prepare("SELECT supplemental_info FROM data WHERE dataID = ?");
    $stmt1->bind_param("i", $_GET['dataID']);
    $stmt1->execute();
    $stmt1->bind_result($supplemental_info_file_name);
    $stmt1->fetch();
    $stmt1->close();

    // Prepare statement to fetch dataIDs
    $stmt2 = $mysqli->prepare("SELECT dataID FROM data WHERE originating_dataID = ?");
    $stmt2->bind_param("i", $_GET['dataID']);
    $stmt2->execute();
    $result2 = $stmt2->get_result();
    $multiple_dataID = [];
    while ($row = $result2->fetch_assoc()) {
        $multiple_dataID[] = $row['dataID'];
    }
    $stmt2->close();

    // Update supplemental_info for each dataID
    $stmt3 = $mysqli->prepare("UPDATE data SET supplemental_info = ? WHERE dataID = ?");
    $stmt3->bind_param("si", $supplemental_info_file_name, $thedataID);

    foreach ($multiple_dataID as $thedataID) {
        $stmt3->execute();
    }
    $stmt3->close();

    // Redirect
    $UpdateGoTo = ""; // Define your redirect location
    header("Location: $UpdateGoTo");
    exit();
}

1. Fetching supplemental_info: The first prepared statement ($stmt1) selects the supplemental_info from the data table where dataID matches the value passed via $_GET['dataID'].
2. Fetching dataID values: The second prepared statement ($stmt2) selects dataID from the data table where originating_dataID matches the value passed via $_GET['dataID'].
3. Updating multiple records: After fetching the supplemental_info and dataID values, the code iterates through each dataID obtained from the second query and updates the supplemental_info field for each record in the data table where dataID matches the current value of $thedataID.
4. Redirecting: Finally, after the updates are performed, the code redirects the user to the specified location.

The code is untested and I am not sure if I understood you fully?

[lwaters55]

Thank you so much! I will give it a try.

[lwaters55]

@Strider64

if ($originator == 'Y') {
    // Establish a MySQLi connection
    $mysqli = new mysqli('localhost', 'username', 'password', 'your_database_name');

    // Check connection
    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }

    // Prepare statement to fetch supplemental_info
    $stmt1 = $mysqli->prepare("SELECT supplemental_info FROM data WHERE dataID = ?");
    $stmt1->bind_param("i", $_GET['dataID']);
    $stmt1->execute();
    $stmt1->bind_result($supplemental_info_file_name);
    $stmt1->fetch();
    $stmt1->close();

    // Prepare statement to fetch dataIDs
    $stmt2 = $mysqli->prepare("SELECT dataID FROM data WHERE originating_dataID = ?");
    $stmt2->bind_param("i", $_GET['dataID']);
    $stmt2->execute();
    $result2 = $stmt2->get_result();
    $multiple_dataID = [];
    while ($row = $result2->fetch_assoc()) {
        $multiple_dataID[] = $row['dataID'];
    }
    $stmt2->close();

    // Update supplemental_info for each dataID
    $stmt3 = $mysqli->prepare("UPDATE data SET supplemental_info = ? WHERE dataID = ?");
    $stmt3->bind_param("si", $supplemental_info_file_name, $thedataID);

    foreach ($multiple_dataID as $thedataID) {
        $stmt3->execute();
    }
    $stmt3->close();

    // Redirect
    $UpdateGoTo = ""; // Define your redirect location
    header("Location: $UpdateGoTo");
    exit();
}

Thanks so much. I had to make a small tweak and it worked!