28.5. The syntax of SQL code

Aiming at making PHP-Nuke compatible with more databases, the SQL syntax has been transformed to functions, in order to achieve a standard syntax that is independent of the database used. For convenience, let us recall the file sql_layer.php in a somewhat cleaned-up version:

sql_connect($host, $user, $password, $db) 
sql_logout($id) 
sql_query($query, $id) 
sql_num_rows($res) 
sql_fetch_row(&$res, $nr) 
sql_fetch_array(&$res, $nr) 
sql_fetch_object(&$res, $nr) 
sql_free_result($res) 

With this syntax you will be able to render all the modifications, blocks or modules you create compatible to all the databases supported by PHP-Nuke, which are:

• MySQL

• mSQL

• PostgreSQL

• PostgreSQL_local

• ODBC

• ODBC_Adabas

• Interbase

• Sybase

From version 6.5 and up, PHP-Nuke users the same abstraction layer of phpBB for compatibility reasons. It's very easy and highly tested. Normaly you make a query on MySQL like this:

$sql = "SELECT uid, uname FROM nuke_users";
$result = mysql_query($sql);
list($uid, $uname) = mysql_fetch_row($result);

With the SQL abstraction layer on PHP-Nuke, you would declare $db as a global variable and then write:

$sql = "SELECT uid, uname FROM nuke_users";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);

This will return the array $row[] with the results. If you want to work with more friendly names you should do this:

$uid = $row[uid];
$uname = $row[uname];

But it is much faster for you (and for the system) to use just the array values directly, ie:

echo "Hello $row[uname], Welcome to my site!";

Note that there isn't any "sql_fetch_array", the sql_fetch_row automaticaly will create the array with the results of your query.

The old method using the file sql_layer.php and the variable $dbi is now deprecated. It works for compatibility reasons, but we strongly suggest to any developer making new modules or modifying a module to start using the new method. See also the ADDONS-MODULES file that came with your PHP-Nuke package.