Syntax error when running multiple queries through mysqli [duplicate]

Question

I‘m trying to move rows from one table to another, when they‘re older than 6 hours. While my code works perfectly in PMA, I just get an error via PHP. This is my PHP code:

$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$timestamp6h = time() - 21600;
$sql="BEGIN;
INSERT INTO archiv6h SELECT * FROM links WHERE tweettimestamp < $timestamp6h
ON DUPLICATE KEY UPDATE archiv6h.tweetscount= archiv6h.tweetscount+ links.tweetscount, archiv6h.followerscount= archiv6h.followerscount + links.followerscount, archiv6h.tweettimestamp= archiv6h.tweettimestamp + links.tweettimestamp;
DELETE FROM links WHERE tweettimestamp < $timestamp6h;
COMMIT;";
if (!mysqli_query($conn, $sql)) {
    printf("Errormessage: %s\n", mysqli_error($conn));
}
$result = mysqli_query($conn, $sql);
$conn->close();

I then get the following error message:

Errormessage:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO archiv6h SELECT * FROM links WHERE tweettimestamp < 1521038638 ON ' at line 1

When I use this as a query in PMA, it works like intended:

BEGIN;
INSERT INTO archiv6h SELECT * FROM `links` WHERE tweettimestamp < 1521038638
ON DUPLICATE KEY UPDATE archiv6h.tweetscount= archiv6h.tweetscount+ links.tweetscount, archiv6h.followerscount= archiv6h.followerscount + links.followerscount, archiv6h.tweettimestamp= archiv6h.tweettimestamp + links.tweettimestamp;
DELETE FROM `links` WHERE tweettimestamp < 1521038638;
COMMIT;

Has someone an idea how to get the query running in PHP?

Paul Spiegel · Accepted Answer · 2018-03-14 21:38:26Z

5

With BEGIN; and COMMIT; that are four statements. You can't run multiple statements in one mysqli_query() call. If you need a transaction, use mysqli::begin_transaction(). And only use one statement per mysqli_query() call:

$conn->begin_transaction();
$conn->query("INSERT ...");
$conn->query("DELETE ...");
$conn->commit();

Note that you should also configure mysqli to throw exceptions. I would write your script the following way:

// set connection variables

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = mysqli_connect($servername, $username, $password, $dbname);
$timestamp6h = time() - 21600;

$conn->begin_transaction();
$conn->query("
    INSERT INTO archiv6h SELECT * FROM links WHERE tweettimestamp < $timestamp6h
    ON DUPLICATE KEY UPDATE
        archiv6h.tweetscount    = archiv6h.tweetscount    + links.tweetscount,
        archiv6h.followerscount = archiv6h.followerscount + links.followerscount,
        archiv6h.tweettimestamp = archiv6h.tweettimestamp + links.tweettimestamp
");
$conn->query("DELETE FROM links WHERE tweettimestamp < $timestamp6h");
$conn->commit();

If anything fails, $conn->commit(); will never be executed, and the script will output an error message. Threre is not even a need to close the conection, since it will be closed when the script ends.

edited Mar 14, 2018 at 21:38

answered Mar 14, 2018 at 21:05

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nico Haase Over a year ago

Four statements - there's an INSERT and a DELETE left after removing the transaction

Paul Spiegel Over a year ago

@NicoHaase Didn't see that first.

Collectives™ on Stack Overflow

Syntax error when running multiple queries through mysqli [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related