Your premium source for custom modification services for phpBB


HomeForumsBlogMOD ManagerFAQSearch Log in

The phpBB Doctor is currently available for private clients only. At this time we are not accepting any new work until we can clear out our current backlog. Thank you for your patience.


[Coding Standard] SQL Statements in PHP Code

Register or Login to Post    ForumsphpBB Anatomy
View previous topic :: View next topic  
Author Message
Site Admin

Joined: 12 Apr 2005

Posts: 269

None Selected

Post 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:
$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? Smile 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:
        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.
Back to top
Display posts from previous:   
Register or Login to Post    ForumsphpBB Anatomy
Page 1 of 1 All times are GMT - 5 Hours

Jump to:  

HomeForumsMOD ManagerFAQSearch Register Log in
Not endorsed by or affiliated with the phpBB Group
Powered by phpBB © phpBB Group
Terms of Service
Web design by MomentsOfLight.com logo