phpbbDoctor
Site Admin
Joined: 12 Apr 2005
Posts: 269
|
Subject: [Coding Standard] SQL Statements in PHP Code
Sat May 14, 2005 3:35 pm |
|
|
|
There is one area in the phpBB code where they are not consistent, and that is formatting SQL statements. Here at the phpBB Doctor we have a specific format that we will always use for SQL statements. It goes something like this:
| Code: | $sql = 'SELECT t1.col1
, t1.col2
, t1.col3
, t1.col4
, sum(t1.col3*t2.col4) AS new_name
, ...
FROM ' . TABLE_CONSTANT_1 . ' t1
, ' . TABLE_CONSTANT_2 . ' t2
WHERE t1.pk = t2.pk
AND t1.col1 = ' . $php_variable . '
AND t2.col2 = ' . $another_variable . '
ORDER BY t1.col2
, t2.col1'; |
What you will notice is that every individual item in the SELECT is on its own line with the exception of the first one, every table is on its own line and is aliased, every line of the WHERE clause is on its own line, complex expressions in the SELECT clause are aliased while simple ones are not, and all reserved words are in CAPS while table columns are not.
Why go through all of this?  Several reasons. For one, by having everything on a separate line there are no more "inline find" or "inline add" or "inline replace" commands required. Since every element of the SQL statement is on its own line, all MOD operations become adding before or adding after. It is much easier to debug.
More weirdness... each line begins with a comma instead of ending with one. Did you notice that? There's a very good reason for that. When the comma is at the end of the line, and you want to add a new line at the end of the select clause, you have to first add a comma to the end of the previous line. By having the comma at the beginning, a simple insert takes care of both the comma and the new selection.
As a general rule the first column in the select clause does not follow this pattern. We generally require that the first column of any select statement be a primary key value, and as such it is not likely to be edited or removed at any later date. We always require using primary key values (user_id) rather than alternate values (username) as keys are unique and numeric and therefore more efficient.
The same rule is used for the list of tables and the joins or query conditions.
We do not use double-quotes for SQL code. We always use single quotes. Values that are input from php variables are concatenated with the "dot" notation rather than embedded in quotes. By being consistent there is no confusion. If the embedded php variables require quotes, then the quotes are included within the SQL statement, as follows: | Code: | | AND t1.col1 = "' . $php_text_value . '" |
What happens with a very long SQL statement with this format? Yes, it tends to get long... perhaps several pages worth, depending on your screen resolution. But the advantages to MODding far outweigh the additional screen space required for manual editing, in our opinion.
Note that when we deliver MODs our custom SQL code will always be formatted in this way. We will not reformat existing phpBB queries as that could cause future installations to fail due to a failure to match the code. |
|
Your premium source for custom modification services for phpBB
Forums