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 to Lucinda\STDOUT\FrontController object:
$object->addEventListener(Lucinda\STDOUT\EventType::APPLICATION, Lucinda\Project\EventListeners\SQLDataSource::class);
and / or to Lucinda\ConsoleSTDOUT\FrontController object:
$object->addEventListener(Lucinda\ConsoleSTDOUT\EventType::APPLICATION, Lucinda\Project\EventListeners\Console\SQLDataSource::class);
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 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.
To retrieve a connection for a DEVELOPMENT ENVIRONMENT and (optionally) a specific DB server on that machine, use:
$connection = Lucinda\SQL\ConnectionFactory::getInstance(SERVER_NAME);
Where SERVER_NAME is value of "name" attribute at matching <server> tag. If no such attribute was defined, empty string is assumed!
This will return a single Lucinda\SQL\Connection per session, reused until script ends and automatically closed afterwards.
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 = [], SERVER_NAME = "");
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!
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();
When framework is installed, a number of DAO classes are pushed already to your project, all assuming you will only use one SQL driver per DEVELOPMENT ENVIRONMENT. If that is not true for your project, modify DRIVER_NAME constant appropriately in following files:
Value of DRIVER_NAME must match with value of a "name" attribute in a <server> tag for that DEVELOPMENT ENVIRONMENT!