skip to Main Content

I have an array of column headers and an array of corresponding values at the same index
I need to update a row using both those arrays in SQL.

I know this might not be the neatest way of doing this. How I’ve got the values is I take the SQL database columns and use those to generate an HTML form which gives the values hence why both are in an array.

Using an INPUT my SQL looks like this and works how I want it to:

$strcol = implode(",", $fieldname);
$strdata = implode(",", $fielddata);
$placeholders = str_repeat(" ?, ", count($fielddata)-1) . "?";

print_r($placeholders);
$sql = "INSERT INTO morning (" . $strcol . ")
VALUES (". $placeholders . ")";
print_r($sql);
$result = $conn->execute_query($sql, $fielddata);

That gives a result something like:
enter image description here

BUT I can’t figure out how to do the same with an UPDATE, I might have missed a really obvious solution somewhere

2

Answers


  1. // find the value of the primary key (assuming it is "id")
    $idKey = array_search("id", $fieldname);
    if ($idKey === false) {
      // return error because the data does not include the primary key
    }
    $idValue = $fielddata[$idKey];
    
    $assignments = implode(", ", array_map(function($col) { return "`$col`=?"; }, $fieldname));
        
    $sql = "UPDATE morning SET {$assignments} WHERE id=?";
    $result = $conn->execute_query($sql, array_merge($fielddata, [$idValue]));
    
    Login or Signup to reply.
  2. // Assuming $fieldname is an array containing column headers
    // and $fielddata is an array containing corresponding values
    
    // Find the index of the primary key (assuming it is "id")
    $idKey = array_search("id", $fieldname);
    
    // Check if the primary key is found
    if ($idKey === false) {
        // If the primary key is not found, return an error because the data does not include the primary key
        // You may want to handle this error differently based on your application's requirements
        // For example, you could throw an exception or log an error message
        // Here, we are simply printing an error message
        echo "Primary key 'id' not found in column headers.";
        // You might want to exit the script or return from the function here
        exit;
    }
    
    // Extract the value of the primary key from $fielddata
    $idValue = $fielddata[$idKey];
    
    // Construct the SET part of the UPDATE statement with column-value pairs
    // We're using array_map to iterate over $fieldname array and generate "`column` = ?" strings
    // The lambda function inside array_map surrounds each column name with backticks and appends '=?' for parameterized query
    $assignments = implode(", ", array_map(function($col) { 
        return "`$col`=?"; 
    }, $fieldname));
    
    // Construct the UPDATE statement
    $sql = "UPDATE morning SET {$assignments} WHERE id=?";
    
    // Execute the UPDATE query
    // We merge $fielddata with an array containing only the primary key value at the end
    // This is because the WHERE clause requires the primary key value
    $result = $conn->execute_query($sql, array_merge($fielddata, [$idValue]));
    

    Explanation:

    • $idKey: This variable stores the index of the primary key column
      (‘id’) in the $fieldname array. It uses the array_search function to
      find the index.
    • Error Handling: If the primary key is not found in the $fieldname
      array, an error message is printed, and the script exits. You should
      handle this error according to your application’s requirements.
    • $idValue: This variable extracts the value of the primary key from
      the $fielddata array using the $idKey index.
    • $assignments: This variable holds the SET part of the UPDATE
      statement. It iterates over the $fieldname array using array_map and
      generates column-value pairs formatted as "column = ?".
    • UPDATE Statement: The UPDATE statement is constructed using the
      $assignments variable for the SET part and the primary key for the
      WHERE clause.
    • Execute Query: The UPDATE query is executed using the
      $conn->execute_query() method. The $fielddata array is merged with an
      array containing only the primary key value for parameterized query
      execution.
      Hopefully, You enjoyed the explanation.
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search