This is how SQL works...

All the examples in this area are carried out in the new style of WB 2.8.4. For older versions mostly you have to replace $oDb by $database, $oDb→TablePrefix by TABLE_PREFIX, and →doQuery() by →query()

This is almost a whole chapter of its own. So the best thing to do first is to establish the most basic rules:

• Statements must not be created in the argument parenthesis of a function / method.
• Statements must always be set up in SQL-strict syntax
  • all SQL keywords must be written in capital letters.
  • all field and table names must be enclosed in `Backticks`.
• Statements must not be limited to „text„, but only with 'text'. If an apostrophe ' is needed within the string, this has to be masked with a backslash \'.

SQL statements should be built up visually such that they can be read and understood quickly and easily. The line by line breakdown by action keywords is very useful here. If a line is too long (Coding Standards) then spread with indentation on multiple lines. In the context of selecting fields it should be noted that the server can provide complete records (SELECT *) significantly faster than a rule containing a long list of individual fields. To demonstrate the structure of statements, in the following there are some examples of the most important types of queries.

Each of all the four examples give back a result-object with all records of the visible sections of a page at the current time.

Example-1.php

$oResult = $oDb->doQuery("SELECT section_id, page_id, position, module, block, publ_start, publ_end FROM ".TABLE_PREFIX."sections where page_id= $iPageId and $iTimestamp between `publ_start` and `publ_end` order by block, position");

Example-2.php

$sql  = 'SELECT `section_id`, `page_id`, `position`, `module`, `block`, `publ_start`, `publ_end` FROM `'.$oDb->TablePrefix.'sections` ';
$sql .= 'WHERE `page_id`='.$iPageId.' AND '.$iTimestamp.' BETWEEN `publ_start` AND `publ_end` ';
$sql .= 'ORDER BY `block`, `position`';
$oResult = $oDb->doQuery($sql);

Example-3.php

$sql = 'SELECT `section_id`, `page_id`, `position`, `module`, '
     .        '`block`, `publ_start`, `publ_end` '
     . 'FROM `'.$oDb->TablePrefix.'sections` '
     . 'WHERE `page_id`='.$iPageId.' '
     .        'AND '.$iTimestamp.' BETWEEN `publ_start` AND `publ_end` '
     . 'ORDER BY `block`, `position`';
$oResult = $oDb->doQuery($sql);     

Example-4.php

$sql = 'SELECT * FROM `'.$oDb->TablePrefix.'sections` '
     . 'WHERE `page_id`='.$iPageId.' '
     .        'AND '.$iTimestamp.' BETWEEN `publ_start` AND `publ_end` '
     . 'ORDER BY `block`, `position`';
$oResult = $oDb->doQuery($sql);     

Let's have a short quiz now:
Question: Which of the examples are easier to read, understand and modify if required?
Answers: 1&2 or 3&4 ??

With this query, you can not go wrong… except for the case that you provide the wrong deletion criteria.
But again: First build the statement and save it in a variable and then passe it to the query method.

DELETE-1.php

$sql = 'DELETE FROM `'.$oDb->TablePrefix.'users`'
     . 'WHERE `user_id`='.$iUserId;
$oDb->doQuery($sql);

Normally, records should be deleted only by specifying their record ID.

In this area it will get interesting. There are several different ways to build the statements for INSERTs and UPDATEs. Especially in the field of supplying the values.
With all kinds of INSERTs, however the SQL-strict rule applies, that values must be assigned to all record fields. Excluded are only the fields that are already defined in the table with a default value. Whenever data is to be written to the database, certain safety rules must be observed.

• It must be ensured that only the correct data type is passed.
• It must be ensured that any value passed was previously examined and is valid.
• It is not allowed to pass values of superglobal arrays, especially from $_POST/$_GET/$_REQUEST/etc. directly.
• String variables have to be 'escaped' before the handover. For this purpose only the escapeString() method of the database object may be used, because only this method is able to mask exactly how it is required by the database currently in use. Any other methods like addslashes() or home-built functions etc. are not allowed.
• If a table contains an autoincrement-field, that one may not be set by an INSERT and not be changed by an UPDATE. In a database running in strict mode this would trigger a severe error and cause the program to exit immediately. Likewise, an abort is triggered when no value is assigned to a field with no default value, when an INSERT is performed.

