WebsiteBaker Documentation

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 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!

===== Various examples of more or less complex SQL queries =====
**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!

==== Retrieval of a single user ====
Based on the `user_id` simply a single record is searched in the table `users`.

[color=red]false[/color]
<code=PHP>
$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
    
    }
}
</code>
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 [color=green]correct[/color] code fragment:
<code=PHP 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
}
</code>

==== Retrieve a page and to the name of the associated user ====
//(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.
<code=SQL 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
</code>

FIXME //...will be continued with emergence of other examples!//
WebsiteBaker Documentation

User Tools

Site Tools

Page Tools
