-3

I am currently making a project where at a certain condition, the content of TableA would be summarized and inserted into TableB, then a new row would be inserted into TableA. However, it seems that the statement I have created cannot be prepared in PHP, despite the actual query working fine in SQL. Can someone instruct me on what to do in order to fix this?

$insert = "INSERT INTO DATABASE.TableB(ColumnB2, ColumnB3, ColumnB4, ColumnB5)
           SELECT DATE(MAX(ColumnA2)), MIN(ColumnA3), MAX(ColumnA3), AVG(ColumnA3) 
           FROM DATABASE.TableA
           ;

           DELETE 
           FROM DATABASE.TableA
           ;

           ALTER TABLE DATABASE.TableA AUTO_INCREMENT = 1
           ;

           INSERT INTO DATABASE.Current_Level(ColumnA2, ColumnA3)
           VALUES(CURRENT_TIMESTAMP, ?)
           ;";

$stmt = mysqli_stmt_init($conn);

if (!mysqli_stmt_prepare($stmt, $insert))
{
    echo "Error";
}
else
{
    mysqli_stmt_bind_param($stmt, "d", $ColumnA2);
    mysqli_stmt_execute($stmt);
}
Funk Forty Niner
  • 74,372
  • 15
  • 66
  • 132
Sakuya
  • 1
  • 1
    Error messages?? – RiggsFolly Jul 19 '20 at 15:42
  • `echo "Error";` Almost useless But `echo $conn->error;` may give you something you can work with – RiggsFolly Jul 19 '20 at 15:46
  • I just checked my error logs, and it does not return any error. I'm thinking that it's a logical error from the way I wrote it, but I'm not sure what exactly makes it wrong. Sorry for this – Sakuya Jul 19 '20 at 15:48
  • To get errors out of PHP even in a LIVE environment add these 4 lines to the top of any `MYSQLI_` based script you want to debug `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This will force any `MYSQLI_` errors to generate an Exception that you can see on the browser as well as normal PHP errors. – RiggsFolly Jul 19 '20 at 15:50
  • Anyway, you have an answer, below – RiggsFolly Jul 19 '20 at 15:51

1 Answers1

2

You cannot create a prepared statement for multiple SQL statements. You'll need to create a prepared statement for each of these SQL statements. Each SQL statement is separated by a semicolon ;

According to the PHP docs:

Use of the multiple statement with prepared statements is not supported.

https://www.php.net/manual/en/mysqli.quickstart.multiple-statement.php

zeterain
  • 1,108
  • 1
  • 10
  • 15