CODEWITHSUNDEEP
phpsqlmysqlitransactions
twan
twan

Reputation: 2659

Fatal error when trying to start a transaction in PHP 7.3 with mysqli

I want to execute the following transaction:

START TRANSACTION;
INSERT INTO
   `orders` (producten, totaalprijs, account_id) 
VALUES
   (
      "{\"601bced33b191\":{\"naam\":\"Sierkussens binnen\", \"alias\":\"sierkussens - binnen\", \"url\":\"https:\\ / \\ / printzelf.nl\\ / new\\ / product\\ / sierkussens - binnen ? term = sierkussen\", \"afbeelding\":\"cms\\ / images\\ / producten\\ / textiel_producten\\ / Sierkussens\\ / sierkussen_1.jpg\", \"aantal\":\"1\", \"hoogte\":null, \"breedte\":null, \"uploaden\":\"1\", \"specificaties\":{\"Formaat\":{\"waarde\":\"40 x 40 cm\"}, \"Materiaal\":{\"waarde\":\"Dekostof\"}, \"Samenstelling\":{\"waarde\":\"Hoes\"}, \"Kleur garen\":{\"waarde\":\"Zwart\"}, \"Kleur rits\":{\"waarde\":\"Zwart\"}, \"Ontwerp\":{\"waarde\":\"PRO ontwerpcontrole\"}}, \"prijs\":\"13.99\", \"totaalprijs\":13.99, \"canvas\":\"{\\n \\\"customer_id\\\": \\\"33\\\", \\n \\\"order_id\\\": \\\"123\\\", \\n \\\"quantity\\\": \\\"1\\\", \\n \\\"rulers\\\": null, \\n \\\"canvas_size\\\": \\\"0x0\\\", \\n \\\"bleed\\\": null, \\n \\\"safety_margin\\\": null, \\n \\\"dpi\\\": null, \\n \\\"multiple_layouts\\\": null, \\n \\\"procheck\\\": \\\"y\\\", \\n \\\"multiple_pages\\\": \\\"1\\\", \\n \\\"product_name\\\": \\\"Sierkussens binnen\\\", \\n \\\"thumbnail\\\": \\\"cms\\\\\\ / images\\\\\\ / producten\\\\\\ / textiel_producten\\\\\\ / Sierkussens\\\\\\ / sierkussen_1.jpg\\\"\\n}\"}}", "19", "33" 
   )
;
UPDATE
   `orders` 
SET
   order_id = CONCAT(account_id, ".", id) 
WHERE
   id = LAST_INSERT_ID();
COMMIT;

So I tried starting a transaction like this:

$conn->autocommit(FALSE);
$conn->query('
INSERT INTO orders
(
    producten,
    totaalprijs,
    account_id
)
VALUES
(
    "'.$conn->real_escape_string($encodedproductenarray).'",
    "'.$conn->real_escape_string(subtractbtw($subtotaalcheckoutafr)).'",
    "'.$conn->real_escape_string($_SESSION['user']['id']).'"
)');
$conn->query('
UPDATE orders SET order_id = CONCAT(account_id, ".", id)
WHERE id = LAST_INSERT_ID()');
$conn->commit();

But this gives me the following error:

PHP Fatal error:  Uncaught Error: Call to undefined method Connection::autocommit()

Am I doing something wrong?

$conn is my connection class that contains the following:

session_start();
class Connection {
    // Configure Database Vars
    private $host     = 'localhost';
    private $username = 'sdgsdg';
    private $password = 'sdgsdg!';
    private $db_name  = 'dsgsd';
    public $db;

    function __construct() {
        // Create connection
        $db = new mysqli($this->host, $this->username, $this->password, $this->db_name);
        // Check connection
        if ($db->connect_errno > 0) {
            die('Unable to connect to the database: '.$db->connect_error);
        }
        $this->db = $db;
    }

    public function query($query) {
        $db = $this->db;
        $this->db->query('SET NAMES utf8');
        if (!$result = $this->db->query($query)) {
            die('There was an error running the query ['.$db->error.']');
        } else {
            return $result;
        }
    }

    public function multi_query($query) {
        $db = $this->db;
        if (!$result = $this->db->multi_query($query)) {
            die('There was an error running the multi query ['.$db->error.']');
        } else {
            return $result;
        }
    }

    public function real_escape_string($value) {
        return $this->db->real_escape_string($value);
    }

    public function inserted_id() {
        return $this->db->insert_id;
    }
}

$conn = new Connection();

I've also tried adding the entire transaction inside a query like this:

$insertorder = '
START TRANSACTION;
    INSERT INTO orders
    (
        producten,
        totaalprijs,
        account_id
    )
    VALUES
    (
        "'.$conn->real_escape_string($encodedproductenarray).'",
        "'.$conn->real_escape_string(subtractbtw($subtotaalcheckoutafr)).'",
        "'.$conn->real_escape_string($_SESSION['user']['id']).'"
    );
    UPDATE orders SET order_id = CONCAT(account_id, ".", id)
    WHERE id = LAST_INSERT_ID();
COMMIT;';
$insertordercon = $conn->query($insertorder);

But this gives me a syntax error.

Upvotes: 0

Views: 171

Answers (1)

scriptor6
scriptor6

Reputation: 226

You have a mysqli object in your class. That has all the methods you are looking to use. Do something like this:

$conn->db->autocommit(FALSE);
$conn->query('
INSERT INTO orders
(
    producten,
    totaalprijs,
    account_id
)
VALUES
(
    "'.$conn->real_escape_string($encodedproductenarray).'",
    "'.$conn->real_escape_string(subtractbtw($subtotaalcheckoutafr)).'",
    "'.$conn->real_escape_string($_SESSION['user']['id']).'"
)');
$conn->query('
UPDATE orders SET order_id = CONCAT(account_id, ".", id)
WHERE id = LAST_INSERT_ID()');
$conn->db->commit();

Alternatively, you could implement these methods in your class. You have already done so for query and real_escape_string, so it should be easy. Just insert into the class:

public function autocommit($b) {
    $this->db->autocommit($b);
}

public function commit() {
   $this->db->commit();
}

...or similar.

Upvotes: 2

Related Questions