Tutorials: Working with SQL Databases

Setting SQL Databases

In order to be able to query SQL databases (eg: MySQL), you first need to open stdout.xml and make sure this tag exists within <listeners>:

<listener class="SQLDataSourceInjector"/>

then create 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>, child of <servers> tag. Example:

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

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