Tutorials: Working with SQL Databases

├─ Setting SQL Databases
└─ Querying 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(Lucinda\STDOUT\EventType::APPLICATION, "SQLDataSourceInjector");

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

<sql>
    <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 <server> 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 = Lucinda\SQL\ConnectionSingleton::getInstance();

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

$connection = Lucinda\SQL\ConnectionFactory::getInstance(SERVER_NAME);

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

Both will return a single Lucinda\SQL\Connection 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 Lucinda\SQL\StatementResults 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();

□ ×