Using MySQL variable in PHP Prepared Statement

Question

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.

With PDO classes you can use named parameters.

Nigel Ren
– Nigel Ren

2022-09-20 20:35:13 +00:00
Commented Sep 20, 2022 at 20:35 — Nigel Ren
– Nigel Ren, Commented Sep 20, 2022 at 20:35

O. Jones · Accepted Answer · 2022-09-20 20:25:24Z

2

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);

answered Sep 20, 2022 at 20:25

O. Jones

110k17 gold badges134 silver badges187 bronze badges

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

3 Comments

tadman Over a year ago

Agreed, there's really no reason for the SET call.

S.Mason Over a year ago

Uh that's too bad it only does one at a time. The real reason is for readability. It's difficult having dozens of question marks sprinkled throughout giant queries. It's difficult to troubleshoot and you have to take extra care of the ordering when binding. oh well -- I guess this is the answer.

tadman Over a year ago

The reason you're struggling is because, in all honestly, mysqli is pretty terrible. PDO is a far better DB layer for the singular reason that you can name your placeholders. ? is mysterious. :channel is not. If you're committed to mysqli you're going to need to get used to placeholder bindings really quickly, because if this irritates you going forward you'll never get anything done. Consider: This isn't unlike function arguments that don't have names either. In time you'll learn to deal.

Bill Karwin · Accepted Answer · 2022-09-20 21:05:42Z

If you need to use SET for some reason, you can SET user-defined variables in a prepared statement. Then you can use those variables in a subsequent call. You just can't do both in one call.

// first use SET
$query = "SET @Channel = ?";
$stmt = $mysqli->prepare($query);
// ...bind parameters...
$stmt->execute();

// Then do a second call:
$query = "INSERT INTO log (channel, message, context, datetime)
           VALUES (@Channel, '', '{}', CURRENT_TIMESTAMP); "
$mysqli->query($query); // no need for prepare()/execute() in this case

As long as you use the same connection for both calls, the user-defined variables will still have their value when you INSERT. There's no need to SET and then use the variables in the same call.

Collectives™ on Stack Overflow

Using MySQL variable in PHP Prepared Statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related