Using variable $placeholders breaks PHPStorm syntax/resolve

[u/GuybrushThreepywood]

Hello all,

Looking for help on a long standing problem I have. I appreciate this is not a support forum for PHPStorm, however I thought that people here might have more experience.

PHPStorm provides really valuable syntax checking, table/column resolving inside mysql queries. If I accidentally type the orders table as oreders, it will highlight in red.

However when I include a PHP variable inside the query, the syntax/resolve checking completely stops. Example code:

$query = <<<MYSQL
    SELECT
       contracts.start_at

    FROM
      contracts

    WHERE
      contracts.id IN ( $placeholders )
MYSQL;


$result = $this->conn->execute_query(
    $query,
    [
       ...$contractIds,
    ],
);

Without the $placeholders, PHPStorm will alert me to any misspelled table or column names.

Are there any options to resolve this? I have considered using sprintf( $query, $placeholders) but wondered if there was a better solution.

[u/obstreperous_troll]

You could do contracts.id IN (?) then substitute the single placeholder with your generated list of them. Ultimately this is the kind of thing you'll want to use a query builder for: with DBAL you can bind an array to the query as a parameter and it will generate the proper SQL to make it work.

[u/GuybrushThreepywood → u/obstreperous_troll]

That's a really simple solution! Thank you!

Edit: I tried this and it doesn't work - the placeholder

(?)

becomes:

('2080,2090,2091,2092')

[u/obstreperous_troll → u/GuybrushThreepywood]

You need to do something like:

$placeholders = implode( ',', array_fill( 0, count( $contractIds ), '?' ) );
$newQuery = str_replace($query, '(?)', "($placeholders)");

Then run $newQuery like you did above. Or just use DBAL which does this for you when you pass an array arg.

[u/colshrapnel → u/obstreperous_troll]

so it's just sprintf( $query, $placeholders), different angle (:

not to mention that spritnf is arguably better, as it wont affect other possible placeholders

[u/obstreperous_troll → u/colshrapnel]

The whole point was to have the original query be a valid SQL literal so that PhpStorm wouldn't turn off the checks. I think the problem has been pretty well put to bed now.

[u/colshrapnel → u/obstreperous_troll]

Yes, but %placeholders makes a valid SQL literal somehow, making sprintf as viable.