Tutorials: Working with SQL Databases

Setting SQL Databases

In order to be able to query SQL databases (eg: MySQL), you first need to open bootstrap index.php file and add this event listener:

$object->addEventListener(::APPLICATION, "SQLDataSourceInjector");

then create in stdout.xml a tag (which will hold your connection settings), child of DEVELOPMENT ENVIRONMENT tag (because credentials will be different on another environment), child of tag. Example:

<local> <server driver="mysql" host="localhost" port="3306" username="root" password="" schema="example" charset="utf8"/> </local> </sql>

If your site uses multiple database servers for a single DEVELOPMENT ENVIRONMENT, it is allowed to have multiple entries there. To learn more how to configure this tag, check official documentation!

Querying SQL Databases

Querying SQL databases requires you completed setting SQL databases section! To see a workign example how to efficiently develop models on it using DAO pattern, check Models section above.

Retrieving a Connection

If you have a single server per DEVELOPMENT ENVIRONMENT, to get a connection, use:

$connection = ::getInstance();

If you are having multiple servers per DEVELOPMENT ENVIRONMENT, use this instead

$connection = ::getInstance(SERVER_NAME);

Where SERVER_NAME is value of "name" attribute at matching tag.

Both will return a single per session, reused until script ends and automatically closed afterwards.

Running Statements

Once a connection is retrieved, to run a query that is not parameterized, use this pattern:

$results = $connection->createStatement()->execute(QUERY);

Example:

$results = $connection->createStatement()->execute("SELECT * FROM users");

To run a query that is parameterized, use this pattern instead:

$statement = $connection->createPreparedStatement(); $statement->prepare(QUERY); $results = $statement->execute(BOUND_PARAMETERS);

Example:

$statement = $connection->createPreparedStatement(); $statement->prepare("SELECT id FROM users WHERE username=:username AND password=:password"); $results = $statement->execute([":username"=>$userName, ":password"=>$password]);

To create and execute a prepared statement in just a single line, use SQL function framework already comes with:

$result = SQL(QUERY, BOUND_PARAMETERS);

Parsing Statement Results

Result of query execution is an instance that can be used to navigate through resultset:

while($row = $resultSet->toRow()) { ... }

Or convert it directly to a PHP value (string, integer, array) via its methods (eg: getInsertId()). Check its reference guide for more info!

Running Transactions

Unless autocommit feature is off on your database server, in order to start a transaction you need this command:

$connection->transaction->begin();

To commit a transaction:

$connection->transaction->commit();
×