Using MySQL variable in PHP Prepared Statement

September 20, 2022

When using a SET clause in a MySQL statement, mysqli->prepare returns false.

This MySQL statement will "prepare" okay:

$query = "  INSERT INTO log (channel, message, context, datetime)
              VALUES (?, 'testmsg', '{}', CURRENT_TIMESTAMP); ";
  
  $stmt = $mysqli->prepare($query); // $stmt will be true

This statement will cause "prepare" to return false.

$query = " SET @Channel = ?;
           INSERT INTO log (channel, message, context, datetime)
           VALUES (@Channel, '', '{}', CURRENT_TIMESTAMP); "
$stmt = $mysqli->prepare($query); // $stmt will be false

The error is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INSERT INTO log (channel, message, context, datetime) ‘ at line 2

Why can’t prepare handle the SET operator in MySQL in this manner? I want to list all my variables (this example only has one but imagine one with 10+) at the top of my statement for readability instead of sprinkling them inside the mysql code. That’s not very unreadable and difficult to debug.

>Solution :

You can only prepare one SQL statement at a time. But your $query contains two statements, separated by a semicolon. Split them apart, or just use this.

$query = " INSERT INTO log (channel, message, context, datetime)
           VALUES (?, '', '{}', CURRENT_TIMESTAMP); "
$stmt = $mysqli->prepare($query);