Difference between revisions of "SQL coding style"

Jump to: navigation, search
(Indentation)
Line 1: Line 1:
This page describes recommended coding style for complex database queries.
+
This page describes recommended coding style for complex database queries.  
  
 +
Full SQL queries are used in $DB->get_records_sql(), $DB->get_recordset_sql() or $DB->execute(). SQL fragments may be used in DML method with _select() suffix.
 +
 +
==Double quotes==
 +
 +
All sql queries and fragments should be enclosed in double quotes, do not concat SQL from multiple parts if possible. The single quotes are used for sql strings, it also helps with visual highlighting and SQL code completion in some IDEs.
 +
 +
<code php>
 +
$records = $DB->get_records_select('some_table', "id > ?", array(111));
 +
</code>
  
 
==Parameter placeholders==
 
==Parameter placeholders==
  
==Double quotes==
+
All variable query parameters must be specified via placeholders. It is possible to use three different types of placeholders: :named, ? and $1. It is recommended to use named parameters if there is more than one.
 +
 
 +
<code php>
 +
$sql = "SELECT *
 +
          FROM {some_table}
 +
        WHERE id > :above";
 +
$records = $DB->get_records_sql($sql, array('above'=>111));
 +
</code>
  
 
==Indentation==
 
==Indentation==

Revision as of 11:07, 14 June 2013

This page describes recommended coding style for complex database queries.

Full SQL queries are used in $DB->get_records_sql(), $DB->get_recordset_sql() or $DB->execute(). SQL fragments may be used in DML method with _select() suffix.

Double quotes

All sql queries and fragments should be enclosed in double quotes, do not concat SQL from multiple parts if possible. The single quotes are used for sql strings, it also helps with visual highlighting and SQL code completion in some IDEs.

$records = $DB->get_records_select('some_table', "id > ?", array(111));

Parameter placeholders

All variable query parameters must be specified via placeholders. It is possible to use three different types of placeholders: :named, ? and $1. It is recommended to use named parameters if there is more than one.

$sql = "SELECT *
          FROM {some_table}
         WHERE id > :above";
$records = $DB->get_records_sql($sql, array('above'=>111));

Indentation

sql indentation.png

See also