Database Access#
Database Objects provide a convenient and object-oriented approach to work with the database, but there can be use-cases that require raw access including writing methods for model classes. This section assumes that you have either used prepared statements before or at least understand how it works.
The PreparedStatement Object#
The database access is designed around PreparedStatement, built on top of PHP's PDOStatement
so that you call all of PDOStatement
's methods, and each query requires you to obtain a statement object.
1 2 3 4 5 6 |
|
Query Parameters#
The example below illustrates the usage of parameters where each value is replaced with the generic ?
-placeholder. Values are provided by calling $statement->execute()
with a continuous, one-dimensional array that exactly match the number of question marks.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
Fetching a Single Result#
Do not attempt to use fetchSingleRow()
or fetchSingleColumn()
if the result contains more than one row.
You can opt-in to retrieve only a single row from database and make use of shortcut methods to reduce the code that you have to write.
1 2 3 4 5 6 7 |
|
There are two distinct differences when comparing with the example on query parameters above:
- The method
prepare()
receives a secondary parameter that will be appended to the query asLIMIT 1
. - Data is read using
fetchSingleRow()
instead offetchArray()
or similar methods, that will read one result and close the cursor.
Fetch by Column#
There is no way to return another column from the same row if you use fetchColumn()
to retrieve data.
Fetching an array is only useful if there is going to be more than one column per result row, otherwise accessing the column directly is much more convenient and increases the code readability.
1 2 3 4 5 6 7 8 9 |
|
Similar to fetching a single row, you can also issue a query that will select a single row, but reads only one column from the result row.
1 2 3 4 5 6 7 |
|
Fetching All Results#
If you want to fetch all results of a query but only store them in an array without directly processing them, in most cases, you can rely on built-in methods.
To fetch all rows of query, you can use PDOStatement::fetchAll()
with \PDO::FETCH_ASSOC
as the first parameter:
1 2 3 4 5 6 |
|
As a result, you get an array containing associative arrays with the rows of the wcf{WCF_N}_example
database table as content.
If you only want to fetch a list of the values of a certain column, you can use \PDO::FETCH_COLUMN
as the first parameter:
1 2 3 4 5 6 |
|
As a result, you get an array with all exampleID
values.
The PreparedStatement
class adds an additional methods that covers another common use case in our code:
Fetching two columns and using the first column's value as the array key and the second column's value as the array value.
This case is covered by PreparedStatement::fetchMap()
:
1 2 3 4 5 6 |
|
$map
is a one-dimensional array where each exampleID
value maps to the corresponding userID
value.
If there are multiple entries for a certain exampleID
value with different userID
values, the existing entry in the array will be overwritten and contain the last read value from the database table. Therefore, this method should generally only be used for unique combinations.
If you do not have a combination of columns with unique pairs of values, but you want to get a list of userID
values with the same exampleID
, you can set the third parameter of fetchMap()
to false
and get a list:
1 2 3 4 5 6 |
|
Now, as a result, you get a two-dimensional array with the array keys being the exampleID
values and the array values being arrays with all userID
values from rows with the respective exampleID
value.
Building Complex Conditions#
Building conditional conditions can turn out to be a real mess and it gets even worse with SQL's IN (…)
which requires as many placeholders as there will be values. The solutions is PreparedStatementConditionBuilder
, a simple but useful helper class with a bulky name, it is also the class used when accessing DatabaseObjecList::getConditionBuilder()
.
1 2 3 4 5 6 |
|
The IN (?)
in the example above is automatically expanded to match the number of items contained in $valuesForBar
. Be aware that the method will generate an invalid query if $valuesForBar
is empty!
INSERT or UPDATE in Bulk#
Prepared statements not only protect against SQL injection by separating the logical query and the actual data, but also provides the ability to reuse the same query with different values. This leads to a performance improvement as the code does not have to transmit the query with for every data set and only has to parse and analyze the query once.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
It is generally advised to wrap bulk operations in a transaction as it allows the database to optimize the process, including fewer I/O operations.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
|