Always double-quote SQL queries, even if they contain no dynamic text.
$db->query_read('SELECT field FROM ' . TABLE_PREFIX . 'table ORDER BY field');
$db->query_read("SELECT field FROM " . TABLE_PREFIX . "table ORDER BY field");
SQL function calls should not be followed by a space.
$db->query_read("SELECT COUNT(*) AS records, SUM(field_one) AS total FROM " . TABLE_PREFIX . "table");
If a query string is long and would be better written on more than one line, separate it onto separate lines, putting the clause keyword at the start of the line in the following manner: double-quote, newline, indent, [query string], newline, outdent, double-quote.
$results = $db->query_read("SELECT field_one, field_two, field_three,
IF(field_four = 'Yes', 1, 0) AS field_four,
FROM " . TABLE_PREFIX . "table_one AS table_one
LEFT JOIN " . TABLE_PREFIX . "table_two As table_two USING(field_one)
WHERE table_one.field_two IN('bla bla bla', 'moo moo moo')
ORDER BY table_one.field_one DESC
LIMIT 5,10");
$results = $db->query_read("
SELECT field_one, field_two, field_three,
IF(field_four = 'Yes', 1, 0) AS field_four,
FROM " . TABLE_PREFIX . "table_one AS table_one
LEFT JOIN " . TABLE_PREFIX . "table_two As table_two USING(field_one)
WHERE table_one.field_two IN('bla bla bla', 'moo moo moo')
ORDER BY table_one.field_one DESC
LIMIT 5,10
");
Additional 'AND' clauses for the 'WHERE' clause should be intented if the query spans multiple lines.
$results = $db->query_read("
SELECT field_one, field_two
FROM " . TABLE_PREFIX . "table AS table
WHERE field_one IN(3,4,5)
AND field_two <> ''
AND field_three = 'something'
ORDER BY field_one
");
Do not quote numeric values in SQL queries, use intval() or $var += 0; to ensure that the variable is safe.
$db->query_first("SELECT something FROM table WHERE tableid = '$numericvar'");
$db->query_first("SELECT something FROM table WHERE tableid = '" . intval(numericvar) . "'");
$db->query_first("SELECT something FROM table WHERE tableid = " . intval($numericvar));
$numericvar = intval($numericvar);
$db->query_first("SELECT something FROM table WHERE tableid = $numericvar");
Do not specify a length for numeric fields unless you have specified 'UNSIGNED ZEROFILL'.
$db->query_write("
ALTER TABLE " . TABLE_PREFIX . "table
ADD field_four SMALLINT(5) UNSIGNED NOT NULL,
ADD field_five INT(10) UNSIGNED NOT NULL
");
$db->query_write("
ALTER TABLE " . TABLE_PREFIX . "table
ADD field_four SMALLINT UNSIGNED NOT NULL,
ADD field_five INT UNSIGNED NOT NULL
");
Do not use the 'AFTER' keyword in 'ALTER' statements. Some servers seem to have problems with 'AFTER' so it is best avoided.
$db->query_write("
ALTER TABLE " . TABLE_PREFIX . "table
ADD field_six INT UNSIGNED NOT NULL AFTER field_five
");
$db->query_write("
ALTER TABLE " . TABLE_PREFIX . "table
ADD field_six INT UNSIGNED NOT NULL
");
Format INSERT / REPLACE queries like this:
$db->query_write("
INSERT INTO " . TABLE_PREFIX . "table
(field_one, field_two, field_three)
VALUES
(1, 2, 'moo'),
(1, 2, 'baa'),
(3, 5, 'quack')
");
Always list the field names in INSERT / REPLACE queries. Never assume that you are inserting all the fields into a table.
$db->query_write("
INSERT INTO " . TABLE_PREFIX . "table
VALUES
(1, 'one', 'some text')
");
$db->query_write("
INSERT INTO " . TABLE_PREFIX . "table
(field_one, field_two, field_three)
VALUES
(1, 'one', 'some text')
");
If a field in an INSERT query is AUTO_INCREMENT, do not include it in the query.
$db->query_write("
INSERT INTO " . TABLE_PREFIX . "table
(auto_increment_field, field_two)
VALUES
(NULL, 'texty bits')
");
$db->query_write("
INSERT INTO " . TABLE_PREFIX . "table
(field_two)
VALUES
('texty bits')
");