Common Moodle programming mistakes
Feel free to add to this list.
Error handling of get_records() and its variants (e.g. get_records_sql())
A common usage of these functions is like this:
$records = get_records() or my_error_handler(); ...
The problem is, if the query is executed successfully but matches no records, get_records() returns false, the same as if there is an error. That triggers the error handling mechanism and might not be what you want.
Since get_records() and its variants cannot tell between these two cases, use them only if you are intentionally not checking for error or if you want to treat the case of no matched record as an error too. For other cases, use get_recordset() or its variants. These functions return an ADORecordSet object (if there is no error) or false (if there is an error).
Result of get_records_sql()
get_records_sql() returns an array if there is at least one record. The function uses the first column of the record set as the key of the result array.
If the SELECT statement only queries one table, and the first column is a primary key (e.g. id), the function returns every row in the record set without problem. If the SELECT joins multiple tables, or if the first column in the statement is not a primary, the function only returns some of the rows in the record set, i.e. for each unique value of the first column, only the last record containing that value. In such case, you should use get_recordset_sql() instead. get_recordset_sql() returns an array that has consecutive keys starting with 0, and the keys are independent of the values in the result set. This way, all records are present in the result array.