Update records using mysqli functions with binding parameters

Student Table : Sample Data and query to create table
config.php Database connection object $connection is taken from config.php file.

Updating all records (without parameters )

require "config.php";// Database connection file,$connection object
$query="UPDATE  student SET class='Four'";
if ($connection->query($query)) {
echo "Records Updated";
}else{
echo $connection->error;
}

With number of rows updated

$query="UPDATE  student SET class='Four'";
if ($connection->query($query)) {
echo "Records Updated : ";
echo $connection->affected_rows;
}else{
echo $connection->error;
}

With Parameters by using bind_param()

<?Php
require "config.php";// Database connection file.

$class='Three';
$mark=66;
$id=5;
$query="UPDATE  student SET class=?,mark=? WHERE id=?";
$stmt = $connection->prepare($query);
if ($stmt) {
$stmt->bind_param('sii', $class, $mark, $id);
$stmt->execute();
echo "Record Updated:";
echo $stmt->affected_rows;
}else{
echo $connection->error;
}
?>

MySQL Update Query

Procedural style

$class='Three';
$mark=68;
$id=5;
$query="UPDATE  student SET class=?,mark=? WHERE id=?";
if ($stmt = mysqli_prepare($connection,$query)){
mysqli_stmt_bind_param($stmt, "sii", $class, $mark, $id);
mysqli_stmt_execute($stmt);
echo "Record Updated:";
echo mysqli_affected_rows($connection);

}else{
echo mysqli_error($connection);
}

Updating Multiple records

This code will increase mark of all students of class Three by 5 .

$class = 'Three';
$mark = 5;
  // SQL UPDATE query with place holders 
$query = "UPDATE student SET  mark=mark+? WHERE class=?";
 // Prepare the SQL statement
if ($stmt = $connection->prepare($query)) {
        // Bind parameters (i = integer,s = string)
    $stmt->bind_param("is", $mark,$class);
    // Execute the statement
    if ($stmt->execute()) {
    // Output the number of affected rows
    echo "Records Updated: " . $connection->affected_rows;
    } else {
    // Handle execution error
    throw new Exception("Statement execution failed: " . $stmt->error);
    }
    // Close the statement
    $stmt->close();
 } else {
    // Handle preparation error
    throw new Exception("Statement preparation failed: " . $connection->error);
}

Output

Records Updated: 4

Updating Data with MySQLi Prepared Statements and Try-Catch Block

More on Try - Catch - Finally code blocks to handle error
procedural style

try {
    // Variables to update
    $class = 'Three';
    $mark = 68;
    $id = 5;

    // SQL UPDATE query
    $query = "UPDATE student SET class=?, mark=? WHERE id=?";

    // Prepare the statement
    if ($stmt = mysqli_prepare($connection, $query)) {
        // Bind parameters (s = string, i = integer)
        mysqli_stmt_bind_param($stmt, "sii", $class, $mark, $id);

        // Execute the statement
        if (mysqli_stmt_execute($stmt)) {
            // Output the number of affected rows
            echo "Record Updated: " . mysqli_affected_rows($connection);
        } else {
            // Handle execution error
            throw new Exception("Statement execution failed: " . mysqli_stmt_error($stmt));
        }

        // Close the statement
        mysqli_stmt_close($stmt);
    } else {
        // Handle preparation error
        throw new Exception("Statement preparation failed: " . mysqli_error($connection));
    }
} catch (Exception $e) {
    // Handle any caught errors
    echo "Error: " . $e->getMessage();
}

// Close the connection
mysqli_close($connection);

Here’s the object-oriented style (OOP) version of the code with a try-catch block for error handling:

try {
    // Variables to update
    $class = 'Three';
    $mark = 68;
    $id = 5;

    // SQL UPDATE query
    $query = "UPDATE student SET class=?, mark=? WHERE id=?";

    // Prepare the SQL statement
    if ($stmt = $connection->prepare($query)) {
        // Bind parameters (s = string, i = integer)
        $stmt->bind_param("sii", $class, $mark, $id);

        // Execute the statement
        if ($stmt->execute()) {
            // Output the number of affected rows
            echo "Record Updated: " . $connection->affected_rows;
        } else {
            // Handle execution error
            throw new Exception("Statement execution failed: " . $stmt->error);
        }

        // Close the statement
        $stmt->close();
    } else {
        // Handle preparation error
        throw new Exception("Statement preparation failed: " . $connection->error);
    }
} catch (Exception $e) {
    // Handle any caught exceptions
    echo "Error: " . $e->getMessage();
}

// Close the connection
$connection->close();

Try Block:

The try block contains the main logic for preparing, binding, and executing the SQL query.
Any errors that occur during these operations will be caught by the catch block.

SQL Query:

The SQL query is prepared using prepare() on the $connection object, and the query string contains placeholders (?) for the parameters.

Binding Parameters:

bind_param() is called on the $stmt object to bind the variables ($class, $mark, $id) to the placeholders in the SQL query.
The data types are specified as "sii": s for string ($class), and i for integers ($mark and $id).

Execution:

The execute() method runs the prepared statement.
If the query is successful, the number of affected rows is displayed using $connection->affected_rows.

Error Handling:

If an error occurs during statement preparation or execution, an Exception is thrown with the relevant error message (either $stmt->error or $connection->error).
The catch block captures the exception and displays the error message.

Closing:

Both the prepared statement ($stmt->close()) and the connection ($connection->close()) are closed to free up resources.

Benefits of Object-Oriented Approach:

Encapsulation: Object-oriented MySQLi provides cleaner and more modular code.
Error Handling: The use of try-catch ensures that errors are handled gracefully, without crashing the script.
Reusability: With OOP, it’s easier to reuse the database connection and methods for various operations.

Example: Using Transactions for Updates

$connection->begin_transaction();
$class = 'Three';
$mark = 50;

// Prepare the SQL query
$query = "UPDATE student SET  mark=? WHERE class=?";
if ($stmt = $connection->prepare($query)) {
    $stmt->bind_param("is", $mark,$class);
    if ($stmt->execute()) {
		echo "Record updated successfully!";
		echo "<br>Number of records Updated: " . $connection->affected_rows;
        $connection->commit();  // Commit changes if successful
        
    } else {
        $connection->rollback();  // Rollback if there's an error
        echo "Error during update, transaction rolled back.";
    }
    $stmt->close();
}

Output

Record updated successfully!
Number of records Updated: 5

Example: Updating Multiple Columns Conditionally

$query = "UPDATE student SET class=?, mark=? WHERE id=? AND gender=?";
if ($stmt = $connection->prepare($query)) {
    $stmt->bind_param("sisi", $class, $mark, $id, $gender);
    $stmt->execute();
    echo "Updated records: " . $stmt->affected_rows;
    $stmt->close();
}

Example: Fetching Updated Record

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: