Exploring SQL MySQL and PHP
In this section, some useful SQL techniques are described. They are all used somewhere in the RBAC system, but most of them are generally applicable.
When joining tables in the ordinary way, it is better to use the explicit INNER JOIN words between the tables, and to add ON after the second table, followed by the conditions that define the join. This is clearer than using the comma notation and putting the join conditions in among the general WHERE conditions. It will also work in a wider variety of database systems, and therefore has portability benefits.
One commonly used device applies to situations where a SQL SELECT is built dynamically and may have differing numbers of WHERE conditions according to circumstances. Here, we are assuming that the conditions must all apply. Each condition is placed in a new element of an array, which may as well be called $where[]. When all the possible conditions have been set, and assuming we have a partly formed SQL statement...
