MYSQLi Update query

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;
}

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

<?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

$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

$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);
}

Records Updated: 4

Updating Data with MySQLi Prepared Statements and Try-Catch Block

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);

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();
}

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

$id=5;
$mark=70;
$query = "UPDATE student SET mark=? WHERE id=?";
if ($stmt = $connection->prepare($query)) {
    $stmt->bind_param("ii", $mark, $id);
    if ($stmt->execute()) {
        $stmt->close();
        // Now fetch the updated record
        $result = $connection->query("SELECT * FROM student WHERE id=$id");
        $updatedRecord = $result->fetch_assoc();
        echo "Updated Record: " . json_encode($updatedRecord);
    }
}

MySQL DUMP of student table