Using Placeholders to Build Database Queries

In the Venddor IO ecosystem, placeholders play a vital role in structuring database queries. Their utility streamlines query building, ensuring both clarity and safety, especially when inserting dynamic data.

Placeholders Overview

?u Placeholder

This specific placeholder aids in crafting the structure for data updates.

Accepted input: array

Example:

$data = array (
    'payment_id' => 5
);
$order_id = 3;
db_query('UPDATE ?:orders SET ?u WHERE order_id = ?i', $data, $order_id);

Generated query:

UPDATE venddor_orders SET payment_id = '5' WHERE order_id = 3;

Formulating Database Queries with Placeholders in Venddor IO

Within the Venddor IO architecture, database query formation is optimized using placeholders. These placeholders aid developers in constructing concise and secure database queries. Below is a summary of the various placeholders and their applications in Venddor IO.

?e Placeholder

Employed to structure data insertion.

Accepted input: array

Example:

$data = array (
    'payment_id' => 5,
    'order_id' => 3
);
db_query('INSERT INTO ?:orders ?e', $data);

Generated query:

INSERT INTO venddor_orders (payment_id, order_id) VALUES ('5', '3');

?i Placeholder

Converts the given data to an integer.

Accepted data: string, number

Example:

$order_id = 4;
db_query('SELECT * FROM ?:orders WHERE order_id = ?i', $order_id);

Generated query:

SELECT * FROM venddor_orders WHERE order_id = 4;

?s Placeholder

Turns data into a string and adds appropriate slashes.

Accepted data: string, number

Example:

$product_code = 'adasd';
db_query('SELECT * FROM ?:products WHERE product_code = ?s', $product_code);

Generated query:

SELECT * FROM venddor_products WHERE product_code = 'adasd';

?l Placeholder

Processes data to be compatible with the LIKE operator.

Accepted data: string

Example:

$piece = '%black\white%';
db_query('SELECT * FROM ?:product_descriptions WHERE product LIKE ?l', $piece);

Generated query:

SELECT * FROM venddor_product_descriptions WHERE product LIKE '%black\\\\white%';

?d Placeholder

Formats data into a fractional number.

Accepted data: string, number

Example:

$list_price = '123.345345';
db_query('SELECT * FROM ?:products WHERE list_price = ?d', $list_price);

Generated query:

SELECT * FROM venddor_products WHERE list_price = '123.35';

?a Placeholder

Crafts data for the IN () structure, treating values as strings.

Accepted data: string, number, array

Example:

$product_codes = array('EAN123', 'EAN234');
db_query('SELECT * FROM ?:products WHERE product_code IN (?a)', $product_codes);

Generated query:

SELECT * FROM venddor_products WHERE product_code IN ('EAN123', 'EAN234');

?n Placeholder

Shapes data for the IN () structure, interpreting values as integers.

Accepted data: string, number, array

Example:

$order_id = '123.45';
db_query('SELECT * FROM ?:orders WHERE order_id IN (?n)', $order_id);

Generated query:

SELECT * FROM venddor_orders WHERE order_id IN (123);

?p Placeholder

Incorporates a pre-prepared value.

Example:

$order_id = 'order_id = 4';
db_query('SELECT * FROM ?:orders WHERE ?p', $order_id);

Generated query:

SELECT * FROM venddor_orders WHERE order_id = 4;

?w Placeholder

Shapes data for the WHERE structure.

Accepted data: array

Example:

$data = array (
    'payment_id' => 5,
    'order_id' => 3
);
db_query('SELECT * FROM ?:orders WHERE ?w', $data);

Generated query:

SELECT * FROM venddor_orders WHERE payment_id = '5' AND order_id = '3';

Formulating Database Queries with Advanced Operators in Venddor IO

Database queries within Venddor IO are further enhanced with a set of specialized operators. These operators, combined with the use of placeholders, offer developers a more flexible and secure means of crafting database statements.

?w Operator

The ?w placeholder comes with integrated support for several SQL operators, including: =, !=, >, <, <=, >=, <>, LIKE, NOT LIKE, IN, NOT IN, NULL.

Here's the structure of the accepted array for this placeholder:

$data = array (
    field => value,
    array(field, operator, value)
);

Where:

field: Represents the table's field name.
value: Denotes the condition's value.
operator: Specifies the condition operator.

Depending on the nature of the input data, the placeholder applies these rules:

For a null value: It uses the NULL operator with a true value.
If the value is an array: It employs the IN operator.
For all other scenarios: The = operator is utilized.

For instance:

$data = array(
    'field1' => 100,
    'field2' => '200',
    'field3' => null,
    'field4' => array(100, 'value'),
    array('field5', '<=', 200),
    array('field6', 'NOT IN', array(100, 'value')),
    array('field7', '!=', 300),
    array('field8', 'NULL', false)
);
db_query('SELECT * FROM ?:orders WHERE ?w', $data);

The translated query will appear as:

SELECT * FROM venddor_orders
    WHERE
        field1 = 100 AND field2 = 200
        AND field3 IS NULL AND field4 IN (100, 'value')
        AND field5 <= 200 AND field6 NOT IN (100, 'value')
        AND field7 != 300 AND field8 IS NOT NULL;

?f Operator

This placeholder ascertains if the variable's value is a valid field name. Should the check fail, it returns an empty string.

Example:

$data = 'payment@id';
db_query('SELECT * FROM ?:orders WHERE ?f = 5', $data);

Resulting in:

SELECT * FROM venddor_orders WHERE  = 5;

?m Operator

A handy placeholder that lets you insert multiple new records into a table simultaneously.

For example:

$data = array(
    array(
        'payment_id' => 5,
        'order_id' => 3
    ),
    array(
        'payment_id' => 5,
        'order_id' => 4
    ),
);
db_query('INSERT INTO ?:orders ?m', $data);

Produces:

INSERT INTO venddor_orders (payment_id, order_id) VALUES ('5', '3'),('5', '4');