29,232
edits
Changes
m
→SQL Coding rules
Line 247:
Line 247:
+
+
+
'MU' (for unit prices), 'MT' (for total prices) or 'MS' (otherwise) depending on usage of number. (see function documentation)
'MU' (for unit prices), 'MT' (for total prices) or 'MS' (otherwise) depending on usage of number. (see function documentation)
−* Functions NOW or SYSDATE are forbidden inside SQL requests. If you must use the current date into a field, value must come from the PHP and not from the database engine. This is for better portability of code and correct management of TimeZone.
+* Functions NOW, SYSDATE or DATEDIFF are forbidden inside SQL requests. If you must use the current date into a field, value must come from the PHP and not from the database engine. This is for better portability of code and correct management of TimeZone.
+For example, don't do:
+<source lang="php">
+$sql="SELECT rowid FROM table where datefield = NOW()";
+</source>
+but do:
+<source lang="php">
+$sql="SELECT rowid FROM table where datefield = '".$this->db->idate(dol_now())."'";
+</source>
+For example, don't do:
+<source lang="php">
+$sql="SELECT rowid FROM table where DATEDIFF(table.datefield, NOW()) > 7";
+</source>
+but do:
+<source lang="php">
+$sql="SELECT rowid FROM table where datefield < '".$this->db->idate(dol_now() - (7 * 24 * 3600))."'";
+</source>
+An other advantage of this rule, is that request benefits of index because we are making a compare of a field with a fixed value. When using datediff, you make an operation on field before comparison, this means database can't use the index on field, resulting on very bad performance compared to solution without the datediff.
== Mysql specificities ==
== Mysql specificities ==