Handling multi query

[tmtung144]

I am working with MySQL database, PHP Sanbox

How or if there are instructions for executing multiple queries (similar to http://php.net/manual/en/mysqli.multi-query.php)

For example: I have 3 tables: order, product_order, options_product_order

First, I insert a new row into the order table Then I get the latest ID_ORDER, insert it into the product_order table and then get ID_PRODUCT_ORDER to insert it into the last table.

[chriskapp]

Hi, so Fusio uses the Doctrine DBAL library (https://github.com/doctrine/dbal) so you can not directly use this function but you could simply trigger 3 sql queries i.e.:

$connection->insert('order', [
    'name' => 'my order,
]);

$orderId = $connection->lastInsertId()

$connection->insert('product_order', [
    'order_id' => $orderId,
]);

$productOrderId = $connection->lastInsertId()

$connection->insert('options_product_order', [
    'order_product_id' => $productOrderId,
]);

[tmtung144]

I also read the document about Doctrine DBAL library

but before that I used:

$check = $connection->executeQuery("SELECT LAST_INSERT_ID() FROM " . DB_PREFIX . "order");
$order_last_id = $check->fetchColumn();

Is there any difference between different situations in the case of multiple users working together? And I want to ask more about Transactions, how it is used, because my lastInsertId() function is not found in the document

[chriskapp]

Hi, so the lastInsertId function is based on the mysql LAST_INSERT_ID() query, it should return the same result. To control transactions on your connection you can use the following methods:

$connection->beginTransaction();
$connection->commit();
$connection->rollBack();

You may want to take a look at the doctrine DBAL documentation for more details: https://www.doctrine-project.org/projects/doctrine-dbal/en/2.9/reference/transactions.html#transactions

[EvoPulseGaming]

Answered, should be closed, but I'll add something just for reference for searches to this:

Transactions are perfect if you combing custom versioning, for example:

Table: (id, name, address, version) User downloads their data (1, myname, myaddress, version1) After this another user, Admin, downloads and edits the Users info-> (1, myname, myaddress, version1) to (1, newName, myaddress, version2)

IF user attempts to edit their address, a check in php will compare the user's version number, with the number in the database and will fail, asking the user to refresh before editing again

Here is a stripped down version of how I handled this myself:

<?php
/**
 * @var \Fusio\Engine\ConnectorInterface $connector
 * @var \Fusio\Engine\ContextInterface $context
 * @var \Fusio\Engine\RequestInterface $request
 * @var \Fusio\Engine\Response\FactoryInterface $response
 * @var \Fusio\Engine\ProcessorInterface $processor
 * @var \Psr\Log\LoggerInterface $logger
 * @var \Psr\SimpleCache\CacheInterface $cache
 */

/** @var \Doctrine\DBAL\Connection $connection */
$connectionEvo = $connector->getConnection('EvoTracker_Data');

$body = $request->getBody();
$propertyname = $body->name;
$json_array = $request->getBody();

$id = $request->getParameter("id");

$version = $request->getParameter("version");
$id_assigned_staff = $request->getParameter("id_assigned_staff");
$assigned_staff_pay = $request->getParameter("assigned_staff_pay");

$notes = $request->getParameter("notes");

if ($id == null || $version == null) {
    return $response->build(
        422,
        [],
        [
            'success' => false,
            'message' => "One or more inputs are null",
            'givenid' => $id,
            'givenversion' => $version
        ]
    );
}

$connectionEvo->beginTransaction();
try {
    $currVersion = $connectionEvo->fetchColumn('SELECT version FROM workorder WHERE id = "' . $id . '" FOR UPDATE');

    if ($version == $currVersion) {
        $hasChanged = false;

        if ($id_assigned_staff != null) {
            $affected = $connectionEvo->update(
                'workorder',
                [
                    'id_assigned_staff' => $id_assigned_staff
                ],
                [
                    'id' => $id
                ]
            );
            $hasChanged = true;
        }

        if ($assigned_staff_pay != null) {
            $affected = $connectionEvo->update(
                'workorder',
                [
                    'assigned_staff_pay' => $assigned_staff_pay
                ],
                [
                    'id' => $id
                ]
            );
            $hasChanged = true;
        }

        if ($hasChanged) {
            $affected = $connectionEvo->update(
                'workorder',
                [
                    'version' => $currVersion + 1
                ],
                [
                    'id' => $id
                ]
            );
        }

        $connectionEvo->commit();
        $connectionEvo->close();
    } else {
        return $response->build(
            409,
            [],
            [
                'success' => false,
                'message' => "Workorder you are updating has been modified recently, please re-load workorder and submit your changes again",
                'oldversion' => $version,
                'currentversion' => $currVersion
            ]
        );
    }
} catch (Exception $ex) {
    $connectionEvo->rollBack();
    $connectionEvo->close();
    //throw $ex;
    return $response->build(
        400,
        [],
        [
            'success' => false,
            'WARNING' => "WARNING: DOES NOT PROPERLY IMPLEMENT ROLLBACK IN EVENT OF FAILURE!!!",
            'message' => '$ex'
        ]
    );
}

return $response->build(
    200,
    [],
    [
        'success' => true
    ]
);

[chriskapp]

@EvoPulseGaming yes this is a good example. But I have just noticed the line:

$currVersion = $connectionEvo->fetchColumn('SELECT version FROM workorder WHERE id = "' . $id . '" FOR UPDATE');

I would highly recommend to use prepared statments

$currVersion = $connectionEvo->fetchColumn('SELECT version FROM workorder WHERE id = :id FOR UPDATE', ['id' => $id]);

instead to prevent SQL injections, depending on your route definition this could be an issue. Otherwise you are right I will close this issue.