Proper use of SQL queries

This chapter is intended to show common mistakes/improper use of databases in earlier versions of WB and also offer the appropriate solutions. The offending code fragments are not invented, but taken from original WB and modules!

Attention: The following examples are not intended for copy&paste programming, but only exemplary of thinking ideas that are intended to show how a problem can be solved!

Based on the `user_id` simply a single record is searched in the table `users`.

false

$query = $database->query("SELECT * FROM '.TABLE_PREFIX.'users WHERE user_id = $user_id LIMIT 1");
if ($query->numRows() > 0) {
    while ($user = $query->fetchRow()) {
 
        // all ok, do something with the data
 
    }
}

This code contains a number of problem areas and superfluous code:

• The query() method is not secured. If an error occurs in the statement or the database, the script breaks with the very meaningful message “Error in class Database line xxx” completely.
• LIMIT 1 - this statement is unnecessary because a user_id can be the primary key in principle only once.
• The SQL statement does not correspond to the SQL Strict rules and is additionally defined within the argument bracket of →query().
• WHILE - this loop is superfluous, since a maximum of only one data set will be delivered.
• The query on numRows() is unnecessary because the subsequent fetchRow() returns the first record of the result object, or null if no record was found.

Corrected applying the new standards of WB, this results in the following correct code fragment:

Snippet.php

$sql = 'SELECT * FROM `'.$oDb->TablePrefix.'users` '
     . 'WHERE `user_id`='.$user_id;
if (($oResult = $oDb->doQuery($sql))) {
    if (($aUser = $oResult->fetchArray())) {
        // all ok, do something with the data now
    } else {
        // no user record found
    }
} else {
    // error on executing query
}

(For simplicity, only the SQL statements are displayed.)
To achieve this, data from two tables are required: the data from 'wb_pages' and the user name from the table 'wb_users'. This problem is elegantly solved without any additional PHP code with pure SQL. To do this, only the two tables need to be correlated by the fields 'modified_by' and 'user_id' linked by a JOIN.
It is worthwhile in any case, be read in SQL to grasp the basics.

Snippet.php

SELECT `wb_users`.`display_name`, `wb_pages`.* 
FROM `wb_pages` LEFT JOIN `wb_users`
    ON `wb_pages`.`modified_by`=`wb_users`.`user_id`
WHERE `wb_pages`.`page_id`=1

…will be continued with emergence of other examples!