Can you SET a MySQL variable in ADOdb to avoid repeating bind variables?-CodePudding

I'm using ADOdb with MySQL in PHP and trying to pass a keyword multiple times as a bind variable without having to repeat it in my $queryParams array, is this possible? I'm having an error thrown due to the semicolon.

  $queryParams = [$keyword, $row_ids];
  $query = "
    SET @myKeyword = ?;
    SELECT *
    FROM my_table
    WHERE row_id IN (?)
    AND (
      row_desc1 = @myKeyword
      OR row_des2 = @myKeyword
      OR row_desc3 = @myKeyword
    )";

  $connection->execute($query, $queryParams);

What I'm trying to avoid:

  $queryParams = [$row_ids, $keyword, $keyword, $keyword];
  $query = "
    SELECT *
    FROM my_table
    WHERE row_id IN (?)
    AND (
      row_desc1 = ?
      OR row_des2 = ?
      OR row_desc3 = ?
    )";

  $connection->execute($query, $queryParams);

CodePudding user response：

Most query interfaces do not support multi-statements by default. That is, you can't send two SQL statements in one call.

https://dev.mysql.com/doc/internals/en/multi-statement.html says:

The client must announce that it wants multi-statements by either setting the CLIENT_MULTI_STATEMENTS capability or by using COM_SET_OPTION.

This is something the code making the connection (ADODB in this case) would have to do when you connect.

But it won't work anyway, because multi-statements cannot work with prepare(), therefore cannot be used with query parameters.

https://dev.mysql.com/doc/refman/8.0/en/prepare.html says:

The text must represent a single statement, not multiple statements.

You could make two calls, one to do the SET and the second to do the SELECT that uses that variable you had set in the first call. As long as the two calls use the same MySQL session, the variable will keep its value.

Alternatively, in your example of repeating the keyword, you could write it this way:

AND (
  ? IN (row_desc1, row_des2, row_desc3)
)

This allows you to pass the keyword parameter once, and it still compares it to three columns.

P.S.: You also have a problem with your other condition: row_id IN (?). You can't use a single parameter for multiple values for an IN predicate like that.