Examples for MySQL MariaDB Prepared Statements & Raw SQL

A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency and protect against SQL injections. Prepared statements should be used in cases where the need for performance is critical, as they are the fastest and safest way for a PHP application to communicate with a MySQL database.

Insert Data Example

The following example will bind parameters to a prepared statement and then will execute the query. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency and protect against SQL injections.

require_once(__DIR__ . "/berry/mysql.php"); // Include php-berry mysql package

// Establish a connection with MySQL server
$connection = new MySQLConnection('localhost', "database_name", "username", "password", "utf8");

$sql = 'INSERT INTO `Employees` (`FirstName`,`LastName`,`Email`,`PhoneNumber`,`Salary`) VALUES (?,?,?,?,?)';
$command = new MySQLCommand($connection, $sql);
$command->Parameters->setString(1, "Elon");
$command->Parameters->setString(2, "Musk");
$command->Parameters->setString(3, "email@email.com");
$command->Parameters->setInteger(4, 7797039519);
$command->Parameters->setDouble(5, 2000);
$command->ExecuteQuery();
$recordID = $command->getLastInsertID(); // This returns the Auto Increment ID
echo 'New employee inserted. Record ID is ' . $recordID . '<br>';

$connection->Close();

Read Data Example

The following example will use a prepared statement to read data from the database. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency and protect against SQL injections.

require_once(__DIR__ . "/berry/mysql.php"); // Include php-berry mysql package

// Establish a connection with MySQL server
$connection = new MySQLConnection('localhost', "database_name", "username", "password", "utf8");

$sql = "SELECT `ID`,`FirstName`,`LastName`,`Salary` FROM `Employees` WHERE `Salary`> ?";
$command = new MySQLCommand($connection, $sql);
$command->Parameters->setDouble(1, 1000);
$reader = $command->ExecuteReader();
while ($reader->Read())
{
    $employeeID = $reader->getValue(0);
    $firstName = $reader->getValue(1);
    $lastName = $reader->getValue(2);
    $salary = $reader->getValue(3);

    echo $firstName . " " . $lastName . " salary is " . $salary;
}
$reader->Close();

$connection->Close();

Transaction Example

The following example uses a transaction to execute two prepared statements. If both statements execute successfully, the transaction is committed. If an error occurs, all changes are rolled back.

require_once(__DIR__ . "/berry/mysql.php"); // Include php-berry mysql package

// Establish a connection with MySQL server
$connection = new MySQLConnection(
    "localhost",
    "database_name",
    "username",
    "password",
    "utf8"
);

try
{
    // Start transaction
    $connection->getLink()->begin_transaction();

    // Insert a new employee
    $sql = "INSERT INTO `Employees`
            (`FirstName`, `LastName`, `Email`, `Salary`)
            VALUES (?, ?, ?, ?)";

    $command = new MySQLCommand($connection, $sql);
    $command->Parameters->setString(1, "Elon");
    $command->Parameters->setString(2, "Musk");
    $command->Parameters->setString(3, "elon@example.com");
    $command->Parameters->setDouble(4, 2000);
    $command->ExecuteQuery();

    // Get the Auto Increment ID of the new employee
    $employeeID = $command->getLastInsertID();

    // Insert a related employee log
    $sql = "INSERT INTO `EmployeeLogs`
            (`EmployeeID`, `Description`)
            VALUES (?, ?)";

    $command = new MySQLCommand($connection, $sql);
    $command->Parameters->setInteger(1, $employeeID);
    $command->Parameters->setString(2, "Employee created");
    $command->ExecuteQuery();

    // Commit all changes
    $connection->getLink()->commit();

    echo "Transaction completed successfully.";
}
catch (Throwable $exception)
{
    // Roll back all changes if an error occurs
    $connection->getLink()->rollback();

    echo "Transaction failed: " . $exception->getMessage();
}
finally
{
    // Close the database connection
    $connection->Close();
}