This is an old revision of the document!
This page is not fully translated, yet. Please help completing the translation.
(remove this paragraph once the translation is finished)
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:
Corrected applying the new standards of WB, this results in the following correct code fragment:
$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.
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!