berry.mysql

Introduction

Every modern web application interacts with a database. PHP-Berry makes interacting with MySQL and MariaDB databases extremely simple using raw SQL and prepare statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency and protect against SQL injections.

The berry.mysql package includes all necessary tools to read, write and update data to a MySQL Server.

• MySQLConnection
• MySQLCommand
• MySQLDataReader

Configuration

PHP-Berry does not need any special configuration to connect to a database. All you need to be able to exchange data with a database is to initialize a connection. The following example shows how you can initialize a MySQL/MariaDB connection

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

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

MySQL/MariaDB Example

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, "Nikos");
$command->Parameters->setString(2, "Siatras");
$command->Parameters->setString(3, "[email protected]");
$command->Parameters->setInteger(4, 2100000000);
$command->Parameters->setDouble(5, 1000);
$command->ExecuteQuery();
$recordID = $command->$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 `Saralary`> ?";
$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();