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