In the context of WebsiteBaker for INSERT and UPDATE statements the only permitted method is the 'SET'-method for supplying values.

Only one example of how such a statement (although syntactically correct) may not look like:

insert-01.php

// by the way this is an original statement from WB-2.8.3-SP1 'add user'
$sql = "INSERT INTO ".TABLE_PREFIX."users (group_id,groups_id,active,username,password,display_name,home_folder,email,timezone, language) VALUES ('$group_id', '$groups_id', '$active', '$username','$md5_password','$display_name','$home_folder','$email','-72000', '$default_language')";

The same statement now following the rules described above:

insert-02.php

$sql = 'INSERT INTO `'.$oDb->TablePrefix.'users` '
     . 'SET `group_id`='.$group_id.', '
     .     '`groups_id`=\''.$oDb->escapeString($groups_id).'\'`, '
     .     '`active`='.($active ? 1 : 0).', '
     .     '`username`=\''.$oDb->escapeString($username).'\', '
     .     '`password`=\''.$oDb->escapeString($md5_password).'\', '
     .     '`display_name`=\''.$oDb->escapeString($display_name).'\', '
     .     '`home_folder`=\''.$oDb->escapeString($home_folder).'\', '
     .     '`email`=\''.$oDb->escapeString($email).'\', '
     .     '`timezone`='.(int)-72000.', '
     .     '`language`=\''.$oDb->escapeString($default_language).'\'';

An UPDATE basically looks like this:

SET-1.php

$sql = 'UPDATE `'.$oDb->TablePrefix.'users` '
     . 'SET `display_name`=\''.$oDb->escapeString($sNewDisplayName).'\', '
     .     '`active`=1 '
     . 'WHERE `user_id`='.$iUserId;

A major advantage of SET-method in addition to the clarity and ease of changeability, is the fact that no consideration has to be made to the order of the fields. It is sufficient that simply all required fields are present. These benefits by far outweigh the fact that a few more lines of code are needed.

(MySQL specific extension to ANSI SQL)

Like everywhere else, there is almost no rule without exception.
REPLACE works primarily the same as INSERT. With a small but crucial difference:
If you try to insert a record which would cause an index conflict with an existing data set, the process will not be canceled, but the existing, old record is deleted and the new values are added instead.
REPLACE is fundamentally unsuitable for tables that use an autoincrement value that is used in another table as a foreign key.
The WebsiteBaker core uses this SQL command, for example, for entering and changing values in the table `settings`. For this to work, certain requirements must be met at the table.

-- Structure of table  '{TABLE_PREFIX}settings'
--
CREATE TABLE IF NOT EXISTS `{TABLE_PREFIX}settings` (
  `name` VARCHAR(255){FIELD_COLLATION} NOT NULL,
  `value` text{FIELD_COLLATION} NOT NULL,
  PRIMARY KEY (`name`)
){TABLE_ENGINE};

In a statement it is mandatory to pass a value for the field with the PRIMARY_KEY or unique_key. Also, the structure of the statement differs from normal 'SET' - standard, in turn, several records can be changed in one run:

snippet.php

    $sql = 'REPLACE INTO `'.$oDb->TablePrefix.'settings` (`name`, `value`) '
         . 'VALUES (\''.$sName_1.'\', \''.$oDb->escapeString($sValue_1).'\'), ' // Datensatz 1
                . '(\''.$sName_2.'\', \''.$oDb->escapeString($sValue_2).'\'), ' // Datensatz 2
                . '(\''.$sName_3.'\', \''.$oDb->escapeString($sValue_3).'\')';  // Datensatz 3
    if (!$oDb->doQuery($sql)) {
        $retval = false;
    }