The syntax of SQL code

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) (1)
sql_logout($id) (2)
sql_query($query, $id) (3)
sql_num_rows($res) (4)
sql_fetch_row(&$res, $nr) (5)
sql_fetch_array(&$res, $nr) (6)
sql_fetch_object(&$res, $nr) (7)
sql_free_result($res) (8)
Log into the DB.
Disconnect from the DB.
Number of Rows.
Fetch Rows.
Fetch Array.
Fetch Object.
Free Result.

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.


Site Info v2.2.2

Last SeenLast Seen
  • fkelly
  • elnegro
Server TrafficServer Traffic
  • Total: 377,123,243
  • Today: 3,369
Server InfoServer Info
  • Feb 21, 2019
  • 01:33 am CET