SQL Query Syntax 
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')
");
Copyright © 2024 MH Sub I, LLC dba vBulletin. All rights reserved. vBulletin® is a registered trademark of MH Sub I, LLC dba vBulletin.