• Navigation
  • Main page
  • Recent changes
  • Random page
  • Help
• Toolbox
  • What links here
  • Related changes
  • Upload file
  • Special pages
  • Printable version
  • Permanent link
  • Print as PDF

DBAL Layer

From EQdkp Plus Wiki

General

What is a DBAL?

A database abstraction layer is an application programming interface which unifies the communication between a computer application and databases such as MySQL, PostgreSQL, Oracle or SQLite. Traditionally, all database vendors provide their own interface tailored to their products which leaves it to the application programmer to implement code for all database interfaces he would like to support. Database abstraction layers reduce the amount of work by providing a consistent API to the developer and hide the database specifics behind this interface as much as possible. There exist many abstraction layers with different interfaces in numerous programming languages.

Source: Wikipedia.com

Which Datbase Types are supported?

MySQL, MySQLi and PostgreSQL

Security

• Use db->escape() for all inserts & updates, never trust the user!
• Use the :params notation for handling inserts & updates, because the build in build_query() function automatically makes all variables SQL-safe
• use the input class instead of $_GET/$_POST

Table Prefix

The table Prefix is automatically added to the table, if you're adding two underscores in front of the table name. The Query processor is automatically adding the table prefix.

$db->query("SELECT * FROM __tablename");

Read

Single Row

Use the Query function to fetch a single line

$row = $db->query_first("SELECT * FROM __tablename");
echo $row['some_value'];

Multiple Rows

If you want to fetch multiple lines, use this construct

$result = $db->query("SELECT * FROM __tablename");
while($row = $db->fetch_record($result)){
	echo $row['some_value'];
}
$db->free_result($result);

Write

Update

You should use the build in SQL Query generation. the attached array is parsed and set instead of the :params into the base Query. The Query Builder recognizes if its INSERT, REPLACE or UPDATE.

$db->query("UPDATE __test SET :params WHERE id='".$myid."'", array(
        'wl_member' => ucfirst($user->data['username']),
        'wl_item'   => $in->get('wishlist_item'),
        'wl_type'   => $in->get('wishlist_type'),
));

Insert

You should use the build in SQL Query generation. the attached array is parsed and set instead of the :params into the base Query. The Query Builder recognizes if its INSERT, REPLACE or UPDATE.

$db->query("INSERT INTO __test :params", array(
        'wl_id'     => null,
        'wl_member' => ucfirst($user->data['username']),
        'wl_item'   => $in->get('wishlist_item'),
        'wl_type'   => $in->get('wishlist_type'),
));