php with sql - cannot resolve tablename as variable

Hello,
I really like to work with phpStorm becuase of the possibilities to combine php and mySQL with language injection.
This works fine as long as I "hardcode" the mySQL part.
Because I am working on a very complex project that builds php-forms from sql-tables in a very flexible way, I need to access database dynamically, that means by passing variables as arguments in the sql-query. Please let me show the following to code snippets, doing the same, first is "hardcoded", second I call "dynamic"; the only difference is in the kind of the prepare-statement:

1.
//sql - update single field in table
/** @var $mydb_sqli_connection mysqli */
      if ($mysql_statement = $mydb_sqli_connection->prepare("UPDATE members SET lastname = ? WHERE uid = ?")) {
           $mysql_statement->bind_param("si", $ds[$ds_nr][$control_nr]['value'], $ds[$ds_nr][$control_nr]['rowid']);
           $mysql_statement->execute();
          $mysql_statement->close();
     }

2.
//sql - update single field in table
$db_table = $myagTables->memberstable; //sets the table name
$db_field = $myagFields->lastname; //sets the field name
/** @var $mydb_sqli_connection mysqli */
     if ($mysql_statement = $mydb_sqli_connection->prepare("UPDATE $db_table SET $db_field = ? WHERE uid = ?")) {
          $mysql_statement->bind_param("si", $ds[$ds_nr][$control_nr]['value'], $ds[$ds_nr][$control_nr]['rowid']);
          $mysql_statement->execute();
          $mysql_statement->close();
     }

So: Both version work fine. But:
In version 1) I have full support of the editor in phpstorm; it can resolve the table. There are no errors in the inspection.
In version 2) There is an error in the inspection: Cannot resolve table and unable to resolve column.

Is there any workaround? Like defining the variable with @var or @define or so?

Thank you.
Sincerely, Adrian.

6 comments
Comment actions Permalink

I've actually had similar issues with code that relies on table prefixes for deploying code to different server instances.

We use PostGreSQL and I would really love it if PhpStorm could figure out what table I'm referencing in the following example.

$sql = "SELECT * FROM {$prefix}members AS m WHERE m.id = $id";

Currently if I explicitly use "db1_members AS m" I can get code completion and column suggestions when I type 'm.'

I was disappointed that even if I set a temporary value like $prefix = 'db1_'; I still cannot get PhpStorm to detect which table I am referring to.

Is there any update on a workaround?

Thanks very much,
Matt

0
Comment actions Permalink

Thanks for the quick reply Andriy.

Do you think it would be possible to develop a plugin to add this functionality?

0
Comment actions Permalink

No idea. As you can see if it would have been so easily they would have done it LONG time ago (you can clearly see how many duplicates that ticket has).

Thing is -- it's rather PhpStorm specific issue (PHP language, to be precise) as other languages (like Java) do not allow such string interpolation (where real variable is inside the string where SQL is injected). The best they could possibly do right now (I would think so) is to ignore variable completely somehow (to get rid of the error marker) .. but this will not help to resolve the actual SQL completion etc.

You can get rid of the error by switching to "Keywords only" dialect where only syntax highlighting is done and no code validation/completion (e.g. column names from DB tables)

0
Comment actions Permalink

Thank you for the thoughtful reply.

I saw earlier that changing the "Unresolved Reference" inspection from a Warning to Info would be a good solution as well.

0

Please sign in to leave a comment.