Dbal.sql build array

From phpBB Development Wiki

dbal::sql_build_array –– Builds a SQL INSERT or UPDATE Query statement string.

1 Description
2 Parameters
3 Return Values
4 Examples
5 See Also

Description

string dbal::sql_build_array ( string $query [, array $assoc_ary ] )

The $db->sql_build_array function is responsible for handling UPDATE or INSERT SQL data safely. This function automatically escapes strings and ensures type safety. When using the sql_build_array, there is no need to escape strings or force variable types. Although forcing variable types is still recommended as a general rule.

Parameters

query
Query type, supports:

Parameter	Usage
INSERT	Building insert statements.
INSERT_SELECT	Building query for INSERT INTO table (...) SELECT value, column ... statements.
UPDATE	Building update statements.
SELECT	Building WHERE statement AND logic.

assoc_ary
The data array to create the INSERT or UPDATE SQL Statement string. Array keys are used for field names.

Return Values

Returns a string containing INSERT or UPDATE syntax to be used within an SQL Query.

Examples

Example #1 Inserting a new row into a database table

$sql_ary = array(
    'somedata'      => $my_string,
    'otherdata'     => $an_int,
    'moredata'      => $another_int
);

$sql = 'INSERT INTO ' . SOME_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);

$db->sql_query($sql);

Example #2 Updating a database table row

$sql_ary = array(
    'somedata'      => $my_string,
    'otherdata'     => $an_int,
    'moredata'      => $another_int
);

$sql = 'UPDATE ' . SOME_TABLE . '
    SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
    WHERE user_id = ' . (int) $user_id;
$db->sql_query($sql);

Example #3 Building a SELECT

$sql_ary = array(
    'somedata'      => $my_string,
    'otherdata'     => $an_int,
    'moredata'      => $another_int
);

$sql = 'SELECT some_field
        FROM ' . SOME_TABLE . '
        WHERE ' . $db->sql_build_array('SELECT', $sql_ary);
$result = $db->sql_query($sql);

while ($row = $db->sql_fetchrow($result))
{
    $template->assign_blockvars('some_block', array(
        'SOMEDATA'    => $row['somedata'],
    ));
}

Development Wiki