CHAPTER 5 ?– CREATING THE PRODUCT CATALOG: PART 2 133
The reason we need to use prepared statements is that they allow adding parameters to
the LIMIT clause of a SELECT query. MySQL 5, at the time of this writing, doesn??™t allow using an
input parameter to set the value of LIMIT except when using prepared statements.
We created a prepared statement that retrieves the necessary products using the PREPARE
command. The statement variables are marked with a question mark (?) in the query. When
executing the query with EXECUTE, we provide the values of those parameters as parameters of
the EXECUTE command. The prepared statement contains five parameters, so we supply five
parameters to EXECUTE (@p1, @p2, @p3, @p4, @p5).
Prepared statements are also useful for performance (because the same statement can be executed
multiple times) and security reasons (because the data types for parameters can be checked
for data type compliancy). However, in our case, we??™re using PDO to implement these features, so
we??™re really using prepared statements only so that we can supply parameters to LIMIT.
We??™ll use the same technique on the other procedures that use LIMIT as well.
catalog_count_products_on_department
This stored procedure counts the number of products that are to be displayed in the page of a
given department. Note that all the department??™s products aren??™t listed on the department??™s page,
but only those products whose display value is 2 (product on department promotion) or 3
(product on department and catalog promotion).
Pages:
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231