Executing Commands with the exec() Method

The exec() method is used to execute SQL statements that do not return a result set, such as INSERT, UPDATE, or DELETE. The main advantage of this method is that it returns an integer representing the number of rows that were affected (added, updated, or deleted) by the command. If no rows were affected, the method returns 0.

EXAMPLE

<?php
class Database {
    private $server = "localhost";
    private $username = "root";
    private $password = "";
    private $db_name = "pdo_db";
    public $connection;

    public function __construct() {
        try {
            // Added charset=utf8mb4 for character support
            $dsn = "mysql:host=" . $this->server . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $this->connection = new PDO($dsn, $this->username, $this->password);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

    public function updateLastNames($old, $new) {
        // The exec method executes the command and returns the number of affected rows
        $sql = "UPDATE users SET last_name = '$new' WHERE last_name = '$old'";
        return $this->connection->exec($sql);
    }
}

// PART 3: Usage
$db = new Database();
$changes_count = $db->updateLastNames('Smith', 'Smithson');

echo "Number of updated rows: " . $changes_count;
?>

RESULT

The result will be displayed here...

Practical Application: We use the exec method for quick operations where prepared statements are not required (e.g., clearing logs or quick status updates). Check the process at home by following these steps:

Start Apache and MySQL in the XAMPP control panel.
Ensure that the users table with test data exists within the pdo_db database.
The method returns the row count (Integer); be careful, as it can return 0 if no changes were made.
Insert the example below into a PHP file and observe how many rows were updated.